Product Documentation

Appearance

All platforms

ALTER ROLE ​

Alter the attributes and privileges of an existing user or role. ROLE and USER are synonymous keywords in this command.

In general, the ability to alter roles requires the system-level ALTER ANY ROLE privilege. To alter roles and set CREATEROLE or NOCREATEROLE also requires the privilege CREATE ROLE WITH GRANT OPTION. To alter roles and set CREATEDB or NOCREATEDB also requires the privilege CREATE DATABASE WITH GRANT OPTION. See ON SYSTEM.

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

where option can be:

     SUPERUSER | NOSUPERUSER
   | CREATEDB | NOCREATEDB
   | CREATEROLE | NOCREATEROLE
   | INHERIT | NOINHERIT
   | LOGIN | NOLOGIN
   | CONNECTION LIMIT connlimit
   | [ ENCRYPTED | UNENCRYPTED ] PASSWORD 'password'
   | 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 } | FROM CURRENT
ALTER ROLE { role_specification | ALL } [ IN DATABASE database_name ] 
   RESET configuration_parameter | RESET ALL

where role_specification can be:

role_name | CURRENT_USER | SESSION_USER

Note: For system-defined users and roles with ID values less than 16384, you can only alter the password (no other attributes). Query the sys.userandsys.role views to check ID values.

role_specification

Use a valid role name or the current value of the CURRENT_USER or SESSION_USER function.

option

For details about the list of options, see CREATE ROLE.

CAUTION:

If you create and update unencrypted passwords with CREATE ROLE and ALTER ROLE commands, passwords are transmitted in clear text and may appear in log messages.

RENAME

Rename the role.

configuration_parameter

You can set or reset configuration parameters to specific default values for sessions initiated by the specified role.

  • DEFAULT (or RESET) removes the role-specific setting; the role inherits the system-wide default setting in new sessions.
  • RESET ALL removes all role-specific settings.
  • SET FROM CURRENT saves the session's current value as the role-specific value.
  • IN DATABASE sets or removes the parameter for the specific role and database only.

See also the SET, RESET, ALTER SYSTEM, and SHOW commands, also ALTER USER SET DEFAULT_CLUSTER and ALTER USER ALTER PASSWORD_POLICY.

Examples ​

txt
premdb=# alter role henry connection limit 10;
txt
premdb=# alter role all set search_path to 'premdb';