Postgresql 中文操作指南

ALTER ROLE

ALTER ROLE — 更改数据库角色

ALTER ROLE — change a database role

Synopsis

ALTER ROLE role_specification [ WITH ] option [ ... ]

where option can be:

      SUPERUSER | NOSUPERUSER
    | CREATEDB | NOCREATEDB
    | CREATEROLE | NOCREATEROLE
    | INHERIT | NOINHERIT
    | LOGIN | NOLOGIN
    | REPLICATION | NOREPLICATION
    | BYPASSRLS | NOBYPASSRLS
    | CONNECTION LIMIT connlimit
    | [ ENCRYPTED ] PASSWORD 'password' | PASSWORD NULL
    | VALID UNTIL 'timestamp'

ALTER ROLE name RENAME TO new_name

ALTER ROLE { role_specification | ALL } [ IN DATABASE database_name ] SET configuration_parameter { TO | = } { value | DEFAULT }
ALTER ROLE { role_specification | ALL } [ IN DATABASE database_name ] SET configuration_parameter FROM CURRENT
ALTER ROLE { role_specification | ALL } [ IN DATABASE database_name ] RESET configuration_parameter
ALTER ROLE { role_specification | ALL } [ IN DATABASE database_name ] RESET ALL

where role_specification can be:

    role_name
  | CURRENT_ROLE
  | CURRENT_USER
  | SESSION_USER

Description

ALTER ROLE 更改 PostgreSQL 角色的属性。

ALTER ROLE changes the attributes of a PostgreSQL role.

在概要中列出的此命令的第一个变体可以更改 CREATE ROLE 中可以指定的角色属性（涵盖了所有可能的属性，但没有添加或删除成员资格的选项；请使用 GRANT 和 REVOKE ）。命令中未提及的属性保留其以前的设置。数据库超级用户可以为任何角色更改其中任何设置。具有 CREATEROLE 权限的非超级用户角色可以更改大多数这些属性，但只能针对已向其授予 ADMIN OPTION 的非超级用户和非复制角色进行更改。非超级用户不能更改 SUPERUSER 属性，只有在他们拥有对应的属性时，才能更改 CREATEDB 、 REPLICATION 和 BYPASSRLS 属性。普通角色只能更改他们自己的密码。

The first variant of this command listed in the synopsis can change many of the role attributes that can be specified in CREATE ROLE. (All the possible attributes are covered, except that there are no options for adding or removing memberships; use GRANT and REVOKE for that.) Attributes not mentioned in the command retain their previous settings. Database superusers can change any of these settings for any role. Non-superuser roles having CREATEROLE privilege can change most of these properties, but only for non-superuser and non-replication roles for which they have been granted ADMIN OPTION. Non-superusers cannot change the SUPERUSER property and can change the CREATEDB, REPLICATION, and BYPASSRLS properties only if they possess the corresponding property themselves. Ordinary roles can only change their own password.

第二个变体更改角色的名称。数据库超级用户可以重命名任何角色。具有 CREATEROLE 权限的角色可以重新命名已向其授予 ADMIN OPTION 的非超级用户角色。当前会话用户不能被重命名。（与另一个用户建立连接，如果您需要这样做的话）。因为 MD5 加密密码使用角色名称作为加密 salt，因此如果密码是 MD5 加密，重命名一个角色将清除其密码。

The second variant changes the name of the role. Database superusers can rename any role. Roles having CREATEROLE privilege can rename non-superuser roles for which they have been granted ADMIN OPTION. The current session user cannot be renamed. (Connect as a different user if you need to do that.) Because MD5-encrypted passwords use the role name as cryptographic salt, renaming a role clears its password if the password is MD5-encrypted.

其余变体更改角色的配置变量会话默认值，无论针对所有数据库还是在指定 IN DATABASE 子句时，仅针对指定数据库中的会话进行更改。如果指定 ALL 而不是角色名称，这将更改所有角色的设置。将 ALL 与 IN DATABASE 一起使用实际上与使用命令 ALTER DATABASE …​ SET …​ 相同。

The remaining variants change a role’s session default for a configuration variable, either for all databases or, when the IN DATABASE clause is specified, only for sessions in the named database. If ALL is specified instead of a role name, this changes the setting for all roles. Using ALL with IN DATABASE is effectively the same as using the command ALTER DATABASE …​ SET …​.

无论何时角色随后开始一个新的会话，指定的值就会成为会话默认值，覆盖 postgresql.conf 中存在的任何设置或从 postgres 命令行接收的任何设置。这仅在登录时发生；执行 SET ROLE 或 SET SESSION AUTHORIZATION 不会导致设置新的配置值。针对所有数据库设置的设置将被附加到角色的特定数据库设置覆盖。特定数据库或特定角色的设置将覆盖所有角色的设置。

Whenever the role subsequently starts a new session, the specified value becomes the session default, overriding whatever setting is present in postgresql.conf or has been received from the postgres command line. This only happens at login time; executing SET ROLE or SET SESSION AUTHORIZATION does not cause new configuration values to be set. Settings set for all databases are overridden by database-specific settings attached to a role. Settings for specific databases or specific roles override settings for all roles.

