在上一篇中，我们安装了 PostgreSQL 数据库系统管理软件并且初始化了一个数据库集群（Database Cluster）。本篇我们来了解一下 PosgtreSQL 中的角色（role）和用户（user）的概念，以及如何为数据库对象进行授权操作。

PostgreSQL 通过角色的概念来控制数据库的访问权限。角色又包含了两种概念，具有登录权限的角色称为用户，包含其他成员（也是角色）的角色称为组（group）。因此，一个角色可以是一个用户，也可以是一个组，或者两者都是。

角色可以拥有数据库对象（例如表和函数），并且可以将这些对象上的权限授予其他角色，从而控制对象的访问。此外，一个组中的成员可以拥有该组所拥有的权限。

创建角色

在 PostgreSQL 中，使用CREATE ROLE语句创建角色：

CREATE ROLE name;

其中，name指定了要创建的角色名称。

📝完整的CREATE ROLE语句还包含了一些常用的选项，例如 LOGIN，具体内容参考官方文档。

如果想要显示当前数据库集群中已有的角色，可以查询系统目录 pg_roles：

postgres=# SELECT rolname FROM pg_roles;
          rolname          
---------------------------
 pg_monitor
 pg_read_all_settings
 pg_read_all_stats
 pg_stat_scan_tables
 pg_read_server_files
 pg_write_server_files
 pg_execute_server_program
 pg_signal_backend
 postgres
 monitor_system_stats
(10 rows)

或者使用 psql 中的\du命令：

postgres=# \du
                                         List of roles
      Role name       |                         Attributes                         | Member of 
----------------------+------------------------------------------------------------+-----------
 monitor_system_stats | Cannot login                                               | {}
 postgres             | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

其中的 postgres 是系统初始化数据库时创建的默认角色，它是一个超级用户。前一个命令中多出的几个角色都是系统提供的默认角色，用于提供针对一些特定的常用特权和信息的访问权限。每个默认角色包含的具体权限可以参考官方文档。

角色属性

角色可以拥有属性，属性确定了角色拥有的特权，并且在登录时与客户端认证系统进行交互。常见的角色属性包括：

登录特权

只有具有 LOGIN 属性的角色才能连接数据库。具有 LOGIN 角色的用户可以被看做一个“数据库用户”。使用以下语句创建具有登录特权的角色：

CREATE ROLE name LOGIN;
CREATE USER name;

CREATE USER与CREATE ROLE都可以用于创建角色，只不过CREATE USER默认包含了 LOGIN 选项，而CREATE ROLE 没有。

超级用户

数据的超级用户可以避开所有的权限检查，只验证登录权限。因此，这是一个很危险的特权，使用时需要特别小心；最好在日常的操作中避免使用超级用户。使用以下命令创建一个新的超级用户：

CREATE ROLE name SUPERUSER;

只有超级用户才能创建其他的超级用户。

创建数据库
只有明确授权的角色才能够创建数据库（超级用户除外，因为他们可以避开权限检查）。使用以下语句创建一个具有数据库创建特权的角色：

CREATE ROLE name CREATEDB;

创建角色
只有明确授权的角色才能够创建其他角色（超级用户除外，因为他们可以避开权限检查）。使用以下命令创建一个具有角色创建特权的角色：

CREATE ROLE name CREATEROLE;

具有 CREATEROLE 特权的角色还可以修改或删除其他角色，以及为这些角色授予或者撤销成员角色。但是，针对超级用户的创建、修改、删除，以及它的成员变更，需要超级用户特权；CREATEROLE 特权无法针对超级用户执行这些操作。

启动复制
只有明确授权的角色才能够启动流复制（超级用户除外，因为他们可以避开权限检查）。用于流复制的角色还需要拥有 LOGIN 特权。使用以下语句创建创建可以用于流复制的角色：

CREATE ROLE name REPLICATION LOGIN;

密码
只有当用户连接数据库使用的客户端认证方法要求提供密码时，密码属性才有意义。password 和 md5 认证方法需要使用密码。数据库的密码与操作系统的密码相互独立。使用以下语句在创建角色时指定密码：

CREATE ROLE name PASSWORD 'string';

我们在创建角色时，可以根据需要指定某些属性。例如，以下命令创建一个具有登录特权的角色 tony，并且为它指定了密码以及密码过期时间：

CREATE ROLE tony WITH LOGIN PASSWORD 'Pass2019' VALID UNTIL '2020-01-01';
-- CREATE USER tony WITH PASSWORD 'Pass2019' VALID UNTIL '2020-01-01';

使用该用户连接到 postgres 数据库：

