ALTER ROLE

Description

Modifies role attributes.

Precautions

None

Syntax

Modify the permissions of a role.

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

The option clause for granting permissions is as follows.

        
         
           
           
             {CREATEDB | NOCREATEDB}
    | {CREATEROLE | NOCREATEROLE}
    | {INHERIT | NOINHERIT}
    | {AUDITADMIN | NOAUDITADMIN}
    | {SYSADMIN | NOSYSADMIN}
    | {MONADMIN | NOMONADMIN}
    | {OPRADMIN | NOOPRADMIN}
    | {POLADMIN | NOPOLADMIN}
    | {USEFT | NOUSEFT}
    | {LOGIN | NOLOGIN}
    | {REPLICATION | NOREPLICATION}
   
    | {PERSISTENCE | NOPERSISTENCE}
    | CONNECTION LIMIT connlimit
    | [ ENCRYPTED | UNENCRYPTED ] PASSWORD { 'password' [EXPIRED] | DISABLE | EXPIRED }
    | [ ENCRYPTED | UNENCRYPTED ] IDENTIFIED BY { 'password' [ REPLACE 'old_password' | EXPIRED ] | DISABLE }
    | VALID BEGIN 'timestamp'
    | VALID UNTIL 'timestamp'
    | RESOURCE POOL 'respool'
    | USER GROUP 'groupuser'
    | PERM SPACE 'spacelimit'
    | TEMP SPACE 'tmpspacelimit'
    | SPILL SPACE 'spillspacelimit'
    | NODE GROUP logic_cluster_name
    | ACCOUNT { LOCK | UNLOCK }
    | PGUSER

            

          

        
       

Rename a role.

      
           ALTER ROLE role_name 
    RENAME TO new_name;

Lock or unlock.

ALTER ROLE role_name 
    ACCOUNT { LOCK | UNLOCK };

Set parameters for a role.

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

Reset parameters for a role.

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

Parameters

role_name
Specifies a role name.

Value range: an existing role name. If a role name contains uppercase letters, enclose the name with double quotation marks ("").
IN DATABASE database_name
Modifies the parameters of a role in a specified database.
SET configuration_parameter {{ TO | = } { value | DEFAULT } | FROM CURRENT}
Sets parameters for a role. The session parameters modified using ALTER ROLE is only for a specific role and is valid in the next session triggered by the role.

The current version does not support setting user-level parameters.

Value range:

For details about the values of configuration_parameter and value, see SET.

DEFAULT: clears the value of configuration_parameter. configuration_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}
Clears the value of configuration_parameter. The statement has the same effect as that of SET configuration_parameter TO DEFAULT.

The current version does not support the resetting of user-level parameters.

Value range: ALL indicates that the values of all parameters are cleared.
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
In the current version, the PGUSER permission of a role cannot be modified.
{PASSWORD|IDENTIFIED BY} 'password'
Resets or changes the user password. Except the initial user, other administrators and common users need to enter the correct old password when changing their own passwords. Only the initial user, the system administrators (sysadmin) when separation of duties is disabled, or users who have the permission (CREATEROLE) to create users can reset the password of a common user without entering the old password. The initial user can reset passwords of system administrators. A system administrator cannot reset passwords of other system administrators.
EXPIRED
Invalidates the password. Only the initial user, the system administrators (sysadmin), or users who have the permission to create a user (CREATEROLE) can invalidate the user password. A system administrator can invalidate the password of itself or other system administrators only when the separation of duties is disabled. The password of the initial user cannot be invalidated.

The user whose password is invalid can log in to the database but cannot perform the query operation. The query operation can be performed only after the password is changed or the administrator resets the password.

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

Examples

-- Create role test_role.
gaussdb=# CREATE ROLE test_role PASSWORD '********';
 
-- Set role test_role to log in to the database.
gaussdb=# ALTER ROLE test_role WITH LOGIN;

-- Lock role test_role.
gaussdb=# ALTER ROLE test_role ACCOUNT LOCK;

-- Unlock a locked role.
gaussdb=# ALTER ROLE test_role ACCOUNT UNLOCK;

-- Change the password of role test_role.
gaussdb=# ALTER ROLE test_role PASSWORD '********';

-- Rename role test_role to test_role2.
gaussdb=# ALTER ROLE test_role RENAME TO test_role2;

-- Change role test_role2 to the system administrator.
gaussdb=# ALTER ROLE test_role2 SYSADMIN;

-- Delete.
gaussdb=# DROP ROLE test_role2;