Manage PostgreSQL users with standard 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 standard 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 does not 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 Clusters page.

    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 authentication selected.

    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. 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 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 creating it. To do this, include the --db-roles or --superuser arguments, as described in the following section.

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 gcloud alloydb users set-roles command to specify all of 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 of 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 commamd:

    gcloud alloydb list users --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 Clusters page.

    Go to Clusters

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

  3. Click Users.

  4. On the row representing 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 Password field.

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

  7. Click OK.

psql

ALTER USER USERNAME WITH PASSWORD PASSWORD;

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

gcloud

Use the 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.

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 gcloud alloydb users set-roles command to specify all of the roles that you want the user to possess, omitting 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 of 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 list users --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 Clusters page.

    Go to Clusters

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

  3. Click Users.

psql

The \du command prints a table of all database users, including their group memberships.

\du

gcloud

Use the 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 Clusters page.

    Go to Clusters

  2. Click the name of the cluster containing the user.

  3. Click Users.

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

  5. Select Remove.

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

psql

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

DROP ROLE USERNAME;

gcloud

Use the 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