在 PostgreSQL 中管理角色和角色属性

简介

PostgreSQL 使用各种机制来实现数据库集群中的 身份验证、授权 和对象所有权。其中最重要的是角色的概念。

PostgreSQL 角色将“用户”和“组”的概念组合成一个灵活的实体。它们是用户在数据库系统中采用的身份，是身份验证系统接受或拒绝连接的实体，也是所有范围内的权限管理规则的主题。

本指南将介绍角色是什么以及如何在 PostgreSQL 数据库集群中管理它们。更具体地说，本指南将涵盖与角色属性相关的角色管理。有关角色如何融入更大范围的更广泛概述，请查看身份验证和授权简介指南。要了解如何在特定数据库对象上更改角色权限，请查看我们关于角色授权的指南。

什么是角色？

在 PostgreSQL 中，角色是一组特定的功能、权限和“拥有”的实体。PostgreSQL 没有使用“用户”和“组”的单独概念，而是使用角色来表示这两个概念。角色可以对应于现实世界中的个人，也可以作为一个组，具有其他角色可以成为成员的某些访问权限。

角色是 PostgreSQL 中的锚点，用于确定身份验证和授权策略适用于谁。任何不普遍适用的策略都需要一个身份概念来定义谁可以访问，谁不能访问。在 PostgreSQL 中，这个身份由角色表示。

PostgreSQL 的身份验证系统具有许多不同的组件，每个组件都与角色相关联。为了能够用于与数据库集群的初始连接，角色必须首先设置 LOGIN 属性。身份验证规则本身在名为 pg_hba.conf 的基于主机的配置文件中定义。每个规则定义可能根据单个角色进行范围限定的身份验证方法。对于配置为密码身份验证的角色，必须设置密码属性，以便系统可以验证所提供的用户密码。

在授权方面，角色是在数据库集群级别定义的，在 PostgreSQL 中，这意味着它们在数据库之间共享。由于角色跨越数据库，因此授权系统控制每个角色对每个数据库实体的访问级别。由于角色可以表示一组人，因此在配置访问权限方面具有很大的灵活性。

角色对于 PostgreSQL 中的对象所有权的概念也很重要。例如，每个数据库和表都恰好配置了一个角色作为所有者。除 超级用户 外，所有者角色是唯一可以修改或删除实际对象的

总之，角色是大多数实际数据库操作的核心。它们的灵活性使它们可以同时充当用户标识符和用户类。数据库集群中的每个操作都会根据角色的权限进行检查，并且与数据库集群的每个连接的成功与否都取决于身份验证的角色。由于角色在许多核心操作中都很重要，因此掌握角色管理非常重要。

角色属性

角色属性是角色本身的标志，用于确定它在数据库集群级别上拥有的某些核心权限。这些属性可以在最初创建角色时设置，也可以由具有适当属性（在本例中为 SUPERUSER 或 CREATEROLE）的任何角色随时更改。

可以应用于角色的属性包括

• LOGIN：允许用户使用此角色最初连接到数据库集群。CREATE USER 命令会自动添加此属性，而 CREATE ROLE 命令则不会。
• SUPERUSER：允许角色绕过所有权限检查，除了登录权限。只有其他 SUPERUSER 角色才能创建具有此属性的角色。
• CREATEDB：允许角色创建新数据库。
• CREATEROLE：允许角色创建、更改和删除其他角色。此属性还允许角色分配或更改角色成员身份。例外是，具有 CREATEROLE 属性的角色不能在没有 SUPERUSER 属性的情况下更改 SUPERUSER 角色。
• REPLICATION：允许角色启动流式复制。具有此属性的角色还必须具有 LOGIN 属性。
• PASSWORD：为角色分配一个密码，该密码将与 password 或 md5 身份验证机制一起使用。此属性以引号形式直接在属性关键字之后接受密码作为参数。
• INHERIT: 确定角色是否继承其所属角色的权限。如果没有 INHERIT，成员必须使用 SET ROLE 切换到另一个角色才能访问这些独占权限。此属性默认情况下会为新角色设置。

您可以查看 PostgreSQL 关于 角色属性 和 CREATE ROLE 命令 的文档以了解更多关于角色属性的信息。

