Postgresql 中文操作指南

22.3. Role Membership #

通常，将用户分组以减轻对权限的管理很方便：这样，权限可以授予或从一个整体组收回。在 PostgreSQL 中，这是通过创建一个表示该组的角色，然后授予 membership 组角色中的各个用户角色来完成的。

It is frequently convenient to group users together to ease management of privileges: that way, privileges can be granted to, or revoked from, a group as a whole. In PostgreSQL this is done by creating a role that represents the group, and then granting membership in the group role to individual user roles.

要设置一个组角色，首先创建角色：

To set up a group role, first create the role:

CREATE ROLE name;

通常作为组使用的角色不会具有 LOGIN 属性，不过如果你愿意，可以设置它。

Typically a role being used as a group would not have the LOGIN attribute, though you can set it if you wish.

一旦组角色存在，您可以使用 GRANT 和 REVOKE 命令添加和删除成员：

Once the group role exists, you can add and remove members using the GRANT and REVOKE commands:

GRANT group_role TO role1, ... ;
REVOKE group_role FROM role1, ... ;

你也可以向其他组角色授予成员资格（因为组角色与非组角色之间实际上没有任何区别）。数据库不会允许你设置循环成员资格循环。此外，不允许向 PUBLIC 中授予成员资格。

You can grant membership to other group roles, too (since there isn’t really any distinction between group roles and non-group roles). The database will not let you set up circular membership loops. Also, it is not permitted to grant membership in a role to PUBLIC.

组角色的成员可以使用两种方式使用角色的特权。首先，已授予 SET 选项的成员角色可以执行 SET ROLE 以暂时“成为”组角色。在此状态下，数据库会话可以访问组角色而非原始登录角色的特权，并且所创建的任何数据库对象都被视为归组角色所有，而不是登录角色所有。其次，已授予 INHERIT 选项的成员角色自动具有直接或间接成为成员的身份的特权，尽管该链会停止在缺少继承选项的成员身份处。例如，假设我们已经执行了以下操作：

The members of a group role can use the privileges of the role in two ways. First, member roles that have been granted membership with the SET option can do SET ROLE to temporarily “become” the group role. In this state, the database session has access to the privileges of the group role rather than the original login role, and any database objects created are considered owned by the group role not the login role. Second, member roles that have been granted membership with the INHERIT option automatically have use of the privileges of those directly or indirectly a member of, though the chain stops at memberships lacking the inherit option. As an example, suppose we have done:

CREATE ROLE joe LOGIN;
CREATE ROLE admin;
CREATE ROLE wheel;
CREATE ROLE island;
GRANT admin TO joe WITH INHERIT TRUE;
GRANT wheel TO admin WITH INHERIT FALSE;
GRANT island TO joe WITH INHERIT TRUE, SET FALSE;

在以 joe 为角色连接之后，数据库会话将有权使用直接授予 joe 的权限，外加授予 admin 和 island 的任何权限，因为 joe “继承”了这些权限。但是，授予 wheel 的权限不可用，因为即使 joe 间接是 wheel 的成员，成员资格也是通过 admin 获得的，而 admin 是使用 WITH INHERIT FALSE 授予的。在：

Immediately after connecting as role joe, a database session will have use of privileges granted directly to joe plus any privileges granted to admin and island, because joe “inherits” those privileges. However, privileges granted to wheel are not available, because even though joe is indirectly a member of wheel, the membership is via admin which was granted using WITH INHERIT FALSE. After:

SET ROLE admin;

之后，会话将只能使用授予 admin 的那些权限，而不能使用授予 joe 或 island 的那些权限。在：

the session would have use of only those privileges granted to admin, and not those granted to joe or island. After:

SET ROLE wheel;

之后，会话将只能使用授予 wheel 的那些权限，而不能使用授予 joe 或 admin 的那些权限。原始权限状态可用以下任何方式还原：

the session would have use of only those privileges granted to wheel, and not those granted to either joe or admin. The original privilege state can be restored with any of:

SET ROLE joe;
SET ROLE NONE;
RESET ROLE;

Note

SET ROLE 命令始终允许选择原始登录角色直接或间接成为其成员的任何角色，前提是有一个成员资格授予链，每个链都有 SET TRUE（这是默认设置）。因此，在上述示例中，在成为 wheel 之前，无需成为 admin。另一方面，根本无法成为 island；joe 只可以通过继承访问这些特权。

The SET ROLE command always allows selecting any role that the original login role is directly or indirectly a member of, provided that there is a chain of membership grants each of which has SET TRUE (which is the default). Thus, in the above example, it is not necessary to become admin before becoming wheel. On the other hand, it is not possible to become island at all; joe can only access those privileges via inheritance.

Note

在 SQL 标准中，用户和角色之间有明显区别，用户不会自动继承特权，而角色会自动继承特权。通过向用作 SQL 角色的角色赋予 INHERIT 属性，同时向用作 SQL 用户的角色赋予 NOINHERIT 属性，可以在 PostgreSQL 中获得这种行为。但是，出于与 8.1 之前的版本（其中用户始终可以使用授予其所在组的权限）向后兼容的目的，PostgreSQL 默认将 INHERIT 属性赋予所有角色。

In the SQL standard, there is a clear distinction between users and roles, and users do not automatically inherit privileges while roles do. This behavior can be obtained in PostgreSQL by giving roles being used as SQL roles the INHERIT attribute, while giving roles being used as SQL users the NOINHERIT attribute. However, PostgreSQL defaults to giving all roles the INHERIT attribute, for backward compatibility with pre-8.1 releases in which users always had use of permissions granted to groups they were members of.

角色属性 LOGIN、SUPERUSER、CREATEDB 和 CREATEROLE 可以视为特殊权限，但它们从不会像普通权限那样继承数据库对象。你必须实际对具有其中一个属性的特定角色执行 SET ROLE 才能使用该属性。继续上面的示例，我们可以选择向 admin 角色授予 CREATEDB 和 CREATEROLE。然后，以 joe 为角色连接的会话将不会立即拥有这些权限，而只有在执行 SET ROLE admin 之后才会有。

The role attributes LOGIN, SUPERUSER, CREATEDB, and CREATEROLE can be thought of as special privileges, but they are never inherited as ordinary privileges on database objects are. You must actually SET ROLE to a specific role having one of these attributes in order to make use of the attribute. Continuing the above example, we might choose to grant CREATEDB and CREATEROLE to the admin role. Then a session connecting as role joe would not have these privileges immediately, only after doing SET ROLE admin.

要销毁组角色，请使用 DROP ROLE ：

To destroy a group role, use DROP ROLE:

DROP ROLE name;

组角色的任何成员资格都会自动被撤销（但成员角色不会受到其他影响）。

Any memberships in the group role are automatically revoked (but the member roles are not otherwise affected).