PostgreSQL 教程: 角色成员资格

八月 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表中查询数据,将会失败,因为角色planningcontacts表没有权限:

corp=> select * from contacts;
ERROR:  permission denied for table contacts

要恢复jane的原始权限,请使用RESET ROLE语句:

RESET ROLE;

概括

  • 管理组角色而不是个体角色的权限。

  • 具有INHERIT属性的角色拥有其所属组角色的所有权限。