pgsql-用户&角色&组角色创建和维护

环境

• win10
• pgsql 14.2

相关文档

PostgreSQL 14.1 手册
create 语法
grant 授权语法
revoke 撤回语法
alter 更新语法

用户、角色、组角色概念和区别

早期版本（8.1之前）中用户、组、角色是不同的概念，现在角色的概念把“用户”和“组”的概念都包括在内。角色可以完成登录用户、组角色功能。

• 作为组角色时，可以不为角色分配login属性；
• 作为登录用户时，可以添加login属性，并设置认证类型（登录密码），如 password ‘123456’；
• 为角色授予访问权限，如为数据库对象schema、table或者column的访问权限，每种对象有不同的特性；

数据库对象的关系

• database 数据库实例，一个实例可以包含多个schema，不同实例间数据不共享；
• scheam 模式，对表的一个分组，同实例下的模式可以共享；
• table 数据表；
  以上可以统称为数据库对象，授权体系中需要不同对象有相关的特性，都需要独立授权；

ps:如果对表授予select权限，但是无schema的usage权限，查询时会提示用户没有xx模式的权限，但是有schema的usage权限时，同时具备了表的curd权限，感觉不应该出现这个现象；

创建测试表

‘’’
create schema mall;
–建table
create table mall.product
(
id varchar(32) not null constraint product_pk primary key,
name varchar(128),
price int
);
create table mall.“order”
(
id varchar(32) not null
constraint order_pk
primary key,
count int,
price int
);
–数据初始化
insert into mall.product(id,name,price) values (‘p1’,‘n1’,1),(‘p2’,‘n2’,2),(‘p3’,‘n3’,3);
insert into mall.order(id,count,price) values (‘o1’,1,1),(‘o2’,2,2),(‘o3’,2,3);
–CRUD验证
select * from mall.product;
delete from mall.product where id=‘x’;
update mall.product set name=‘x’ where id=‘x’;
insert into mall.product(id,name,price) values (‘p1’,‘n1’,1);
select * from mall.order;
‘’’

测试用例

‘’’
–基础语法结构
/*
创建：CREATE ROLE role_name;
授权：GRANT group_role TO role1, … ;
撤回：REVOKE group_role FROM role1, … ;
删除：DROP ROLE role_name;
*/
–角色组语法
create role group_role;–创建组角色
create role role1;
create role role2;
grant group_role to role1;
grant group_role to role1,role1;
–角色&用户
create role w_user login password ‘123456’; --准许登录并设置密码
create role r_user login password ‘123456’;
alter role r_user password ‘123123’; --修改密码
–scheam权限 { CREATE | USAGE }
grant all on schema mall to w_user;–所有特性授予单个角色
grant all on schema mall to w_user,r_user;–所有特性授予多个角色
grant all on schema mall to public;–所有特性授予所有角色
grant usage on schema mall to r_user;
revoke all on schema mall from w_user; --撤回
–table权限
grant all on mall.product to w_user;–单表单用户
grant all on mall.product,mall.“order” to w_user;–多表单用户
grant all on all tables in schema mall to w_user,r_user;–所有表多用户
grant all on all tables in schema mall to public;–所有表所有用户
grant all on mall.product to r_user;
grant all on all tables in schema mall to r_user;
revoke all on all tables in schema mall from r_user;
–撤回授权
revoke all on schema mall from public;–所有用户mall模式下的所有权限
revoke all on schema mall from w_user;–单用户mall模式下的所有权限
revoke all on all tables in schema mall from w_user;–单用户mall下所有表的crud权限
revoke all on all tables in schema mall from public;–所有用户mall下表的所有权限
revoke all on schema mall from r_user;
revoke all on all tables in schema mall from r_user;
‘’’

总结

pgsql高版本中授权体系里将用户、组、角色都合并到role对象中，完全松散管理。没有login特性的role可以理解为角色；带login特性的可以理解为用户；
官方文档中grant 授权语法里的语法结构仔细的看看，结构基本一致注意下on后边的关键字，对角色的理解很有帮助。