ALTER ROLE

Synopsis

ALTER ROLE changes the attributes of a role (user or group).

Superusers can change the attributes of any role. Roles with CREATEROLE privilege can change the attributes of any non-superuser role.Other roles can only change their own password.

Syntax

  1. alter_role ::= ALTER ROLE role_specification 
  2.                [ [ WITH ] alter_role_option [ , ... ] ]
  4. alter_role_option ::= SUPERUSER
  5.                       | NOSUPERUSER
  6.                       | CREATEDB
  7.                       | NOCREATEDB
  8.                       | CREATEROLE
  9.                       | NOCREATEROLE
  10.                       | INHERIT
  11.                       | NOINHERIT
  12.                       | LOGIN
  13.                       | NOLOGIN
  14.                       | CONNECTION LIMIT connlimit
  15.                       | [ ENCRYPTED ] PASSWORD  ' password ' 
  16.                       | PASSWORD NULL
  17.                       | VALID UNTIL  ' timestamp ' 
  19. role_specification ::= role_name | CURRENT_USER | SESSION_USER
  21. alter_role_rename ::= ALTER ROLE role_name RENAME TO new_role_name
  23. alter_role_config ::= ALTER ROLE { role_specification | ALL } 
  24.                       [ IN DATABASE database_name ] config_setting
  26. config_setting ::= SET config_param { TO | = } 
  27.                    { config_value | DEFAULT }
  28.                    | SET config_param FROM CURRENT
  29.                    | RESET config_param
  30.                    | RESET ALL

alter_role

ALTER ROLE - 图1

alter_role_option

ALTER ROLE - 图2

role_specification

ALTER ROLE - 图3

alter_role_rename

ALTER ROLE - 图4

alter_role_config

ALTER ROLE - 图5

config_setting

ALTER ROLE - 图6

Where

  • role_specification specifies the name of the role whose attributes are to be changed or current user or current session user.

  • SUPERUSER, NOSUPERUSER determine whether the role is a “superuser” or not. Superusers can override all access restrictions and should be used with care.Only roles with SUPERUSER privilege can create other SUPERUSER roles.

  • CREATEDB, NOCREATEDB determine whether the role can create a database or not.

  • CREATEROLE, NOCREATEROLE determine whether the role can create other roles or not.

  • INHERIT, NOINHERIT determine whether the role inherits privileges of the roles that it is a member of.Without INHERIT, membership in another role only grants the ability to SET ROLE to that other role. The privileges of the other role are only available after having done so.

  • LOGIN, NOLOGIN determine whether new role is allowed to login or not. Only roles with login privilege can be used during client connection.

  • CONNECTION LIMIT specifies how many concurrent connections the role can make. This only applies to roles that can login.

  • [ENCRYPTED] PASSWORD sets the password for the role. This only applies to roles that can login.If no password is specified, the password will be set to null and password authentication will always fail for that user.Note that password is always stored encrypted in system catalogs and the optional keyword ENCRYPTED is only present for compatibility with Postgres.

  • VALID UNTIL sets a date and time after which the role’s password is no longer valid.

  • config_param and config_value are the name and value of configuration parameters being set.

ALTER ROLE role_name RENAME TO can be used to change the name of the role. Note that current session role cannot be renamed.Because MD5-encrypted passwords use the role name as cryptographic salt, renaming a role clears its password if the password is MD5-encrypted.

ALTER ROLE SET | RESET config_param is used to change 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.

Examples

  • Change a role’s password.
  1. yugabyte=# ALTER ROLE John WITH PASSWORD 'new_password';
  • Rename a role.
  1. yugabyte=# ALTER ROLE John RENAME TO Jane;
  • Change default_transaction_isolation session parameter for a role.
  1. yugabyte=# ALTER ROLE Jane SET default_transaction_isolation='serializable';

See also