八月 17, 2023
摘要：在本教程中，您将了解 PostgreSQL 用户组角色以及如何使用它们更有效地管理权限。
目录
PostgreSQL 用户组角色简介
将角色作为一个用户组进行管理会更容易，这样您就可以从整个组中授予或撤销权限，而不是对单个角色执行此操作。
通常，您创建一个代表用户组的角色，然后将组角色中的成员资格授予各个角色。
按照惯例，组角色没有LOGIN特权。这意味着您将无法使用组角色登录 PostgreSQL。
要创建组角色，请使用如下CREATE ROLE语句：
CREATE ROLE group_role_name;
例如，以下语句创建一个组角色sales：
CREATE ROLE sales;
当您在psql工具中使用\du命令时，您将看到组角色与用户角色一起列出：
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
alice | | {}
api | 1000 connections | {}
bob | Cannot login | {}
dba | Create DB | {}
john | Superuser | {}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
sales | Cannot login | {}
要将角色添加到组角色，请使用以下形式的GRANT语句：
GRANT group_role to user_role;
例如，以下语句将角色alice添加到组角色sales中：
GRANT sales TO alice;
如果再次运行\du命令，您将看到alice现在是sales的成员：
\du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
alice | | {sales}
api | 1000 connections | {}
bob | Cannot login | {}
dba | Create DB | {}
john | Superuser | {}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
sales | Cannot login | {}
要从组角色中删除用户角色，请使用REVOKE语句：
REVOKE group_role FROM user_role;
例如，以下语句使用REVOKE语句从组角色sales中删除角色alice：
REVOKE sales FROM alice;
请注意，PostgreSQL 不允许有循环的成员资格关系，比如角色 A 是角色 B 的成员，角色 B 又是角色 A 的成员。
PostgreSQL 角色成员资格示例
角色可以通过以下方式使用组角色的权限：
- 首先，具有
INHERIT属性的角色将自动拥有其所属组角色的特权，包括该角色继承的任何特权。 - 其次，角色可以使用
SET ROLE语句暂时成为组角色。该角色将拥有组角色的权限，而不是其原始登录角色的权限。此外，由角色创建的对象归组角色所有，而不是登录角色所有。
步骤 1. 设置示例数据库和表
\1) 使用 postgres 数据库登录 PostgreSQL。
\2) 创建一个名为corp的新数据库：
create database corp;
\3) 切换到corp数据库：
\c corp
\4) 创建contacts表：
create table contacts(
id int generated always as identity primary key,
name varchar(255) not null,
phone varchar(255) not null
);
\5) 创建forecasts表：
create table forecasts(
year int,
month int,
amount numeric
);
步骤 2. 设置角色和组角色
\1) 创建一个可以使用密码登录的角色jane，并继承其所属组角色的所有权限：
create role jane inherit login password 'securePass1';
\2) 将forecasts表上的select权限授予jane：
grant select on forecasts to jane;
\3) 使用\z命令查看授权表：
\z
\4) 创建marketing组角色：
create role marketing noinherit;
\5) 创建planning组角色：
create role planning noinherit;
\6) 将contacts表上的所有权限授予marketing：
grant all on contacts to marketing;
\7) 将forecasts表上的所有权限授予planning：
grant all on forecasts to planning;
\8) 添加jane为角色marketing的成员：
grant marketing to jane;
\9) 添加planning为角色marketing的成员：
grant marketing to planning;
步骤 3. 使用角色
\1) 如果您使用角色jane连接到 PostgreSQL，您将拥有直接授予jane的权限以及授予marketing的任何权限，因为jane继承了 marketing 的权限：
psql -U jane -d corp
\2) 它会提示您输入 Jane 的密码。
\3) 角色jane可以从forecasts表中查询数据：
dcorp=> select * from forecasts;
\4) 并向contacts表中插入一行：
corp=> insert into contacts(name, phone) values('Jone Doe','408-102-3459');
\5) 但是，jane无法在forecasts表中插入行：
corp=> insert into forecasts(year, month, amount) values(2020,1,1000);
ERROR: permission denied for table forecasts
\6) 执行下面的SET ROLE语句后：
corp=> set role planning;
\7) 角色jane将拥有授予planning的权限，而不是直接授予jane或间接授予marketing的权限。
\8) 现在，jane可以在forecasts表中插入一行：
corp=> insert into forecasts(year, month, amount) values(2020,1,1000);
\9) 如果jane尝试从contacts表中查询数据，将会失败，因为角色planning对contacts表没有权限：
corp=> select * from contacts;
ERROR: permission denied for table contacts
要恢复jane的原始权限，请使用RESET ROLE语句：
RESET ROLE;
概括
-
管理组角色而不是个体角色的权限。
-
具有
INHERIT属性的角色拥有其所属组角色的所有权限。