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
-
Sign in to your Google Account.
If you don't already have one, sign up for a new account.
-
In the Google Cloud Console, on the project selector page, select or create a Google Cloud project.
-
Make sure that billing is enabled for your Cloud project. Learn how to confirm that billing is enabled for your project.
- Install and initialize the Cloud SDK.
- Enable the Cloud Key Management Service API.
- Make sure you have the Cloud SQL Admin role on your user account.
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
- Go to the Cloud SQL Instances page in the Google Cloud Console.
- Click the instance to open its Overview page.
- Select Users from the Navigation menu.
- Click ADD USER ACCOUNT.
- 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.
- 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 accountsa-name@project-id.iam.gserviceaccount.com
should besa-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 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://www.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://www.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://www.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://www.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://www.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://www.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
- In the Cloud Console, go to the IAM page.
- Click Add.
- 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.
- In Select a role, navigate to Cloud SQL and select Cloud SQL Instance User.
- 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.
gcloud projects add-iam-policy-binding PROJECT_ID --member=user:EMAIL --role=roles/cloudsql.instanceUserFor a service account, run the following command:
gcloud projects add-iam-policy-binding PROJECT_ID --member=serviceAccount:EMAIL --role=roles/cloudsql.instanceUser
REST 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" ] }
Granting database privileges to the Cloud SQL user
Before a user or service account can connect to a database or run queries against it, they need to be granted privileges for that database. Privileges that you can grant include SELECT, INSERT, UPDATE, DELETE, CREATE, CONNECT, and others. 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 all on all tables in schema SCHEMA_NAME 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
- Learn more about IAM database authentication.
- Learn how to log in to a Cloud SQL database.
- Learn how to configure instances for IAM database authentication.