Home > APIs > YSQL > The SQL language > SQL statements >

ALTER ROLE

Synopsis

Use the ALTER ROLE statement to change 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.

alter_role ::= ALTER ROLE role_specification 
               [ [ WITH ] alter_role_option [ , ... ] ]

alter_role_option ::= SUPERUSER
                      | NOSUPERUSER
                      | CREATEDB
                      | NOCREATEDB
                      | CREATEROLE
                      | NOCREATEROLE
                      | INHERIT
                      | NOINHERIT
                      | LOGIN
                      | NOLOGIN
                      | CONNECTION LIMIT connlimit
                      | [ ENCRYPTED ] PASSWORD  ' password ' 
                      | PASSWORD NULL
                      | VALID UNTIL  ' timestamp ' 

role_specification ::= role_name | CURRENT_USER | SESSION_USER

alter_role_rename ::= ALTER ROLE role_name RENAME TO new_role_name

alter_role_config ::= ALTER ROLE { role_specification | ALL } 
                      [ IN DATABASE database_name ] config_setting

config_setting ::= SET config_param { TO | = } 
                   { config_value | DEFAULT }
                   | SET config_param FROM CURRENT
                   | RESET config_param
                   | RESET ALL

alter_role

alter_role_option

role_specification

alter_role_rename

alter_role_config

config_setting

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.

yugabyte=# ALTER ROLE John WITH PASSWORD 'new_password';

Rename a role.

yugabyte=# ALTER ROLE John RENAME TO Jane;

Change default_transaction_isolation session parameter for a role.

yugabyte=# ALTER ROLE Jane SET default_transaction_isolation='serializable';