ALTER GROUP

Description

Modifies the attributes of a user group.

Precautions

ALTER GROUP is not a standard SQL statement and is not recommended.
Two clauses (ADD USER and DROP USER) are used to add users to or delete users from a user group. Any user can be a user or a user group. These two clauses are equivalent to granting or revoking the permissions of a user or role to other users or roles. Therefore, you are advised to replace them with GRANT or REVOKE.
The RENAME TO clause changes the user group name, which is equivalent to renaming roles using ALTER ROLE.

Syntax

Add users to a group.

ALTER GROUP group_name 
    ADD USER user_name [, ... ];

Click to enlarge

Remove users from a group.

ALTER GROUP group_name 
    DROP USER user_name [, ... ];

Click to enlarge

Change the name of the group.

ALTER GROUP group_name 
    RENAME TO new_name;

Click to enlarge

Parameters

user_name
Role name.

Value range: an existing role name. If a role name contains uppercase letters, enclose the name with double quotation marks ("").
group_name
Name of an existing user group.

Value range: an existing role name. If a role name contains uppercase letters, enclose the name with double quotation marks ("").

new_name
Name of a new role.

Value range: a string. It must comply with the identifier naming convention and can contain a maximum of 63 characters. If the value contains more than 63 characters, the database truncates it and retains the first 63 characters as the role name. If a role name contains uppercase letters, the database automatically converts the uppercase letters into lowercase letters. To create a role name that contains uppercase letters, enclose the role name with double quotation marks ("").

Examples

Rename a user group.

-- Create a user test.
gaussdb=# CREATE ROLE test PASSWORD '********';

-- Change the username, which is equivalent to ALTER ROLE RENAME.
gaussdb=# ALTER GROUP test RENAME TO tu_a1;

Add users to or delete users from a user group.

-- Create users tu_a2 and tu_a3.
gaussdb=# CREATE ROLE tu_a2 PASSWORD '********';
gaussdb=# CREATE ROLE tu_a3 PASSWORD '********';

-- Add user tu_a2 to user group tu_a1.
gaussdb=# ALTER GROUP tu_a1 ADD USER tu_a2;

-- The preceding SQL statement is equivalent to the GRANT statement.
gaussdb=# GRANT tu_a1 TO tu_a3;

-- Query.
gaussdb=# SELECT groname, grolist FROM pg_group WHERE groname = 'tu_a1';
 groname |    grolist    
---------+---------------
 tu_a1   | {25590,25593}
(1 row)
gaussdb=# SELECT rolname, oid FROM gs_roles WHERE oid IN (25590,25593);
 rolname |  oid  
---------+-------
 tu_a2   | 25590
 tu_a3   | 25593
(2 rows)

-- Delete.
gaussdb=# DROP ROLE tu_a1,tu_a2,tu_a3;