什么是 superuser 角色？

如上所述，一个名为 superuser 的特殊权限允许对数据库集群进行无限制的管理访问。这类似于 Linux 和类 Unix 操作系统中的 root 帐户，但是在数据库级别。

每个数据库集群中必须至少有一个具有 superuser 权限的角色。最初的 superuser 帐户是在安装过程中创建的。最初 superuser 帐户的名称可能因安装过程而异，但最常见的是，此帐户称为 postgres。

不建议使用具有 superuser 权限的帐户进行日常工作，原因有两个：一是其可能造成破坏性操作，二是尽量减少损害具有广泛访问权限的帐户的可能性。相反，大多数情况下，用户应该使用专门用于其正在处理的特定功能或数据对象的帐户，只有在需要更强大的访问权限时才使用 superuser 帐户。

检查现有角色属性

现在您已经对角色属性是什么以及它们允许哪些类型的权限有了大致了解，您应该学习如何在整个 PostgreSQL 中查找应用于角色的属性。本节将向您展示一些命令，以帮助您找到在一般角色和您自己的当前角色上设置的属性。

\du

                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

SELECT r.rolname, r.rolsuper, r.rolinherit,
  r.rolcreaterole, r.rolcreatedb, r.rolcanlogin,
  r.rolconnlimit, r.rolvaliduntil,
  ARRAY(SELECT b.rolname
        FROM pg_catalog.pg_auth_members m
        JOIN pg_catalog.pg_roles b ON (m.roleid = b.oid)
        WHERE m.member = r.oid) as memberof
, r.rolreplication
, r.rolbypassrls
FROM pg_catalog.pg_roles r
WHERE r.rolname !~ '^pg_'
ORDER BY 1;

-- turn on vertical display
\x on
SELECT * FROM pg_roles WHERE rolname !~ '^pg_';
-- turn off vertical display
\x off

-[ RECORD 1 ]--+---------
rolname        | postgres
rolsuper       | t
rolinherit     | t
rolcreaterole  | t
rolcreatedb    | t
rolcanlogin    | t
rolreplication | t
rolconnlimit   | -1
rolpassword    | ********
rolvaliduntil  | 
rolbypassrls   | t
rolconfig      | 
oid            | 10

SELECT rolname FROM pg_roles WHERE rolsuper;

 rolname
----------
 postgres
(1 row)

SELECT usename,usesuper FROM pg_user;

 usename  | usesuper
----------+----------
 postgres | t
 user1    | f
(2 rows)

SELECT rolname,rolsuper FROM pg_roles WHERE rolname !~ '^pg_';

 rolname  | rolsuper
----------+----------
 postgres | t
 user1    | f
(2 rows)

\du :USER

                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

-- First, turn on vertical output
\x on
SELECT * FROM pg_roles WHERE rolename = CURRENT_ROLE;
-- Change back to normal output
\x off

-[ RECORD 1 ]--+---------
rolname        | postgres
rolsuper       | t
rolinherit     | t
rolcreaterole  | t
rolcreatedb    | t
rolcanlogin    | t
rolreplication | t
rolconnlimit   | -1
rolpassword    | ********
rolvaliduntil  |
rolbypassrls   | t
rolconfig      |
oid            | 10

SHOW is_superuser;

 is_superuser
--------------
 on
(1 row)

SELECT rolname as "Users who can manage roles" FROM pg_roles WHERE rolsuper OR rolcreaterole;

 Users who can manage roles
----------------------------
 postgres
(1 rows)

SELECT 'Yes' AS "Can I manage roles?" FROM pg_roles WHERE rolname = :'USER' AND (rolsuper OR rolcreaterole);

 Can I manage roles?
---------------------
 Yes
(1 row)

创建角色

验证您拥有角色管理权限后，就可以开始在 PostgreSQL 中创建、修改或删除角色。

设置角色属性的一种方法是在创建角色时声明它们。这允许您为角色设置初始条件，但如果您想更改角色的访问级别，您仍然可以在之后修改它们。您可以找到有关我们将用于熟悉基本语法的 CREATE ROLE 命令 的更多信息。

