Console
    Start free

    Manage PostgreSQL users with built-in authentication

    This page describes how to create, manage, and remove both PostgreSQL and AlloyDB for PostgreSQL roles, users, and groups using the password-based PostgreSQL built-in authentication methods.

    Create PostgreSQL users in AlloyDB

    When you create PostgreSQL users, they only start with the LOGIN privilege. For more information about these privileges, see CREATE USER .

    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.

    Manage users

    You can create and manage AlloyDB users with the Google Cloud CLI, or by using PostgreSQL commands. The following sections demonstrate common user management tasks using either method.

    Before you begin

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

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

    To use the Google Cloud console or Google Cloud CLI to manage users on a cluster, that cluster must have a primary instance. If your cluster doesn't have a primary instance, then you must create one before you can manage users.

    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 , follow the steps in this section.

    To instead create a database user that uses Identity and Access Management (IAM) to authenticate, see Manage IAM authentication .

    Console

    1. Go to the Clusterspage.

      Go to Clusters

    2. Click the name of the cluster where you want to add a user.

    3. Click Users.

    4. Click Add user account.

    5. Leave Built-in authenticationselected.

      If you want to use IAM to authenticate and sign into your database clusters, you must complete additional steps to prepare your project and grant appropriate permissions to your new user. For more information, see Manage IAM authentication .

    6. Enter a username and password for the new user.

    7. To assign roles other than the default alloydbsuperuser to the user, follow these steps:

      1. Enter the name of the role in Roles.
      2. To add the role, press Enter or Tab .

      3. Select xfor any role that you want to remove for that user.

        For more information, see Predefined roles .

    8. Click Add.

    psql

      CREATE 
  
 USER 
  
  USERNAME 
 
  
 WITH 
  
 PASSWORD 
  
  PASSWORD 
 
 ;

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

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

    gcloud

    Use the following gcloud alloydb users create command:

     gcloud  
alloydb  
users  
create  
 USERNAME 
  
 \ 
--password = 
 PASSWORD 
  
 \ 
--cluster = 
 CLUSTER_ID 
  
 \ 
--region = 
 REGION_ID

    Replace the following variables:

    • USERNAME : User's username. USERNAME must follow the rules for SQL identifiers: without any special characters, or wrapped in double quotes. For example, db_user_1 .

    • PASSWORD : User's password. To create the user without a password, omit this argument.

    • CLUSTER_ID : ID of the cluster to add the user to.

    • REGION_ID : ID of the region where the cluster resides. For example, us-central1 .

    After you create a user, you can change its privileges by using the gcloud alloydb users set-roles command.

    Additionally, you can grant roles or superuser privileges to a user while you create it. To do this, include the --db-roles or --superuser arguments, as the following section describes.

    Grant roles to a database user

    Console

    To grant roles to a database user, use psql or the gcloud CLI, instead of the Google Cloud console.

    psql

      GRANT 
  
  ROLE 
 
  
 to 
  
  USERNAME 
 
 ;

    Replace the following variables:

    • USERNAME : User's username.
    • ROLE : Role to grant the user. For example, to give the user superuser privileges, grant them alloydbsuperuser .

    gcloud

    Use the following gcloud alloydb users set-roles command to specify all the roles that you want the user to possess:

     gcloud  
alloydb  
users  
set-roles  
USERNAME  
 \ 
--db-roles = 
 ROLES 
  
 \ 
--cluster = 
 CLUSTER_ID 
  
 \ 
--region = 
 REGION_ID

    Replace the following variables:

    • USERNAME : User's username.

    • ROLES : Comma-separated list of all the roles you want the user to possess. Make sure that you list all roles, which includes existing and new roles.

      To find the list of existing roles assigned to the user, run the following command:

       gcloud  
alloydb  
users  
list  
--cluster = 
 CLUSTER_ID 
  
--region = 
 REGION_ID

      Replace the following variables:

      • CLUSTER_ID : ID of the cluster containing the user.

      • REGION_ID : ID of the region where the cluster resides. For example, us-central1 .

      To give a user superuser privileges, you can either manually grant that user the alloydbsuperuser role using psql , or you can run the gcloud alloydb users set-superuser command with the --superuser=true argument:

       gcloud  
alloydb  
users  
set-superuser  
USERNAME  
 \ 
--superuser = 
 true 
  
 \ 
--cluster = 
 CLUSTER_ID 
  
 \ 
