About PostgreSQL users and roles

Stay organized with collections Save and categorize content based on your preferences.

This page describes how Cloud SQL works with PostgreSQL users and roles. PostgreSQL roles enable you to control the access and capabilities of users who access a PostgreSQL instance.

For complete documentation about PostgreSQL roles, see Database Roles in the PostgreSQL documentation. For information about creating and managing Cloud SQL users, see Create and manage users.

Difference between users and roles

PostgreSQL roles can be a single role, or they can function as a group of roles. A user is a role with the ability to log in (the role has the LOGIN attribute). Because all roles Cloud SQL creates have the LOGIN attribute, Cloud SQL uses the terms role and user interchangeably. However, if you create a role with the psql client, the role does not necessarily have the LOGIN attribute.

All PostgreSQL users must have a password. You cannot log in with a user that lacks a password.

Superuser restrictions

Cloud SQL for PostgreSQL is a managed service, so it restricts access to certain system procedures and tables that require advanced privileges. In Cloud SQL, customers cannot create or have access to users with superuser attributes.

You cannot create database users that have superuser privileges. However, you can create database users with the cloudsqlsuperuser role, which has some superuser privileges, including:

  • Creating extensions that require superuser privileges
  • Creating event triggers
  • Creating replication users
  • Creating replication publications and subscriptions
  • Full access to the pg_largeobject catalog table

Default PostgreSQL users

When you create a new Cloud SQL for PostgreSQL instance, the default admin user postgres is created but not its password. You need to set a password for this user before you can log in. You can do this either in the Google Cloud console or by using the following gcloud command:

gcloud sql users set-password postgres \
--instance=INSTANCE_NAME \
--password=PASSWORD

The postgres user is part of the cloudsqlsuperuser role, and has the following attributes (privileges): CREATEROLE, CREATEDB, and LOGIN. It does not have the SUPERUSER or REPLICATION attributes.

A default cloudsqlimportexport user is created with the minimal set of privileges needed for CSV import/export operations. You can create your own users to perform these operations, but if you don't, the default cloudsqlimportexport user is used. The cloudsqlimportexport user is a system user and customers cannot directly use it.

Cloud SQL IAM users for IAM database authentication

IAM is integrated with Cloud SQL in a feature called IAM database authentication. When you create instances using this feature, IAM users can log in to the instance using their IAM username and password. The advantage to using IAM database authentication is that you can use a user's existing IAM credentials when granting them access to a database. When the user leaves the organization, their IAM account is suspended, automatically removing their access. To learn more about IAM database authentication, see Cloud SQL IAM database authentication.

Other PostgreSQL users

You can create other PostgreSQL users or roles. All users you create using Cloud SQL are created as part of the cloudsqlsuperuser role, and have the same set of attributes as the postgres user: CREATEROLE, CREATEDB, and LOGIN. You can change the attributes 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 give it different attributes.

Access to the pg_shadow view

You can use the pg_shadow view to work with the properties of roles that are marked as rolcanlogin in the pg_authid catalog.

The pg_shadow view contains hashed passwords and other properties of the roles (users) allowed to log in to a cluster. In Cloud SQL, customers cannot access the pg_shadow view using the default privileges. However, access to role names and hashed passwords is useful in certain situations, including:

  • Setting up proxies or load balancing with existing users and passwords
  • Migrating users without changes in passwords
  • Implementing custom solutions for password policy management

Setting the flag for the pg_shadow view

To access the pg_shadow view, set the cloudsql.pg_shadow_select_role flag to a PostgreSQL role name. If the role exists, it has read-only (SELECT) access to the pg_shadow view.

If the role doesn't exist, the setting has no effect, but no error occurs. However, an error is logged when a user tries to access the view. The error is logged in the PostgreSQL database log, cloudsql.googleapis.com/postgres.log. For information about viewing this log, see View instance logs. Ensure that the configured role exists and that there isn't a typo in the value of the cloudsql.pg_shadow_select_role flag. You also can use the pg_has_role function to verify that a user is a member of a role. Information about this function is available on the System Information Functions and Operators page.

You can use the cloudsql.pg_shadow_select_role flag with PostgreSQL role membership to manage pg_shadow access for multiple users.

Changes to the flag do not require a database restart.

For more information about supported flags, see Configure database flags.

Choose a password storage format

Cloud SQL for PostgreSQL stores user passwords in a hashed format. You can use the password_encryption flag to set the encryption algorithm to md5 or scram-sha-256. The md5 algorithm provides the broadest compatibility, whereas scram-sha-256 is more secure but might be incompatible with older clients.

When enabling pg_shadow access to export role properties from a Cloud SQL instance, consider using the most secure algorithm supported by your clients.

In the PostgreSQL documentation, also see:

What's next