Creating and managing users that use IAM database authentication

This page describes how to create and manage a user or service account that uses IAM database authentication. For more information about the IAM integration, see Overview of IAM database authentication.

Before you begin

  1. Sign in to your Google Cloud account. If you're new to Google Cloud, create an account to evaluate how our products perform in real-world scenarios. New customers also get $300 in free credits to run, test, and deploy workloads.

  2. In the Google Cloud Console, on the project selector page, select or create a Google Cloud project.

    Go to project selector

  3. Make sure that billing is enabled for your Cloud project. Learn how to confirm that billing is enabled for your project.

  4. Install and initialize the Cloud SDK.
  5. Enable the Cloud Key Management Service API.

    Enable the API

  6. Make sure you have the Cloud SQL Admin role on your user account.

    Go to the IAM page

  7. Enable IAM database authentication on your Cloud SQL instance.

Creating a user or service account that uses IAM database authentication

You add the user to each instance that contains the databases that the user needs to access. Once added, the user can access all the instances and databases in the project.

To create a database user or service account that uses IAM database authentication:

Console

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

    Go to the Cloud SQL Instances page

  2. Click the instance to open its Overview page.
  3. Select Users from the Navigation menu.
  4. Click ADD USER ACCOUNT.
  5. Leave the Identity and Access Management button selected and add the email address in Member. The user account must already have the Cloud SQL Instance User role assigned. For more information, see Granting login access to a user or service account.
  6. Click ADD.

gcloud

User creation

Use the email, such as test-user@gmail.com, to identify the user.

Replace the following:

  • EMAIL: The email address for the user.
  • INSTANCE_NAME: The name of the instance you want to authorize the user to access.
gcloud sql users create EMAIL --instance=INSTANCE_NAME --type=cloud_iam_user

Service account creation

Replace the following:

  • EMAIL: The email address of the service account. Due to length limit on database username, you need to omit the .gserviceaccount.com suffix in the email. For example, the username for the service account sa-name@project-id.iam.gserviceaccount.com should be sa-name@project-id.iam.
  • INSTANCE_NAME: The name of the instance you want to authorize the service account to access.
gcloud sql users create EMAIL --instance=INSTANCE_NAME --type=cloud_iam_service_account

REST API v1beta4

User creation

Before using any of the request data below, make the following replacements:

  • project-id: Your project ID
  • location-id: The location ID
  • instance-id: The instance ID for the instance you are adding the user to
  • user-name: The email address for the user
  • operation-id: The ID for the operation

HTTP method and URL: 

POST https://sqladmin.googleapis.com/sql/v1beta4/projects/project-id/locations/location-id/instances/instance-id/users

Request JSON body: 

{
  "name": "user-name",
  "type": "CLOUD_IAM_USER"
  }

To send your request, expand one of these options:

You should receive a JSON response similar to the following:

{
  "kind": "sql#operation",
  "targetLink": "https://sqladmin.googleapis.com/sql/v1beta4/projects/project-id/locations/location-id/instances/instance-id",
  "status": "DONE",
  "user": "user@example.com",
  "insertTime": "2020-02-07T22:44:16.656Z",
  "startTime": "2020-02-07T22:44:16.686Z",
  "endTime": "2020-02-07T22:44:20.437Z",
  "operationType": "CREATE_USER",
  "name": "operation-id",
  "targetId": "instance-id",
  "selfLink": "https://sqladmin.googleapis.com/sql/v1beta4/projects/project-id/operations/operation-id",
  "targetProject": "project-id"
}

Service account creation

Before using any of the request data below, make the following replacements:

  • service-account-id: Your service account ID
  • project-id: Your project ID
  • instance-id: The instance ID for the instance you are adding the service account to
  • operation-id: The ID for the operation

HTTP method and URL: 

POST https://sqladmin.googleapis.com/sql/v1beta4/projects/project-id/instances/instance-id/users

Request JSON body: 

{
    "name": "service_account_id"
    "type": "CLOUD_IAM_SERVICE_ACCOUNT"
}

To send your request, expand one of these options:

You should receive a JSON response similar to the following:

