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 and privileges
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 can't 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.
Performing the
CREATE 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.- Having 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 and the pg_authid
table
You can use the pg_shadow
view to
work with the properties of roles that are marked as rolcanlogin
in the
pg_authid
catalog table.
The pg_shadow
view contains hashed passwords and other properties
of the roles (users) allowed to log in to a cluster. The pg_authid
catalog table
contains hashed passwords and other properties for all database roles.
In Cloud SQL, customers can't access the pg_shadow
view or the pg_authid
table
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 flags for the pg_shadow
view and the pg_authid
table
To access the pg_shadow
view, set the cloudsql.pg_shadow_select_role
flag to a PostgreSQL role name. To access the pg_authid
table, set the
cloudsql.pg_authid_select_role
flag to a PostgreSQL role name.
If the cloudsql.pg_shadow_select_role
exists, then it has read-only (SELECT
)
access to the pg_shadow
view. If the cloudsql.pg_authid_select_role
exists,
then it has SELECT
access to the pg_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, see View instance logs.
Ensure that the configured roles exist and that there isn't a typo in the value
of either the cloudsql.pg_shadow_select_role
flag or the cloudsql.pg_authid_select_role
flag. You also can use the pg_has_role
function to verify that a user is a
member of these roles. Information about this function is available on the
System Information Functions and Operators
page.
You can use the cloudsql.pg_shadow_select_role
flag or the
cloudsql.pg_authid_select_role
flag with
PostgreSQL role membership
to manage pg_shadow
or pg_authid
access for multiple users.
Changes to either flag don't 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
- Create and manage users.
- Create and manage databases.
- See the PostgreSQL documentation about roles.
- Learn about options for connecting to your instance.