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

原创

xxj_jing 2023-04-04 21:20:11 博主文章分类：postgresql ©著作权

文章标签 数据库 sql mysql 单用户 文章分类 代码人生

©著作权归作者所有：来自51CTO博客作者xxj_jing的原创作品，请联系作者获取转载授权，否则将追究法律责任

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

环境

win10
pgsql 14.2

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

早期版本（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;
 ‘’’