创建角色的一种方法是从命令行创建。PostgreSQL 包含一个 createuser 命令，它将在数据库集群中创建一个具有 LOGIN 权限的角色。

一般语法为

createuser <options> <rolename>

例如，要创建一个名为 admin 且具有 superuser 权限的角色，同时提示输入密码，您可以键入

createuser --superuser admin

然后，您可以根据 pg_hba.conf 文件 中概述的身份验证方法使用 admin 帐户登录。

要使用 SQL 创建角色，一般语法如下

CREATE ROLE <role>;

可以通过使用 WITH 在角色名称之后指定属性来定义属性

CREATE ROLE <role> WITH <options>;

例如，要创建一个名为 user1 且可以使用密码 secretpassword 登录的角色，您可以键入

CREATE ROLE "user1" WITH LOGIN PASSWORD 'secretpassword';

要创建具有 superuser 权限（您也必须是 superuser 才能成功执行此命令）但 *无法* 登录（用户必须使用 SET ROLE 切换到此角色）的角色，您可以键入

CREATE ROLE "user2" WITH SUPERUSER;

更改现有角色

要修改现有角色的属性，可以使用 ALTER ROLE 命令。与角色创建一样，您当前的角色也必须具有 superuser 或 CREATEROLE 权限。没有这些权限的用户只能使用 ALTER ROLE 命令更改自己的密码。

更改角色允许您在创建后更改分配给角色的属性。角色创建部分中提到的相同属性可与 ALTER ROLE 语法一起使用。不同之处在于，每个属性类型都可以通过添加 NO 前缀来否定。例如，要允许角色登录到数据库集群，您可以赋予它 LOGIN 属性。要删除此功能，您需要通过指定 NOLOGIN 来更改角色。

ALTER ROLE 命令仅更改显式提到的属性。换句话说，ALTER ROLE 命令指定对属性的 *更改*，而不是一组完整的全新属性。

要允许 user2 角色登录到数据库集群，您可以键入

ALTER ROLE "user2" WITH LOGIN;

请记住，虽然这启用了登录功能，但允许的身份验证方法仍然由 pg_hba.conf 文件控制。

如果您希望 user2 能够登录、创建角色和创建数据库，您可以指定这三个属性，用空格分隔。

ALTER ROLE "user2" WITH LOGIN CREATEROLE CREATEDB;

要撤销角色的 superuser 状态（您只能使用另一个 superuser 角色执行此命令），请键入

ALTER ROLE "user2" WITH NOSUPERUSER;

要更改角色的密码，您可以键入以下内容（所有角色都应该能够在其自己的角色上执行此命令，无论是否有 CREATEROLE 或 superuser 权限）

ALTER ROLE <role> WITH PASSWORD '<password>';

虽然上面的命令有效，但如果可能，最好使用 psql 元命令来更改密码。 psql 命令会自动提示输入密码并加密它，然后再将其发送到服务器。 这有助于避免在日志中泄露敏感数据。

您可以使用 psql 键入以下内容来更改角色的密码

-- To change your own password
\password
-- To change the password for another role
\password <role>

您还可以使用 ALTER ROLE 命令重命名角色

ALTER ROLE <role> RENAME TO <newrole>

请记住，您无法重命名当前会话角色。

删除角色

删除现有角色遵循与先前命令类似的模式。 同样，您必须具有 CREATEROLE 或 superuser 权限才能执行这些命令。

一个复杂因素是，如果角色仍然被数据库中的对象引用，则无法删除角色。 这意味着您必须删除或转移角色拥有的任何对象的所属权。 之后，您还必须撤销角色在数据库对象上的任何其他权限。

有关 如何适当地重新分配和删除权限 的详细说明由 Erwin Brandstetter 在数据库管理员 Stack Exchange 网站上提供。 下面使用了相同的流程。

首先，您可以使用 REASSIGNED OWNED 命令重新分配角色的所有对象。 例如，如果您正在准备删除 user2 角色，您可以通过键入以下内容将它的对象分配给 postgres 角色

REASSIGN OWNED BY "user2" TO "postgres";

