Postgresql 中文操作指南

22.2. Role Attributes #

数据库角色可以具有许多定义其权限并与客户端身份验证系统交互的属性。

A database role can have a number of attributes that define its privileges and interact with the client authentication system.

  • login privilege

    • Only roles that have the LOGIN attribute can be used as the initial role name for a database connection. A role with the LOGIN attribute can be considered the same as a “database user”. To create a role with login privilege, use either:

CREATE ROLE name LOGIN;
CREATE USER name;

  • (CREATE USER is equivalent to CREATE ROLE except that CREATE USER includes LOGIN by default, while CREATE ROLE does not.)

    • superuser status

  • A database superuser bypasses all permission checks, except the right to log in. This is a dangerous privilege and should not be used carelessly; it is best to do most of your work as a role that is not a superuser. To create a new database superuser, use CREATE ROLE _name SUPERUSER_. You must do this as a role that is already a superuser.

    • database creation

  • A role must be explicitly given permission to create databases (except for superusers, since those bypass all permission checks). To create such a role, use CREATE ROLE _name CREATEDB_.

    • role creation

  • A role must be explicitly given permission to create more roles (except for superusers, since those bypass all permission checks). To create such a role, use CREATE ROLE _name CREATEROLE_. A role with CREATEROLE privilege can alter and drop roles which have been granted to the CREATEROLE user with the ADMIN option. Such a grant occurs automatically when a CREATEROLE user that is not a superuser creates a new role, so that by default, a CREATEROLE user can alter and drop the roles which they have created. Altering a role includes most changes that can be made using ALTER ROLE, including, for example, changing passwords. It also includes modifications to a role that can be made using the COMMENT and SECURITY LABEL commands.

  • However, CREATEROLE does not convey the ability to create SUPERUSER roles, nor does it convey any power over SUPERUSER roles that already exist. Furthermore, CREATEROLE does not convey the power to create REPLICATION users, nor the ability to grant or revoke the REPLICATION privilege, nor the ability to modify the role properties of such users. However, it does allow ALTER ROLE …​ SET and ALTER ROLE …​ RENAME to be used on REPLICATION roles, as well as the use of COMMENT ON ROLE, SECURITY LABEL ON ROLE, and DROP ROLE. Finally, CREATEROLE does not confer the ability to grant or revoke the BYPASSRLS privilege.

    • initiating replication

  • A role must explicitly be given permission to initiate streaming replication (except for superusers, since those bypass all permission checks). A role used for streaming replication must have LOGIN permission as well. To create such a role, use CREATE ROLE _name REPLICATION LOGIN_.

    • password

  • A password is only significant if the client authentication method requires the user to supply a password when connecting to the database. The password and md5 authentication methods make use of passwords. Database passwords are separate from operating system passwords. Specify a password upon role creation with CREATE ROLE _name PASSWORD 'string'_.

    • inheritance of privileges

  • A role inherits the privileges of roles it is a member of, by default. However, to create a role which does not inherit privileges by default, use CREATE ROLE _name NOINHERIT_. Alternatively, inheritance can be overridden for individual grants by using WITH INHERIT TRUE or WITH INHERIT FALSE.

    • bypassing row-level security

  • A role must be explicitly given permission to bypass every row-level security (RLS) policy (except for superusers, since those bypass all permission checks). To create such a role, use CREATE ROLE _name BYPASSRLS_ as a superuser.

    • connection limit

  • Connection limit can specify how many concurrent connections a role can make. -1 (the default) means no limit. Specify connection limit upon role creation with CREATE ROLE _name CONNECTION LIMIT 'integer'_.

角色的属性可以在创建后使用 ALTER ROLE 进行修改。有关详细信息，请参见 CREATE ROLE 和 ALTER ROLE 命令的参考页面。

A role’s attributes can be modified after creation with ALTER ROLE. See the reference pages for the CREATE ROLE and ALTER ROLE commands for details.

