八月 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
属性的角色拥有其所属组角色的所有权限。