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 and export operations. You cancreate your own users to perform these operations, but if you don't, then the defaultcloudsqlimportexport user is used. Thecloudsqlimportexport user isa system user, and you can't use it directly.

Cloud SQL system users and roles

Cloud SQL uses system users and roles to supportCloud SQL features. You can't delete or modifyCloud SQL system roles or users.You can't assign system roles except thecloudsqlsuperuserrole to database users. You can't assign database roles tosystem users.

• System roles

  • cloudsqliamgroup

    Used to designate a non-login IAM group authentication account that'sused for IAM group authentication.

  • cloudsqlinactiveuser

    Used to designate an IAM group authentication account as inactive.

  • cloudsqliamgroupserviceaccount

    Used to designate a IAM service account thatauthenticates using IAM group authentication.

  • cloudsqliamgroupuser

    Used to designate an IAM user who authenticatesusing IAM group authentication.

  • cloudsqliamserviceaccount

    Used to designate an IAM service accountthat authenticates using IAM database authentication.

  • cloudsqliamuser

    Used to designate an IAM user who authenticatesusing IAM database authentication.

  • cloudsqlsuperuser

    Role granted to users with limited superuser privileges.Thecloudsqlsuperuser role isgranted automatically to new PostgreSQL users who use built-in authentication.

• System users

  • cloudsqladmin

    System user with superuser privileges on the database.

  • cloudsqlagent

    Used for monitoring databases.

  • cloudsqlconnpooladmin

    Used forManaged Connection Pooling.

  • cloudsqlimportexport

    Used for data import and export.

  • cloudsqllogical

    Used for building logical replication.

  • cloudsqlobservability

    Used for database observability such astheindex advisor andactive queries.

  • cloudsqlreplica

    Used for replication.

Cloud SQL IAM users for IAM authentication

Identity and Access Management (IAM) is integrated with Cloud SQL in a featurecalledIAM database 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.Users createdusing Cloud SQL thataren't created usingIAMare created as part of thecloudsqlsuperuser role, and have the same set ofattributes as thepostgres user:CREATEROLE,CREATEDB, andLOGIN. Youcan change the attributes of any user using theALTERROLE command.

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

PostgreSQL roles

You can create custom roles in PostgreSQLto help you organize and assign database privileges for your PostgreSQL users.You can use roles to provide initial database privilegesfor users when you create a Cloud SQL instance.

For more information about creating and using roles in PostgreSQL,seeDatabase roles.

When you create a built-in PostgreSQL user in Cloud SQL for PostgreSQLand don't assign any database roles,the user is granted thecloudsqlsuperuser role automatically.Alternatively, you can create a new PostgreSQL user and assign a differentcustom role or roles with more fine-grained privileges.For more information about assigning roles to users in Cloud SQL for PostgreSQL,seeManage users with built-in authentication.

IAM users and database roles

When you create an IAM user accountin Cloud SQL for PostgreSQL and don't assign anydatabase roles, the user isn't granted anydatabase roles automatically.

You can grant thecloudsqlsuperuser role and custom database rolesto IAM users, service accounts or groups by assigning database roleswhen you create or update the IAM accounts on the instance.

For more information aboutgranting roles to IAM users, seeAssign database roles while adding an IAM account to an instance.

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.