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

For complete documentation about PostgreSQL roles, seeDatabase Roles in the PostgreSQL documentation. Forinformation about creating and managing Cloud SQL users, seeCreate 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 theLOGINattribute). Because all roles Cloud SQL creates have theLOGINattribute, Cloud SQL uses the termsrole anduser interchangeably.However, if you create a role with thepsql client, the role does notnecessarily have theLOGIN attribute.

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

Superuser restrictions and privileges

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

You can't create database users that have superuser privileges. However, youcan create database users with thecloudsqlsuperuser role, whichhas some superuser privileges, including:

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

• Performing theCREATE CAST andDROP CAST statements as a database user with thecloudsqlsuperuser role. However, this user must have theUSAGE privilege on both the source and target data types. For example, a user can create a cast that converts the sourceint data type to the targetboolean data type.

  Note:Cloud SQL doesn't support theWITHOUT FUNCTION cast method.
• Having full access to thepg_largeobject catalog table.

Default PostgreSQL users

When you create a new Cloud SQL for PostgreSQL instance, the defaultadmin userpostgres is created but not its password. You need toset a password for this user before you can log in. You can do this eitherin the Google Cloud console or by using the followinggcloud command:

gcloudsqlusersset-passwordpostgres\--instance=INSTANCE_NAME\--password=PASSWORD

Thepostgres user is part of thecloudsqlsuperuser role, andhas the following attributes (privileges):CREATEROLE,CREATEDB, andLOGIN. It does not have theSUPERUSER orREPLICATIONattributes.

A defaultcloudsqlimportexport user is created with the minimal set ofprivileges needed for CSV import/export operations. You cancreate your own users to perform these operations, but if you don't, the defaultcloudsqlimportexport user is used. Thecloudsqlimportexport user isa system user and customers cannot directly use it.

Cloud SQL IAM users for IAM authentication

Identity and Access Management (IAM) is integrated with Cloud SQL in a featurecalledIAM authentication.When you create instances using this feature, IAM users can signin to the instance using their IAM usernames andpasswords. The advantage to using IAM authentication is that youcan use a user's existing IAM credentials when granting themaccess to a database. When the user leaves the organization, theirIAM account is suspended, removing their access automatically.

Other PostgreSQL users

You cancreate other PostgreSQL users or roles. All users youcreate using Cloud SQL are created as part of thecloudsqlsuperuserrole, and have the same set of attributes as thepostgres user:CREATEROLE,CREATEDB, andLOGIN. You can change the attributes of any user by using theALTER ROLE command.

If you create a new user with thepsql client, you can chooseto associate it with a different role, or give it different attributes.

Access to thepg_shadow view and thepg_authid table

You can use thepg_shadow view towork with the properties of roles that are marked asrolcanlogin in thepg_authidcatalog table.

Thepg_shadow view contains hashed passwords and other propertiesof the roles (users) allowed to log in to a cluster. Thepg_authid catalog tablecontains hashed passwords and other properties for all database roles.

In Cloud SQL, customers can't access thepg_shadow view or thepg_authid tableusing the default privileges. However, access to role names and hashed passwordsis 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 flags for thepg_shadow view and thepg_authid table

To access thepg_shadow view, set thecloudsql.pg_shadow_select_roleflag to a PostgreSQL role name. To access thepg_authid table, set thecloudsql.pg_authid_select_role flag to a PostgreSQL role name.

If thecloudsql.pg_shadow_select_role exists, then it has read-only (SELECT)access to thepg_shadow view. If thecloudsql.pg_authid_select_role exists,then it hasSELECT access to thepg_authid table.

If either role doesn't exist, then the settings have no effect, but no error occurs.However, an error is logged when a user tries to access the view or the table.The error is logged in the PostgreSQL database log:cloudsql.googleapis.com/postgres.log.For information about viewing this log, seeView instance logs.

Ensure that the configured roles exist and that there isn't a typo in the valueof either thecloudsql.pg_shadow_select_role flag or thecloudsql.pg_authid_select_roleflag. You also can use thepg_has_role function to verify that a user is amember of these roles. Information about this function is available on theSystem Information Functions and Operatorspage.

You can use thecloudsql.pg_shadow_select_role flag or thecloudsql.pg_authid_select_role flag withPostgreSQL role membershipto managepg_shadow orpg_authid access for multiple users.

Changes to either flag don't require a database restart.

For more information about supported flags, seeConfigure database flags.

Choose a password storage format

Cloud SQL for PostgreSQL stores user passwords in a hashed format.You can use thepassword_encryption flag to set the encryption algorithmtomd5 orscram-sha-256. Themd5 algorithm providesthe broadest compatibility, whereasscram-sha-256 is more secure but might beincompatible with older clients.

When enablingpg_shadow access to export role properties from a Cloud SQLinstance, consider using the most secure algorithm supported by your clients.

In the PostgreSQL documentation, also see:

• Password Authentication
• Thepassword_encryption flag

What's next

• Create and manage users.
• Create and manage databases.
• See thePostgreSQL documentation about roles.
• Learn aboutoptions for connecting to your instance.