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

蔚落 2024-03-27 15:14 91阅读 0赞

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

### 环境 ###

*  win10
 *  pgsql 14.2

### 相关文档 ###

> [PostgreSQL 14.1 手册][PostgreSQL 14.1]  
> [create 语法][create]  
> [grant 授权语法][grant]  
> [revoke 撤回语法][revoke]  
> [alter 更新语法][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 授权语法][grant]里的语法结构仔细的看看，结构基本一致注意下on后边的关键字，对角色的理解很有帮助。

[PostgreSQL 14.1]: http://postgres.cn/docs/14/index.html
[create]: http://postgres.cn/docs/14/sql-createrole.html
[grant]: http://postgres.cn/docs/14/sql-grant.html
[revoke]: http://postgres.cn/docs/14/sql-revoke.html
[alter]: http://postgres.cn/docs/14/sql-alterrole.html