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 the PostgreSQL documentation. For information about creating and managing Cloud SQL users, see Creating and Managing Users.
PostgreSQL roles and users
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 login (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, it does not necessarily
have the LOGIN
attribute.
All PostgreSQL users must have a password. You cannot login with a user that does not have a password.
Superuser restrictions
Because Cloud SQL for PostgreSQL is a managed service, 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.
Although you can't create database users with superuser privileges, you can
create database users with the cloudsqlsuperuser
role which has
some of those privileges, including:
- Creating extensions that require superuser privileges
- Creating event triggers
- Creating replication users
- Creating replication publications and subscriptions
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 have the option to
create your own users to perform these operations but if you don't, the default
cloudsqlimportexport
user will be 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 the Overview of 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.
What's next
- Configure the postgres user for your instance.
- Create and manage users.
- Create and manage databases.
- See the PostgreSQL documentation about roles.
- Learn about options for connecting to your instance.