{
"kind": "sql#operation",
  "targetLink": "https://sqladmin.googleapis.com/sql/v1beta4/projects/project-id/instances/instance-id",
  "status": "DONE",
  "user": "user@example.com",
  "insertTime": "2020-11-20T04:08:00.211Z",
  "startTime": "2020-11-20T04:08:00.240Z",
  "endTime": "2020-11-20T04:08:02.003Z",
  "operationType": "CREATE_USER",
  "name": "operation-id",
  "targetId": "instance-id",
  "selfLink": "https://sqladmin.googleapis.com/sql/v1beta4/projects/project-id/operations/operation-id",
  "targetProject": "project-id"
}

Granting login access to a user or service account

The database user name must be the IAM user’s email address, for example test-user@gmail.com. It must use quotes because it contains special characters (@ and .).

The service account email should be of the form ???@???.???.gserviceaccount.com.

To grant login access to a user or service account:

Console

  1. In the Google Cloud Console, go to the IAM page.

    Go to the IAM page

  2. Click Add.
  3. In New Members, enter an email address. You can add individuals, or service accounts as members, but every project must have at least one individual as a member.
  4. In Select a role, navigate to Cloud SQL and select Cloud SQL Instance User.
  5. Click Save.

gcloud

Run gcloud projects add-iam-policy-binding with the --role=roles/cloudsql.instanceUser flag.

Replace the following:

  • PROJECT_ID: The ID for the project you want to authorize the user to use.
  • EMAIL: The email address for the user.
For a user account, run the following command: 
gcloud projects add-iam-policy-binding PROJECT_ID \
    --member=user:EMAIL --role=roles/cloudsql.instanceUser
For a service account, run the following command: 
gcloud projects add-iam-policy-binding PROJECT_ID \
    --member=serviceAccount:EMAIL --role=roles/cloudsql.instanceUser

REST API v1beta4

Grant a role by editing the JSON or YAML policy returned by the get-iam-policy command. Note that this policy change will not take effect until you set the updated policy.

{
      "role": "roles/cloudsql.instanceUser",
      "members": [
                   "user:user@example.com"
                 ]
    }

Revoking access to a user or service account

To revoke access to a user or service account, you delete the account from the instance:

Console

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

    Go to the Cloud SQL Instances page

  2. Click the instance to open its Overview page.
  3. Select Users from the Navigation menu.
  4. Click for the user you want to remove.
  5. Select Remove. This revokes access to this instance only.

gcloud

Revoke a user

Use the email, such as test-user@gmail.com, to identify the user.

Replace the following:

  • EMAIL: The email address for the user.
  • INSTANCE_NAME: The name of the instance you want to remove the user from.
gcloud sql users delete EMAIL --instance=INSTANCE_NAME

Service account creation

Replace the following:

  • EMAIL: The email address of the service account. Due to length limit on database username, you need to omit the .gserviceaccount.com suffix in the email. For example, the username for the service account sa-name@project-id.iam.gserviceaccount.com should be sa-name@project-id.iam.
  • INSTANCE_NAME: The name of the instance you want to remove the user from.
gcloud sql users delete EMAIL --instance=INSTANCE_NAME

REST API v1beta4

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

Before using any of the request data below, make the following replacements:

  • project-id: Your project ID
  • instance-id: The desired instance ID
  • user-id: The ID of the user

HTTP method and URL: 

DELETE https://sqladmin.googleapis.com/sql/v1beta4/projects/project-id/instances/instance-id/users?host=&name=user-id

Request JSON body: 

{
  "name": "user-id",
  "host": ""
}

To send your request, expand one of these options:

You should receive a JSON response similar to the following:

{
  "kind": "sql#operation",
  "targetLink": "https://sqladmin.googleapis.com/sql/v1beta4/projects/project-id/instances/instance-id",
  "status": "DONE",
  "user": "user@example.com",
  "insertTime": "2020-02-07T22:38:41.217Z",
  "startTime": "2020-02-07T22:38:41.217Z",
  "endTime": "2020-02-07T22:38:44.801Z",
  "operationType": "DELETE_USER",
  "name": "operation-id",
  "targetId": "instance-id",
  "selfLink": "https://sqladmin.googleapis.com/sql/v1beta4/projects/project-id/operations/operation-id",
  "targetProject": "project-id"
}

Granting database privileges to the Cloud SQL user

