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