现在对象由 postgres 拥有，我们可以使用 DROP OWNED 命令撤销我们在对象上获得的所有其他权限。 此命令还会删除我们拥有的任何对象，但由于我们刚刚将它们转移到 postgres 角色，因此 user2 角色不再拥有任何对象。 因此，该命令只会撤销角色的任何其他权限

DROP OWNED BY "user2";

如果没有上面的 DROP OWNED 快捷方式，您将不得不对角色拥有权限的每个对象或对象类型执行 REVOKE ALL PRIVILEGES。

撤销所有关联的权限后，您可以通过键入以下内容来删除该角色

DROP ROLE "user2";

使用 psql 登录

配置好新角色后，并且 使用 pg_hba.conf 文件配置了身份验证详细信息，您可以使用新角色登录到数据库集群。 psql 命令行客户端提供了一种简单的方法来做到这一点。

默认情况下，psql 假设您想使用与您的操作系统用户名匹配的角色进行连接。 因此，如果您以 john 的身份登录到您的计算机，psql 将假设您想尝试使用同样名为 john 的角色连接到数据库。

要覆盖此行为，您可以传递 -U 或 --username= 选项。 例如，如果您想登录到名为 kerry 的角色，您可以键入

psql -U kerry

psql 命令的成功与否将取决于 kerry 角色的存在、您尝试连接的服务器的可访问性以及服务器上定义的身份验证规则。

在会话期间切换到不同的角色

有时，您可能希望暂时采用您有权访问的另一个角色的权限和身份。 例如，如果您想获得您是成员的角色的权限，而您当前的角色没有 INHERIT 属性，则这是必要的。

要了解它是如何工作的，您必须了解 PostgreSQL 用于对活动角色进行分类的术语

• 会话角色：会话角色是您在初始连接到 PostgreSQL 数据库集群时登录的角色。 它设置您的初始权限并确定您对系统的访问权限。 此角色必须具有 LOGIN 属性。
• 当前角色：相反，当前角色是您当前扮演的角色。 与当前角色关联的权限（无论是直接设置的还是从其他角色继承的）决定了您被允许执行的操作以及您有权访问的对象。

您可以通过键入以下内容来查看您的会话和当前角色值

SELECT SESSION_USER, CURRENT_USER;

 current_user | session_user
--------------+--------------
 postgres     | postgres
(1 row)

虽然更改会话角色的唯一方法是使用不同的角色启动新的连接，但您可以使用 SET ROLE 命令 来更改当前角色。 SET ROLE 命令用于临时充当不同的角色。 该命令还可以选择使用以下修饰符

• SESSION：默认设置。 这会导致 SET ROLE 命令影响整个数据库会话。
• LOCAL：此修饰符将使命令仅更改当前事务的角色。

要将当前角色更改为 user2 角色（在会话的剩余时间内），请键入

SET ROLE "user2";

如果您检查您的会话和当前角色值，您会看到当前角色值已更改

SELECT SESSION_USER, CURRENT_USER;

 current_user | session_user
--------------+--------------
 user2        | postgres
(1 row)

您现在执行的所有操作都将使用 user2 角色作为其上下文。

要切换回您之前使用的会话角色，您可以键入

SET ROLE NONE;

另一个实现相同结果的替代方法是

RESET ROLE;

结论

PostgreSQL 的角色、角色属性、授权和身份验证系统创建了一个灵活的系统，允许管理员有效地管理权限和数据库访问。 本指南描述了角色的具体含义以及它们如何涵盖广泛的用例。 它还涵盖了如何创建、修改和删除角色，以及如何管理决定其全局功能的角色属性。 了解如何管理这些身份对于保护您的数据库并为您的合法用户提供可用的访问权限是必要的。

常见问题解答

ALTER ROLE <role> WITH PASSWORD '<password>';

- To change your own password
\password
-- To change the password for another role
\password <role>

\du :USER

                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

SELECT rolname as "Users who can manage roles" FROM pg_roles WHERE rolsuper OR rolcreaterole;

SELECT 'Yes' AS "Can I manage roles?" FROM pg_roles WHERE rolname = :'USER' AND (rolsuper OR rolcreaterole);

CREATE ROLE <role>;

CREATE ROLE <role> WITH <options>;