[root@centos7 ~]# psql -h 192.168.56.103 -p 5432 -U tony postgres
Password for user tony: 
psql (11.1)
Type "help" for help.

postgres=> \c
You are now connected to database "postgres" as user "tony".

psql命令行工具支持许多选项，-h表示数据库服务器的地址，-p表示服务的监听端口，-U表示登录使用的用户名，最后的postgres代表要连接的数据库。详细的命令行参数可以使用psql --help查看或者参考官方文档。

以下命令创建一个管理角色 admin，它具有创建数据库和创建角色的特权：

postgres=# CREATE ROLE admin CREATEDB CREATEROLE;
CREATE ROLE
postgres=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of 
-----------+------------------------------------------------------------+-----------
 admin     | Create role, Create DB, Cannot login                       | {}
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 tony      | Password valid until 2020-01-01 00:00:00-05                | {}

📝在实践中，最好创建一个拥有 CREATEDB 和 CREATEROLE 特权，但不具有超级用户特权的管理角色，然后使用该角色执行日常的数据库和角色的管理。这种方式可以避免过度使用超级用户可能带来的风险。

一个角色被创建之后，可以通过ALTER ROLE语句修改它的属性。例如，以下命令可以撤销角色 admin 创建角色的特权：

postgres=# ALTER ROLE admin NOCREATEROLE;
ALTER ROLE
postgres=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of 
-----------+------------------------------------------------------------+-----------
 admin     | Create DB, Cannot login                                    | {}
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 tony      | Password valid until 2020-01-01 00:00:00-05                | {}

更多细节可以参考官方文档中的 ALTER ROLE 语句。

对象授权

当我们使用新创建的用户（tony）连接数据库（hrdb）之后，执行以下查询语句：

hrdb=> SELECT * FROM employees;
ERROR:  permission denied for table employees

以上语句执行错误是因为用户没有相应对象上的访问权限。

PostgreSQL 使用GRANT语句进行数据库对象的授权操作。以表为例，基本的授权语法如下：

GRANT privilege_list | ALL
ON [ TABLE ] table_name
TO role_name;

其中，privilege_list 权限列表可以是 SELECT、INSERT、UPDATE、DELETE、TRUNCATE 等，ALL 表示表上的所有权限。

例如，使用 postgres 用户连接 hrdb 数据库后执行以下语句：

hrdb=# GRANT SELECT, INSERT, UPDATE, DELETE
hrdb-#    ON employees, departments, jobs 
hrdb-#    TO tony;
GRANT

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

hrdb=> SELECT * FROM employees;
 employee_id | first_name  |  last_name  |  email   |    phone_number    | hire_date  |   job_id   |  salary  | commission_p
ct | manager_id | department_id 
-------------+-------------+-------------+----------+--------------------+------------+------------+----------+-------------
---+------------+---------------
100 | Steven      | King        | SKING    | 515.123.4567       | 2003-06-17 | AD_PRES    | 24000.00 |             
|            |            90
101 | Neena       | Kochhar     | NKOCHHAR | 515.123.4568       | 2005-09-21 | AD_VP      | 17000.00 |             
|        100 |            90
102 | Lex         | De Haan     | LDEHAAN  | 515.123.4569       | 2001-01-13 | AD_VP      | 17000.00 |             
|        100 |            90
...

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

GRANT privilege_list | ALL
ON ALL TABLES IN SCHEMA schema_name
TO role_name;

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

hrdb=# GRANT SELECT
hrdb-#    ON ALL TABLES IN SCHEMA public
hrdb-#    TO tony;
GRANT

该语句将 public 模式中所有表的查询权限授予 tony 用户。

我们也可以在 GRANT 语句的最后指定一个WITH GRANT OPTION，意味着被授权的角色可以将该权限授权其他角色。例如：

hrdb=# GRANT SELECT, INSERT, UPDATE, DELETE
hrdb-#    ON employees, departments, jobs 
hrdb-#    TO tony WITH GRANT OPTION;

此时，tony 用户不但拥有这些表上的访问权限，还可以将这些权限授予其他角色。

📝除了授权表的访问权限之外，GRANT 语句还支持字段、视图、序列、数据库、函数、过程、模式等对象的授权操作。授权操作的语句基本都类似，具体可以参考官方文档。

撤销授权

与授权操作相反的就是撤销权限，PostgreSQL 使用REVOKE语句撤销数据库对象上的权限。同样以表为例，基本的撤销授权语句如下：

REVOKE privilege_list | ALL
ON TABLE table_name
FROM role_name;

其中的参数和 GRANT 语句一致。例如：

hrdb=# REVOKE SELECT, INSERT, UPDATE, DELETE
hrdb-#     ON employees, departments, jobs
hrdb-#   FROM tony;
REVOKE

该语句撤销了用户 tony 访问 employees、departments 和 jobs 表的权限。

REVOKE 语句也支持对某个模式中的所有对象进行操作：

REVOKE privilege_list | ALL
ON ALL TABLES IN SCHEMA schema_name
FROM role_name;

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

hrdb=# REVOKE SELECT
hrdb-#     ON ALL TABLES IN SCHEMA public
hrdb-#   FROM tony;
REVOKE

📝与 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, ... ;

我们将用户 tony 添加为组 managers 的成员：

postgres=# GRANT managers TO tony;
GRANT ROLE
postgres=# \du
                                    List of roles
 Role name |                         Attributes                         | Member of  
-----------+------------------------------------------------------------+------------
 admin     | Create DB, Cannot login                                    | {}
 managers  | Cannot login                                               | {}
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 tony      | Password valid until 2020-01-01 00:00:00-05                | {managers}

最后一行输出显示了成员角色（tony）所属的组（managers）。

也可以将一个组添加为其他组的成员，因为组角色和非组角色并没有什么本质区别。

postgres=# GRANT admin TO managers;
GRANT ROLE
postgres=# \du
                                    List of roles
 Role name |                         Attributes                         | Member of  
-----------+------------------------------------------------------------+------------
 admin     | Create DB, Cannot login                                    | {}
 managers  | Cannot login                                               | {admin}
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 tony      | Password valid until 2020-01-01 00:00:00-05                | {managers}

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

postgres=# GRANT managers TO admin;
ERROR:  role "managers" is a member of role "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;

使用角色 user1 登录之后，数据库会话将会拥有 user1 自身的特权和 net_admins 所有的特权，因为 user1 “继承”了 net_admins 的特权。但是，会话还不具有 sys_admins 所有的特权，因为即使 user1 间接地成为了 sys_admins 的成员，通过 net_admins 获得的成员资格具有 NOINHERIT 属性，也就不会自动继承权限。

如果执行了以下语句：

SET ROLE net_admins;

会话将会拥有 net_admins 所有的特权，但是不会拥有 user1 自身的特权，也不会继承 sys_admins 所有的特权。

如果执行了以下语句：

SET ROLE sys_admins;

会话将会拥有 sys_admins 所有的特权，但是不会拥有 user1 或者 net_admins 所有的特权。

如果想要恢复初始状态的会话特权，可以执行以下任意语句：

SET ROLE user1;
SET ROLE NONE;
RESET ROLE;

📝在 SQL 标准中，用户和角色之间存在明确的差异，用户不会自动继承特权，而角色会继承特权。PostgreSQL 可以实现这种行为，只需要为角色设置 INHERIT 属性，而为用户设置 NOINHERIT 属性。但是，为了兼容 8.1 之前的版本实现，PostgreSQL 默认为所有的角色都设置了 INHERIT 属性，这样用户总是会继承它所在组的权限。

只有数据库对象上的普通权限可以被继承，角色的 LOGIN、SUPERUSER、CREATEDB 以及 CREATEROLE 属性可以被认为是一些特殊的权限，不会被继承。如果想要使用这些权限，必须使用SET ROLE命令设置为具有这些属性的角色。

基于上面的示例，我们可以为net_admins 角色指定 CREATEDB 和 CREATEROLE 属性。

ALTER ROLE net_admins CREATEDB, CREATEROLE;

然后再使用 user1 连接数据库，会话不会自动具有这些特权，而是需要执行以下命令：

SET ROLE net_admins;

删除角色

删除角色的语句如下：

DROP ROLE name;

如果删除的是组角色，该组中的成员关系会自动从组中删除，但是这些成员角色自身不会受到任何影响。

以下示例删除了角色 admin：

postgres=# drop role admin;
DROP ROLE

由于角色可以拥有数据库中的对象，也可以拥有访问其他对象的权限，删除角色通常不仅仅只是一个简单的DROP ROLE语句。在删除角色之前，需要删除它所拥有的对象，或者将这些对象重新赋予其他的角色；同时还需要撤销授予该角色的权限。详细信息可以参考官方文档。

下一篇我们将会介绍 PostgreSQL 中的数据库和模式的概念。

ITPub博客

《PostgreSQL 开发指南》第 04 篇角色与用户

创建角色

角色属性

对象授权

撤销授权

角色成员

删除角色

最新文章

ITPub博客

《PostgreSQL 开发指南》第 04 篇 角色与用户

创建角色

角色属性

对象授权

撤销授权

角色成员

删除角色

最新文章

《PostgreSQL 开发指南》第 04 篇角色与用户