Updated on 2024-08-20 GMT+08:00

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.
    1
    2
    ALTER GROUP group_name 
        ADD USER user_name [, ... ];
    

  • Remove users from a group.
    1
    2
    ALTER GROUP group_name 
        DROP USER user_name [, ... ];
    

  • Change the name of the group.
    1
    2
    ALTER GROUP group_name 
        RENAME TO new_name;
    

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.
    1
    2
    3
    4
    5
    -- Create a user test.
    gaussdb=# CREATE ROLE test PASSWORD '********';
    
    -- Change the username, which is equivalent to the role of 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 pg_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;

Helpful Links

CREATE GROUP, DROP GROUP, and ALTER ROLE