Manage AlloyDB user roles

This page describes how AlloyDB for PostgreSQL works with PostgreSQL user roles.

About user roles in AlloyDB

An AlloyDB database uses the standard PostgreSQL concept of roles. A role can act as a database user, a group of users, or both.

A user role has the LOGIN privilege that lets users to log in to the system. A group role has member roles with various privileges, which you can grant to or revoke from all members at once.

AlloyDB's predefined PostgreSQL roles

PostgreSQL has a set of predefined roles with various privileges. AlloyDB adds several user and group roles to this set of PostgreSQL's predefined roles.

The following table lists the PostgreSQL roles that AlloyDB predefines:

Role name Privileges
alloydbsuperuser CREATEROLE, CREATEDB, and LOGIN.
postgres CREATEROLE, CREATEDB, and LOGIN.
alloydbimportexport CREATEROLE and CREATEDB.
alloydbagent CREATEROLE and CREATEDB.
alloydbreplica REPLICATION
alloydbiamuser By default, this role does not have any privileges.

The following sections provide information about some of the most commonly used AlloyDB-specific roles.

The alloydbsuperuser group role

The alloydbsuperuser role is a predefined role to initially set up the database system and perform other superuser tasks. This role has the following privileges:

  • Create extensions that require superuser privileges
  • Create event triggers
  • Create replication users
  • Create replication publications and subscriptions

As a managed service, AlloyDB does not let you grant users the PostgreSQL superuser role. Instead, you can give any database user role AlloyDB superuser privileges by granting it the alloydbsuperuser role.

The postgres user role

The postgres user is part of the alloydbsuperuser role. When you create an AlloyDB cluster, you assign a password to the postgres user role. You then log in to your system using the postgres user role to create databases, additional roles, and so on.

The alloydbimportexport user role

When you create an AlloyDB cluster, it creates the default user, alloydbimportexport with the minimal set of privileges that are required for import and export operations.

You have the option to create your own users to perform these operations. If you do not create a custom alloydbimportexport user, the system uses the default alloydbimportexport user for import and export operations.

The alloydbimportexport user is a system user; you cannot directly use the alloydbimportexport user to log in or perform other operations in the PostgreSQL database.

The alloydbiamuser group role

Database users within the alloydbiamuser group role authenticate with an AlloyDB instance by using Identity and Access Management, instead of using standard PostgreSQL password-based authentication.

AlloyDB does not let you grant this role to users using the GRANT PostgreSQL command or similar methods. Instead, you can use AlloyDB administrative tools to create and manage Identity and Access Management-based database users. For more information, see Manage IAM authentication.

Create other PostgreSQL users in AlloyDB

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.

Manage users

You can create and manage AlloyDB user roles 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 the 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 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.

You can instead select Cloud IAM to create a database user that uses IAM to authenticate. This selection requires additional steps to prepare your project and grant appropriate permissions to the new database user. For more information, see Manage IAM authentication.

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

  2. 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 role, 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:

  • USERNAME: The new user role's username.

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

  • CLUSTER_ID: The ID of the cluster to add the new user role to.

  • REGION_ID: The ID of the region that the cluster is located in—for example, us-central1.

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

You can also 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;

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

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:

  • USERNAME: The user role to grant roles to.

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

    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

  • CLUSTER_ID: The ID of the cluster containing the user.

  • REGION_ID: The ID of the region that the cluster is located in—for example, us-central1.

To give a user superuser privileges, you can either manually grant that user the alloydbsuperuser role using the previously described command, 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

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-based 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 whose password you want to change.

  3. Click Users.

  4. On the row representing the user whose password you want to change, 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:

  • USERNAME: The user role's username.

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

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

  • REGION_ID: The ID of the region that the cluster is located in—for example, us-central1.

Revoke a role from a database user

Console

To revoke roles from a database 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:

  • USERNAME: The user role to revoke roles from.

  • ROLES: A comma-separated list of all of the roles you want the specified user to possess, omitting the roles to revoke.

    To find the list of all roles assigned to the user, run the following commamd:

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

  • CLUSTER_ID: The ID of the cluster containing the user.

  • REGION_ID: The ID of the region that the cluster is located in—for example, us-central1.

To revoke superuser privileges, you can either manually remove a user's alloydbsuperuser role using the previously described command, 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: The ID of the cluster whose users to list.

  • REGION_ID: The ID of the region that the cluster is located in—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 you want to remove the user from.

  3. Click Users.

  4. On the row representing the user you want to remove, 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 all the objects it owns or reassign their ownership, and revoke any privileges the role 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: The user role to delete.

  • CLUSTER_ID: The ID of the cluster to remove the user role from.

  • REGION_ID: The ID of the region that the cluster is located in—for example, us-central1.

What's next