角色还可以对 Chapter 20中描述的许多运行时配置设置拥有特定于角色的默认设置。例如，如果出于某种原因，希望在每次连接时禁用索引扫描（提示：这不是一个好主意），则可以使用：

A role can also have role-specific defaults for many of the run-time configuration settings described in Chapter 20. For example, if for some reason you want to disable index scans (hint: not a good idea) anytime you connect, you can use:

ALTER ROLE myname SET enable_indexscan TO off;

这将保存设置（但不会立即设置它）。在此角色后续的连接中，它将显示为 SET enable_indexscan TO off 在会话开始之前已被执行。你仍然可以在会话期间更改此设置；它将只是默认设置。要删除特定于角色的默认设置，请使用 ALTER ROLE _rolename RESET varname_ 。请注意，附加到没有 LOGIN 权限的角色的特定于角色的默认设置几乎没用，因为它们永远不会被调用。

This will save the setting (but not set it immediately). In subsequent connections by this role it will appear as though SET enable_indexscan TO off had been executed just before the session started. You can still alter this setting during the session; it will only be the default. To remove a role-specific default setting, use ALTER ROLE _rolename RESET varname_. Note that role-specific defaults attached to roles without LOGIN privilege are fairly useless, since they will never be invoked.

当非超级用户使用 CREATEROLE 权限创建角色时，创建的角色会自动授予创建用户，就像引导超级用户执行了 GRANT created_user TO creating_user WITH ADMIN TRUE, SET FALSE, INHERIT FALSE 命令一样。由于 CREATEROLE 用户只能对现有角色行使特殊权限（如果他们在该角色上有 ADMIN OPTION），因此此授予仅足以允许 CREATEROLE 用户管理他们创建的角色。但是，由于它使用 INHERIT FALSE, SET FALSE 创建，因此 CREATEROLE 用户不会继承创建角色的权限，也不能使用 SET ROLE 访问该角色的权限。但是，由于拥有 ADMIN OPTION 的任何用户可以让另一个用户加入该角色，因此 CREATEROLE 用户可以通过使用 INHERIT 和/或 SET 选项将该角色授予他们自己，从而获得创建角色的访问权限。因此，权限默认情况下不被继承且 SET ROLE 默认情况下不是授予的是针对事故的保护措施，而不是安全功能。还要注意，由于此自动授予是由引导用户授予的，因此 CREATEROLE 用户无法删除或更改它；但是，任何超级用户都可以撤消它、修改它和/或向其他 CREATEROLE 用户发出此类其他授予。任何在任何给定时间在角色上有 ADMIN OPTION 的 CREATEROLE 用户都可以对其进行管理。

When a non-superuser creates a role using the CREATEROLE privilege, the created role is automatically granted back to the creating user, just as if the bootstrap superuser had executed the command GRANT created_user TO creating_user WITH ADMIN TRUE, SET FALSE, INHERIT FALSE. Since a CREATEROLE user can only exercise special privileges with regard to an existing role if they have ADMIN OPTION on it, this grant is just sufficient to allow a CREATEROLE user to administer the roles they created. However, because it is created with INHERIT FALSE, SET FALSE, the CREATEROLE user doesn’t inherit the privileges of the created role, nor can it access the privileges of that role using SET ROLE. However, since any user who has ADMIN OPTION on a role can grant membership in that role to any other user, the CREATEROLE user can gain access to the created role by simply granting that role back to themselves with the INHERIT and/or SET options. Thus, the fact that privileges are not inherited by default nor is SET ROLE granted by default is a safeguard against accidents, not a security feature. Also note that, because this automatic grant is granted by the bootstrap user, it cannot be removed or changed by the CREATEROLE user; however, any superuser could revoke it, modify it, and/or issue additional such grants to other CREATEROLE users. Whichever CREATEROLE users have ADMIN OPTION on a role at any given time can administer it.