PostgreSQL 用户和角色（二）

对象授权

使用新创建的用户（wdh）连接数据库（test） 

[hui@hadoop201 ~]$ psql -h hadoop201 -p 5432 -U wdh test
Password for user wdh: 
psql (12.13)
Type "help" for help.

执行查询表提示

test=>  select * from employees;
ERROR:  permission denied for table employees

这里报错了：原因是 wdh 这个用户无权限对 相关表的查询权限。PostgreSQL使GRANT语句进行数据库对象的授权操作。以表为例，基本的授权语法如下：

GRANT privilege_list | ALLON [ TABLE ] table_nameTO role_name;

其中，privilege_list权限列表可以是SELECT、INSERT、UPDATE、DELETE、TRUNCATE等，ALL表示表上的所有权限。例如，使用postgres用户连接hrdb数据库后执行以下语句：

grant select, insert, update, delete on employees,jobs,departments to wdh;

该语句将employees、departments和jobs表上的增删改查权限授予了wdh用户。此时wdh用户就可以访问这些表中的数据：

SELECT first_name,last_name FROM employees;

 对表进行授权的GRANT语句还支持一些其他选项：

GRANT privilege_list | ALLON ALL TABLES IN SCHEMA schema_nameTO role_name;

ALL TABLES IN SCHEMA表示某个模式中的所有表，可以方便批量授权操作。例如：

GRANT SELECT  ON ALL TABLES IN SCHEMA public  to wdh

该语句将public模式中所有表的查询权限授予tony用户，也可以在GRANT 语句的最后指定一个WITH GRANT OPTION，意味着被授权的角色可以将该权限授权其他角色。例如

GRANT SELECT, INSERT, UPDATE, DELETE ON employees, departments, jobs TO wdh WITH GRANT OPTION;

此时，wdh用户不但拥有这些表上的访问权限，还可以将这些权限授予其他角色。除了授权表的访问权限之外，GRANT语句还支持字段、视图、序列、数据库、函数、过程、模式等对象的授权操作。授权操作的语句基本都类似，具体可以参考官方文档。

撤销授权

REVOKE privilege_list | ALLON TABLE table_nameFROM role_name;

REVOKE SELECT, INSERT, UPDATE, DELETE    ON employees, departments, jobs   FROM wdh;

该语句撤销了用户wdh访问employees、departments以及jobs表的权限。REVOKE语句也支持对某个模式中的所有对象进行操作：

REVOKE privilege_list | ALLON ALL TABLES IN SCHEMA schema_nameFROM role_name;

例如以下语句撤销了用户tony在public模式中所有表上的查询权限：

REVOKE SELECT  ON ALL TABLES IN SCHEMA public FROM wdh;

与GRANT语句对应，REVOKE语句还支持字段、视图、序列、数据库、函数、过程、模式等对象的撤销授权操作。撤销授权的语句基本都类似，具体可以参考官方文档。

角色成员

在现实的环境中，管理员通常需要管理大量的用户和对象权限。为了便于权限管理，减少复杂度，可以将用户进行分组，然后以组为单位进行权限的授予和撤销操作。为此，PostgreSQL引入了组（group）角色的概念。具体来说，就是创建一个代表组的角色，然后将该组的成员资格授予其他用户，让其成为该组的成员。首先，使用以下创建一个组角色：

CREATE ROLE group_name;

按照习惯，组角色通常不具有LOGIN特权，也就是不能作为一个用户登录。例如，我们可以先创建一个组managers：

CREATE ROLE managers;

然后，使用与对象授权操作相同的GRANT和REVOKE语句为组添加和删除成员：

GRANT group_name TO user_role, ... ;
REVOKE group_name FROM user_role, ... ;

将用户wdh添加为组managers的成员：

GRANT managers TO wdh;

最后一行输出显示了成员角色（wdh）所属的组（managers）。也可以将一个组添加为其他组的成员，因为组角色和非组角色并没有什么本质区别。

GRANT admin TO managers;

另外，PostgreSQL不允许设置循环的成员关系，也就是两个角色互相为对方的成员。

GRANT managers TO admin;

最后，不能将特殊角色PUBLIC设置为任何组的成员。组角色中的成员可以通过以下方式使用该组拥有的特权：

• 首先，组中的成员可以通过SET ROLE命令将自己的角色临时性“变成”该组角色。此时，当前数据库会话拥有该组角色的权限，而不是登录用户的权限；并且会话创建的任何数据库对象归组角色所有，而不是登录用户所有。
• 其次，对于具有INHERIT属性的角色，将会自动继承它所属的组的全部特权，包括这些组通过继承获得的特权。

考虑以下示例：

CREATE ROLE user1 LOGIN INHERIT;
CREATE ROLE net_admins NOINHERIT;
CREATE ROLE sys_admins NOINHERIT;
GRANT net_admins TO user1;
GRANT sys_admins TO net_admins;

SET ROLE net_admins;

SET ROLE sys_admins;

SET ROLE user1;
SET ROLE NONE;
RESET ROLE;

ALTER ROLE net_admins CREATEDB, CREATEROLE;

SET ROLE net_admins;

DROP ROLE name;

drop role admin