超级用户可以更改任何人的会话默认值。具有 CREATEROLE 权限的角色可以更改已向其授予 ADMIN OPTION 的非超级用户角色的默认值。普通角色只能为自己设置默认值。某些配置变量不能按此方式设置，或者只有在超级用户发布命令时才能设置。只有超级用户可以更改所有数据库中所有角色的设置。

Superusers can change anyone’s session defaults. Roles having CREATEROLE privilege can change defaults for non-superuser roles for which they have been granted ADMIN OPTION. Ordinary roles can only set defaults for themselves. Certain configuration variables cannot be set this way, or can only be set if a superuser issues the command. Only superusers can change a setting for all roles in all databases.

Parameters

  • name #

    • The name of the role whose attributes are to be altered.

  • CURRENT_ROLE__CURRENT_USER #

    • Alter the current user instead of an explicitly identified role.

  • SESSION_USER #

    • Alter the current session user instead of an explicitly identified role.

  • SUPERUSER_NOSUPERUSER_CREATEDB_NOCREATEDB_CREATEROLE_NOCREATEROLE_INHERIT_NOINHERIT_LOGIN_NOLOGIN_REPLICATION_NOREPLICATION_BYPASSRLS_NOBYPASSRLS_CONNECTION LIMIT connlimit[ ENCRYPTED ] PASSWORD 'password'PASSWORD NULL_VALID UNTIL 'timestamp_' #

    • These clauses alter attributes originally set by CREATE ROLE. For more information, see the CREATE ROLE reference page.

  • new_name #

    • The new name of the role.

  • database_name #

    • The name of the database the configuration variable should be set in.

  • configuration_parameter__value #

    • Set this role’s session default for the specified configuration parameter to the given value. If value is DEFAULT or, equivalently, RESET is used, the role-specific variable setting is removed, so the role will inherit the system-wide default setting in new sessions. Use RESET ALL to clear all role-specific settings. SET FROM CURRENT saves the session’s current value of the parameter as the role-specific value. If IN DATABASE is specified, the configuration parameter is set or removed for the given role and database only.

    • Role-specific variable settings take effect only at login; SET ROLE and SET SESSION AUTHORIZATION do not process role-specific variable settings.

    • See SET and Chapter 20 for more information about allowed parameter names and values.

Notes

使用 CREATE ROLE 添加新角色，使用 DROP ROLE 删除角色。

Use CREATE ROLE to add new roles, and DROP ROLE to remove a role.

ALTER ROLE 无法更改角色的成员身份。请使用 GRANT 和 REVOKE 来进行该操作。

ALTER ROLE cannot change a role’s memberships. Use GRANT and REVOKE to do that.

使用此命令指定未加密密码时必须谨慎。此密码将以纯文本形式传输至服务器，且也可能记录于客户端命令历史记录或服务器日志中。 psql 中包含一个命令 \password ，可以使用该命令来更改角色密码且不暴露纯文本密码。

Caution must be exercised when specifying an unencrypted password with this command. The password will be transmitted to the server in cleartext, and it might also be logged in the client’s command history or the server log. psql contains a command \password that can be used to change a role’s password without exposing the cleartext password.

也可以将会话默认值绑定到特定数据库，而不是角色；请参阅 ALTER DATABASE 。如果有冲突，则特定于数据库角色的设置将覆盖特定于角色的设置，而后者又将覆盖特定于数据库的设置。

It is also possible to tie a session default to a specific database rather than to a role; see ALTER DATABASE. If there is a conflict, database-role-specific settings override role-specific ones, which in turn override database-specific ones.

Examples

更改角色密码：

Change a role’s password:

ALTER ROLE davide WITH PASSWORD 'hu8jmn3';

删除角色密码：

Remove a role’s password:

ALTER ROLE davide WITH PASSWORD NULL;

更改密码有效期，指定密码应于 2015 年 5 月 4 日中午（UTC+1 小时时区）过期：

Change a password expiration date, specifying that the password should expire at midday on 4th May 2015 using the time zone which is one hour ahead of UTC:

ALTER ROLE chris VALID UNTIL 'May 4 12:00:00 2015 +1';

使密码永久有效：

Make a password valid forever:

ALTER ROLE fred VALID UNTIL 'infinity';

授予角色管理其他角色和创建新数据库的能力：

Give a role the ability to manage other roles and create new databases:

ALTER ROLE miriam CREATEROLE CREATEDB;

授予角色非默认的 maintenance_work_mem 参数设置：

Give a role a non-default setting of the maintenance_work_mem parameter:

ALTER ROLE worker_bee SET maintenance_work_mem = 100000;

授予角色 client_min_messages 参数的非默认数据库特定设置：

Give a role a non-default, database-specific setting of the client_min_messages parameter:

ALTER ROLE fred IN DATABASE devel SET client_min_messages = DEBUG;

Compatibility

ALTER ROLE 语句是 PostgreSQL 扩展。

The ALTER ROLE statement is a PostgreSQL extension.

See Also

CREATE ROLE 、 DROP ROLE 、 ALTER DATABASE 、 SET

CREATE ROLE, DROP ROLE, ALTER DATABASE, SET