--region = 
 REGION_ID

      Replace the following variables:

      • CLUSTER_ID : ID of the cluster containing the user.

      • REGION_ID : ID of the region where the cluster resides. For example, us-central1 .

    Change the password of a database user

    To set a new password for a standard PostgreSQL database user, follow the steps in this section.

    You can't change or set the password of an IAM-based user because IAM users don't use passwords to authenticate. For more information, see Connect using an IAM account .

    Console

    1. Go to the Clusterspage.

      Go to Clusters

    2. Click the name of the cluster that contains the database user.

    3. Click Users.

    4. On the row that represents the user, click Open actions for this user.

    5. Select Change password.

    6. Specify a password:

      • To set a new password for this user, type the password into the Passwordfield.

      • To set no password for this user, select the No passwordcheckbox.

    7. Click OK.

    psql

      ALTER 
  
 USER 
  
  USERNAME 
 
  
 WITH 
  
 PASSWORD 
  
  PASSWORD 
 
 ;

    For more information about how to change a user's password, see ALTER ROLE .

    gcloud

    Use the following gcloud alloydb users set-password command:

     gcloud  
alloydb  
users  
set-password  
 USERNAME 
  
 \ 
--password = 
 PASSWORD 
  
 \ 
--cluster = 
 CLUSTER_ID 
  
 \ 
--region = 
 REGION_ID

    Replace the following variables:

    • USERNAME : User's username.

    • PASSWORD : New password to assign to the user.

    • CLUSTER_ID : ID of the cluster that the user belongs to.

    • REGION_ID : ID of the region where the cluster resides. For example, us-central1 .

    Revoke a role from a database user

    Console

    To revoke roles from a user, use psql or the gcloud CLI, instead of the Google Cloud console.

    psql

    You can revoke previously granted privileges from one or more roles or revoke a user's membership in a role, as shown:

      REVOKE 
  
  ROLE 
 
  
 FROM 
  
  USERNAME 
 
 ;

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

    gcloud

    To revoke a role with the Google Cloud CLI, use the following gcloud alloydb users set-roles command to specify all the roles that you want the user to possess, and to omit roles to revoke from the user:

     gcloud  
alloydb  
users  
set-roles  
 USERNAME 
  
 \ 
--db-roles = 
 ROLES 
  
 \ 
--cluster = 
 CLUSTER_ID 
  
 \ 
--region = 
 REGION_ID

    Replace the following variables:

    • USERNAME : User's username.

    • ROLES : Comma-separated list of all the roles you want the user to possess. Make sure that you list all roles, including existing and new roles.

      To find the list of existing roles assigned to the user, run the following command:

       gcloud  
alloydb  
users  
list  
--cluster = 
 CLUSTER_ID 
  
--region = 
 REGION_ID

    • CLUSTER_ID : ID of the cluster containing the user.

    • REGION_ID : ID of the region where the cluster resides. For example, us-central1 .

    To revoke superuser privileges, you can either manually remove a user's alloydbsuperuser role using psql , or you can run the gcloud alloydb users set-superuser command with the --superuser=false argument.

    View a list of database users

    Console

    1. Go to the Clusterspage.

      Go to Clusters

    2. Click the name of the cluster whose users you want to view.

    3. Click Users.

    psql

    Use the following \du command to print a table of all database users, which includes their group memberships:

      \ 
 du

    gcloud

    Use the following gcloud alloydb users list command:

     gcloud  
alloydb  
users  
list  
 \ 
--cluster = 
 CLUSTER_ID 
  
 \ 
--region = 
 REGION_ID

    Replace the following:

    • CLUSTER_ID : ID of the cluster containing the users.

    • REGION_ID : ID of the region where the cluster resides. For example, us-central1 .

    Delete a database user

    Console

    1. Go to the Clusterspage.

      Go to Clusters

    2. Click the name of the cluster that contains the user.

    3. Click Users.

    4. On the row that represents the user, click Open actions for this user.

    5. Select Remove.

    6. In the Remove user account?dialog, click Remove.

    psql

    Before you delete a user, you must drop, or reassign, all the objects they own, and revoke any privileges the user has been granted on other objects. Use the following:

      DROP 
  
 ROLE 
  
  USERNAME 
 
 ;

    gcloud

    Use the following gcloud alloydb users delete command:

     gcloud  
alloydb  
users  
delete  
 USERNAME 
  
 \ 
--cluster = 
 CLUSTER_ID 
  
 \ 
--region = 
 REGION_ID

    Replace the following:

    • USERNAME : User's username.

    • CLUSTER_ID : ID of the cluster that the user belongs to.

    • REGION_ID : ID of the region where the cluster resides. For example, us-central1 .

    What's next
    Design a Mobile Site
    View Site in Mobile | Classic
    Share by: