PostgreSQL15——用户和角色

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

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

一、创建角色

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

CREATE ROLE name;
-- 其中name制订了要创建的角色名称

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

select rolname from pg_roles;

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

二、角色属性

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

CREATE ROLE name LOGIN;
CREATE USER name;
-- CREATE USER与CREATE ROLE都可以用于创建角色，只不过CREATE USER默认包含了LOGN选项，而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 'Pass2022' valid until '2025-09-09';
- 或
create user tony with password 'Pass2022' valid until '2025-09-09';

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

create role admin createdb createrole;

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

alter role admin nocreaterole;

三、对象授权

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

GRANT privilege_list | ALL
ON [ TABLE ] table_name
TO role_name;
-- 其中，privilege_list 权限列表可以是SELECT、INSERT、UPDATE、DELETE、TRUNCATE等，ALL 表示表上的所有权限。

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

GRANT SELECT, INSERT, UPDATE, DELETE
ON employees, departments, jobs
TO tony;

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

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

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

-- ALL TABLES IN SCHEMA 表示某个模式中的所有表，可以方便批量授权操作。例如：
GRANT SELECT 
ON ALL TABLES IN SCHEMA public
TO tony;
-- 该语句将 public 模式中所有表的查询权限授予 tony 用户。

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

GRANT SELECT, INSERT, UPDATE, DELETE
ON employees, departments, jobs
TO tony WITH GRANT OPTION;
-- 此时，tony 用户不但拥有这些表上的访问权限，还可以将这些权限授予其他角色。

四、撤销授权

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

REVOKE privilege_list | ALL
ON TABLE table_name
FROM role_name;

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

revoke select, insert, update, delete
on employees, departments, jobs
from tony;
-- 该语句撤销了用户 tony 访问 employees、departments 以及jobs 表的权限。

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

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

-- 例如以下语句撤销了用户 tony 在public 模式中所有表上的查询权限：
revoke select
on all tables in schema public
from tony;

五、角色成员

在现实的环境中，管理员通常需要管理大量的用户和对象权限。为了便于权限管理，减少复杂度，可以将用户进行分组，然后以组为单位进行权限的授予和撤销操作。

为此，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 的成员：

grant managers to tony;

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

grant admin to managers;

PostgreSQL 不允许设置循环的成员关系，也就是两个角色互相为对方的成员。最后，不能将特殊角色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：

drop role admin;

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