ALTER GROUP

Description

Changes the role name or membership.

Precautions

• ALTER GROUP is not a standard SQL statement and therefore is not recommended.
• The ADD USER and DROP USER clauses are used to add users to or delete users from a user group. Any user can be considered a user group. The two clauses are equivalent to granting and recycling memberships respectively. Therefore, you are advised to use the GRANT or REVOKE statement.
• The RENAME TO clause changes the user group name, which is equivalent to naming roles using ALTER USER.

Syntax

• Add users to a group.

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

  

• Remove users from a group.

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

  

• Change the name of the group.

  ALTER GROUP group_name 
      RENAME TO new_name;

  

Parameters

• user_name

  Specifies a role name.

  Value range: an existing role name. For details, see •role_name.

• group_name

  Name of an existing user group.

  Value range: an existing role name. For details, see •role_name.

• new_name

  Name of a new role.

  Value range: a string complying with Identifier Description. For details about the role name requirements, see •role_name.

Examples

• Rename a user group.

  -- Create a user group test.
  m_db=# CREATE GROUP test PASSWORD '********';
  
  -- Changes the username, which is equivalent to the role of ALTER GROUP RENAME.
  m_db=# ALTER GROUP test RENAME TO tu_a1;

• Add users to or delete users from a user group.

  -- Create users tu_a2 and tu_a3.
  m_db=# CREATE ROLE tu_a2 PASSWORD '********';
  m_db=# CREATE ROLE tu_a3 PASSWORD '********';
  
  -- Add user tu_a2 to user group tu_a1.
  m_db=# ALTER GROUP tu_a1 ADD USER tu_a2;
  
  -- The preceding SQL statement is equivalent to the GRANT statement.
  m_db=# GRANT tu_a1 TO tu_a3;
  
  -- Query the result.
  m_db=# SELECT groname, grolist FROM pg_group WHERE groname = 'tu_a1';
   groname |    grolist    
  ---------+---------------
   tu_a1   | {25590,25593}
  (1 row)
  m_db=# SELECT rolname, oid FROM pg_roles WHERE oid IN (25590,25593);
   rolname |  oid  
  ---------+-------
   tu_a2   | 25590
   tu_a3   | 25593
  (2 rows)
  
  -- Delete the users and user group.
  m_db=# DROP ROLE tu_a1,tu_a2,tu_a3;

Helpful Links

CREATE GROUP, DROP GROUP, ALTER ROLE