Creating and Managing PostgreSQL Users

This page describes how to configure the default user account and create, delete, and update other user accounts for Cloud SQL instances.

For information about how users work with Cloud SQL, see Users.

Before you begin

Before completing the tasks on this page, you must have created the Cloud SQL instance. For more information, see Creating instances.

If you plan to use your database's administrative client to manage your users, you must have configured:

Configuring the default user account

When you create a new Cloud SQL instance, you must configure the default user account before you can connect to the instance.

For Cloud SQL for PostgreSQL, the default user is postgres.

To configure the default user:

Console

  1. Go to the Cloud SQL Instances page in the Google Cloud Platform Console.

    Go to the Cloud SQL Instances page

  2. Click the instance to open its Overview page.
  3. Select the Users tab.
  4. Find the postgres user and select Change password from the more actions menu More actions icon..
  5. Provide a strong password that you can remember and click Ok.

gcloud

Set the password for the default user:

gcloud sql users set-password postgres no-host \
   --instance [INSTANCE_NAME] --password [PASSWORD]

cURL

The following request uses the users:update method to update the postgres user.

ACCESS_TOKEN="$(gcloud auth application-default print-access-token)"
curl --header "Authorization: Bearer ${ACCESS_TOKEN}" \
     --header 'Content-Type: application/json' \
     --data '{"name": "postgres", "password": "[PASSWORD]"}' \
     -X PUT \
     'https://www.googleapis.com/sql/v1beta4/projects/[PROJECT-ID]/instances/[INSTANCE_NAME]/users?name=postgres&host='

Creating a user

To create a user:

Console

  1. Go to the Cloud SQL Instances page in the Google Cloud Platform Console.

    Go to the Cloud SQL Instances page

  2. Select the instance to open its Overview page.
  3. Select the Users tab.
  4. Click Create user account.
  5. In the Create user account dialog, specify:
    • A User name.
    • A Password.
  6. Click Create.

Users created using Cloud SQL have the privileges associated with the cloudsqlsuperuser role.

gcloud

Create the user:

gcloud sql users create [USER_NAME] [HOST] \
   --instance=[INSTANCE_NAME] --password=[PASSWORD]

Users created using Cloud SQL have the privileges associated with the cloudsqlsuperuser role. The host parameter is ignored; provide any non-empty string.

cURL

The following request uses the users:insert method to create a user account 'user_name'.

ACCESS_TOKEN="$(gcloud auth application-default print-access-token)"
curl --header "Authorization: Bearer ${ACCESS_TOKEN}" \
     --header 'Content-Type: application/json' \
     --data '{"host": "", "name": "[USER_NAME]", "password": "[PASSWORD]"}' \
     -X POST \
     https://www.googleapis.com/sql/v1beta4/projects/[PROJECT-ID]/instances/[INSTANCE_NAME]/users

Users created using Cloud SQL have the privileges associated with the cloudsqlsuperuser role.

psql Client

  1. At the psql prompt, create the user:
    CREATE USER [USER_NAME] WITH [ATTRIBUTE1] [ATTRIBUTE2]...;
    \password [USER_NAME];
    

    Enter the password when prompted.

    For more information about role attributes, see the PostgreSQL documentation.

  2. You can confirm the user creation by displaying the user table:
    SELECT * FROM pg_roles;
    

Changing a user password

To change a user password:

Console

  1. Go to the Cloud SQL Instances page in the Google Cloud Platform Console.

    Go to the Cloud SQL Instances page

  2. Select the instance to open its Overview page.
  3. Select Access Control > Users.
  4. Click more actions More actions icon. for the user you want to update.
  5. Select Change password, specify a new password, and click OK.

gcloud

Update the password:

gcloud sql users set-password [USER_NAME] [HOST] \
   --instance=[INSTANCE_NAME] --password=[PASSWORD]

The host parameter is ignored; provide any value.

cURL

The following request uses the users:update method to update the password for the user account 'user_name'.

ACCESS_TOKEN="$(gcloud auth application-default print-access-token)"
curl --header "Authorization: Bearer ${ACCESS_TOKEN}" \
     --header 'Content-Type: application/json' \
     --data '{"name": "[USER_NAME]", "host": "", "password": "[PASSWORD]"}' \
     -X PUT \
     'https://www.googleapis.com/sql/v1beta4/projects/[PROJECT-ID]/instances/[INSTANCE_NAME]/users?name=[USER_NAME]&host='

psql Client

  1. At the psql prompt, change the password:
    \password [USER];
    

    Enter the password when prompted.

Listing users

To list users:

Console

  1. Go to the Cloud SQL Instances page in the Google Cloud Platform Console.

    Go to the Cloud SQL Instances page

  2. Select the instance to open its Overview page.
  3. Select Access Control > Users.

gcloud

List the users for this instance:

gcloud sql users list --instance=[INSTANCE_NAME]

For a complete list of parameters for this command, see the gcloud sql users list reference page.

cURL

The following request uses users:list method to list the users defined for an instance.

ACCESS_TOKEN="$(gcloud auth application-default print-access-token)"
curl --header "Authorization: Bearer ${ACCESS_TOKEN}" \
     -X GET \
     https://www.googleapis.com/sql/v1beta4/projects/[PROJECT-ID]/instances/[INSTANCE_NAME]/users

psql Client

At the psql prompt, list the PostgreSQL users:

SELECT * FROM pg_roles;

Deleting users

To delete a user:

Console

  1. Go to the Cloud SQL Instances page in the Google Cloud Platform Console.

    Go to the Cloud SQL Instances page

  2. Select the instance to open its Overview page.
  3. Select Access Control > Users.
  4. Click more actions More actions icon. for the user you want to delete.
  5. Select Delete and click OK.

gcloud

Delete the user:

gcloud sql users delete [USER_NAME] [HOST] --instance=[INSTANCE_NAME]

The host parameter is ignored; provide any value.

cURL

The following request uses the users:delete method to delete the specified user account.

ACCESS_TOKEN="$(gcloud auth application-default print-access-token)"
curl --header "Authorization: Bearer ${ACCESS_TOKEN}" \
     --header 'Content-Type: application/json' \
     --data '{"name": "[USER_NAME]", "host": ""}' \
     -X DELETE \
     'https://www.googleapis.com/sql/v1beta4/projects/[PROJECT-ID]/instances/[INSTANCE_NAME]/users?host=&name=[USER_NAME]'

psql Client

  1. At the psql prompt, delete the user:
    DROP ROLE [USER_NAME];
    

    For more information about the DROP ROLE statement, see the PostgreSQL documentation.

What's next

Send feedback about...

Cloud SQL for PostgreSQL