ALTER ROLE

Function

ALTER ROLE changes the attributes of a role.

Important Notes

None

Syntax

• Modifying the Rights of a Role

  1	ALTER ROLE role_name [ [ WITH ] option [ ... ] ];

  The option clause for granting rights is as follows:

  1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25

  {CREATEDB | NOCREATEDB} | {CREATEROLE | NOCREATEROLE} | {INHERIT | NOINHERIT} | {AUDITADMIN | NOAUDITADMIN} | {SYSADMIN | NOSYSADMIN} | {USEFT | NOUSEFT} | {LOGIN | NOLOGIN} | {REPLICATION | NOREPLICATION} | {INDEPENDENT | NOINDEPENDENT} | {VCADMIN | NOVCADMIN} | CONNECTION LIMIT connlimit | [ ENCRYPTED | UNENCRYPTED ] PASSWORD 'password' | [ ENCRYPTED | UNENCRYPTED ] IDENTIFIED BY 'password' [ REPLACE 'old_password' ] | [ ENCRYPTED | UNENCRYPTED ] PASSWORD { 'password' | DISABLE } | [ ENCRYPTED | UNENCRYPTED ] IDENTIFIED BY { 'password' [ REPLACE 'old_password' ] | DISABLE } | VALID BEGIN 'timestamp' | VALID UNTIL 'timestamp' | RESOURCE POOL 'respool' | USER GROUP 'groupuser' | PERM SPACE 'spacelimit' | NODE GROUP logic_cluster_name | ACCOUNT { LOCK | UNLOCK } | PGUSER | AUTHINFO 'authinfo' | PASSWORD EXPIRATION period

• Rename a role.

  1 2	ALTER ROLE role_name RENAME TO new_name;

• Set parameters for a role.

  1 2	ALTER ROLE role_name [ IN DATABASE database_name ] SET configuration_parameter {{ TO | = } { value | DEFAULT } | FROM CURRENT};

• Reset parameters for a role.

  1 2	ALTER ROLE role_name [ IN DATABASE database_name ] RESET {configuration_parameter|ALL};

Parameters

• role_name

  Indicates a role name.

  Value range: an existing user name

• IN DATABASE database_name

  Modifies the parameters of a role on a specified database.

• SET configuration_parameter

  Sets parameters for a role. The session parameters modified using the ALTER ROLE command is only for a specific role and is valid in the next session triggered by the role.

  Valid value:

  Values of configuration_parameter and value are listed in SET.

  DEFAULT clears the value of configuration_parameter. The value of the configuration_parameter parameter will inherit the default value of the new session generated for the role.

  FROM CURRENT uses the value of configuration_parameter of the current session.

• RESET configuration_parameter/ALL

  The effect of clearing the configuration_parameter value is the same as setting it to DEFAULT.

  Value range: ALL indicates that all parameter values are cleared.

• USEFT | NOUSEF
  Determines whether a new role can perform operations on foreign tables, such as creating, deleting, modifying, and reading/witting foreign tables.

  • USEFT: indicates that the new role or user can perform operations on foreign tables.
  • NOUSEF: indicates that the new role or user does not have permissions to perform operations on foreign tables.
• ACCOUNT LOCK | ACCOUNT UNLOCK
  • ACCOUNT LOCK: locks an account to forbid login to databases.
  • ACCOUNT UNLOCK: unlocks an account to allow login to databases.
• PGUSER

  PGUSER of a role cannot be modified in the current version.

For details about other parameters, see Parameters in CREATE ROLE.

Example

Change the password of role manager:

1

ALTER ROLE manager IDENTIFIED BY '{password}' REPLACE '{old_password}';

Alter role manager to the system administrator:

1

ALTER ROLE manager SYSADMIN;

Modify the fulluser information of the LDAP authentication role:

1

ALTER ROLE role2 WITH LOGIN AUTHINFO 'ldapcn=role2,cn=user2,dc=func,dc=com' PASSWORD DISABLE;

Change the validity period of the login password of the role to 90 days:

1

ALTER ROLE role3 PASSWORD EXPIRATION 90;

Links

CREATE ROLE, DROP ROLE, SET