When a user or service account connects to a database, they can run queries against any database objects whose access has been granted to PUBLIC. If they need additional access, more privileges can be granted using the GRANT statement. See the GRANT reference page for a complete list of privileges you can grant to users and service accounts. Run GRANT from the postgres command line, for example:

Replace the following:

  • EMAIL: The email address for the user. You must use quotes around the email because it contains special characters (@ and .)
  • SCHEMA_NAME: The tables' schema name.
postgres=> grant select on finance.orders to "EMAIL";

Viewing login information in audit logs

You can enable audit logs to capture IAM logins to the database. When there are login issues, you can use the audit logs to diagnose the problem.

Note: Audit Logging incurs extra costs. For more information, see Pricing for logging data.

Once configured, you can view Data Access audit logs of successful logins using the Logs Viewer.

For example, a log might have information similar to the following:

{
 insertId: "..."
 logName: "projects/.../logs/cloudaudit.googleapis.com%2Fdata_access"
 protoPayload: {
  @type: "type.googleapis.com/google.cloud.audit.AuditLog"
  authenticationInfo: {
   principalEmail: "..."
  }
  authorizationInfo: [
   0: {
    granted: true
    permission: "cloudsql.instances.login"
    resource: "instances/..."
    resourceAttributes: {
    }
   }
  ]
  methodName: "cloudsql.instances.login"
  request: {
   @type: "type.googleapis.com/google.cloud.sql.authorization.v1.InstancesLoginRequest"
   clientIpAddress: "..."
   database: "..."
   databaseSessionId: ...
   instance: "projects/.../locations/us-central1/instances/..."
   user: "..."
  }
  requestMetadata: {
   callerIp: "..."
   destinationAttributes: {
   }
   requestAttributes: {
    auth: {
    }
    time: "..."
   }
  }
  resourceName: "instances/..."
  serviceName: "cloudsql.googleapis.com"
  status: {
  }
 }
 receiveTimestamp: "..."
 resource: {
  labels: {
   database_id: "...:..."
   project_id: "..."
   region: "us-central"
  }
  type: "cloudsql_database"
 }
 severity: "INFO"
 timestamp: "..."
}

Troubleshooting a login failure

When an attempt to log in fails, PostgreSQL returns a minimal error message for security reasons. For example:

PGPASSWORD=not-a-password psql --host=... --username=... --dbname=...
psql: error: could not connect to server: FATAL:  Cloud SQL IAM user authentication failed for user "..."
FATAL:  pg_hba.conf rejects connection for host "...", user "...", database "...", SSL off

You can review the PostgreSQL error logs for more details about the error. For more information, see Viewing Logs.

For example, for the previous error, the following log entry explains the action you can take to resolve the problem.

F ... [152172]: [1-1] db=...,user=... FATAL:  Cloud SQL IAM user authentication failed for user "..."
I ... [152172]: [2-1] db=...,user=... DETAIL:  Request is missing required authentication credential. Expected OAuth 2 access token, log in cookie or other valid authentication credential. See https://developers.google.com/identity/sign-in/web/devconsole-project.

Check the error message you receive. If the message does not indicate that you used "Cloud SQL IAM user authentication" or "Cloud SQL IAM service account authentication," verify that the database user type used to log in is either CLOUD_IAM_USER or CLOUD_IAM_SERVICE_ACCOUNT. You can use the Console or the gcloud sql users list command to check this. For an IAM user, verify that the database username is the IAM user's email. For a service account, verify that it is the service account's email without the .gserviceaccount.com domain suffix.

If you used IAM database authentication, check the details of the error message. You can find the error message in the database error log. If it indicates the access token (OAuth 2.0) you sent as a password was invalid, you can use the gcloud auth application-default print-access-token gcloud command to find details of the token, as follows:

curl -H "Content-Type: application/x-www-form-urlencoded" \
     -d "access_token=$(gcloud auth application-default print-access-token)" \
     https://www.googleapis.com/oauth2/v1/tokeninfo

Verify that the token is for the intended IAM user or service account and has not expired.

If the details indicates lack of permission, verify the IAM user or service account is granted the cloudsql.instances.login permission using the predefined Cloud SQL Instance User role or custom role in the IAM policy of the instance's project. Use the IAM Policy Troubleshooter for additional help.

If a login fails due to IAM database authentication unavailability, the user can log in using the default PostgreSQL user and password. This method of logging in still gives the user access to the entire database.

What's next