Manage AlloyDB Omni users and roles

In addition to the default users and roles that come with PostgreSQL, you can create other PostgreSQL users or roles. These users have the same set of privileges as the postgres user: CREATE ROLE , CREATEDB , and LOGIN . For more information about these privileges, see CREATE ROLE .

You can change the privileges of any user by using the ALTER ROLE command. If you create a new user with the psql client, you can choose to associate it with a different role, or assign different privileges.

Before you begin

To use PostgreSQL commands to manage users on a cluster, you need the following:

• Access to the psql client
• Access to the postgres database user, or another user with the appropriate administrative privileges

Create a database user

To create a database user that authenticates with the database directly using a username and password—also known as built-in authentication — use the following command:

   
 CREATE 
  
 USER 
  
  USERNAME 
 
  
 WITH 
  
 PASSWORD 
  
 ' PASSWORD 
' 
 ; 
 

Replace the following:

• USERNAME : The user role's username.

• PASSWORD : The new password to assign to the user role.

For more information about how to create and define database users, see CREATE USER .

After you create a user or a role, you can change its privileges by using the ALTER ROLE command in the psql client.

Grant roles to a database user

To grant roles to a database user, use the following command:

  GRANT 
  
  ROLE 
 
  
 to 
  
  USERNAME 
 
 ; 
 

Replace the following:

• ROLE : The role to grant to the database user.

• PASSWORD : The new password to assign to the user role.

To give a user superuser privileges, grant that user the alloydbsuperuser role.

Change the password of a database user

To set a new password for a standard PostgreSQL database user, use the following command:

  ALTER 
  
 USER 
  
  USERNAME 
 
  
 WITH 
  
 PASSWORD 
  
 ' PASSWORD 
' 
 ; 
 

Replace the following:

• USERNAME : The database user's username.

• PASSWORD : The new password to assign to the user role.

For more information about changing a user's password, see ALTER ROLE .

Revoke a role from a database user

To revoke a previously granted privilege from one or more roles, or to revoke a user's membership in a role, use the following command:

   
 REVOKE 
  
  ROLE 
 
  
 FROM 
  
  USERNAME 
 
 ; 
 

To remove a user's superuser privileges, revoke the alloydbsuperuser role from that user.

View a list of database users

To view a table of all database users and their group memberships, use the following command:

   
 \ 
 du 
 

Delete a database user

Before deleting a user, you must drop all the objects it owns or reassign their ownership, and revoke any privileges the role has been granted on other objects.

To delete a user, use the following command:

   
 DROP 
  
 ROLE 
  
  USERNAME 
 
 ;