Logging in to a database with IAM database authentication

MySQL | PostgreSQL | SQL Server

This page describes how users and service accounts can log in to Cloud SQL databases using Cloud SQL IAM database authentication. To learn more about the Cloud SQL IAM integration, see Overview of Cloud SQL IAM database authentication.

Before you begin

Configure an instance to use IAM database authentication. For more information, see Configuring IAM database authentication.
Create and authorize an IAM database authentication user. For more information, see Creating a user that uses IAM database authentication and Granting login access to a user or service account.

Using IAM database authentication with the Cloud SQL Auth proxy

For the most secure and reliable experience, it is recommended that you connect to the database using the Cloud SQL Auth proxy when using IAM database authentication. IAM database authentication uses OAuth 2.0 access tokens, which are short-lived and only valid for one hour. The Cloud SQL Auth proxy is able to request and refresh these tokens, ensuring that long-lived process or applications that rely on connection pooling can have stable connections.

In this procedure, you configure the Cloud SQL Auth proxy to connect to an instance using IAM database authentication.

When using the Cloud SQL Auth proxy with IAM database authentication, the GCP IAM account that you use to start the Cloud SQL Auth proxy must be the same account that authenticates to the database. For more information about authenticating the Cloud SQL Auth proxy, see Options for authenticating the Cloud SQL Auth proxy.
Start the Cloud SQL Auth proxy with the -enable_iam_login flag.

Note: You need to have the `roles/cloudsql.instanceUser` IAM role on your user account to perform this task. It is a predefined role that contains the necessary Cloud SQL IAM permission `cloudsql.instances.login`. You need this permission to log in to a database instance.

Replace the following:
- INSTANCE_CONNECTION_NAME: The connection string to identify a Cloud SQL instance. For more information on how to find this string, see Options for authenticating the Cloud SQL Auth proxy.
```
./cloud-sql-proxy -enable_iam_login -instances=INSTANCE_CONNECTION_NAME=tcp:5432
```
Proxy For more information on how to start the proxy, see Start the Cloud SQL Cloud SQL Auth proxy.

Warning: If you run the Cloud SQL Auth proxy as a service, keep in mind that it requests the access tokens on behalf of your applications. For this reason, ensure that only trusted users are able to access the address and port or the Unix socket that the Cloud SQL Auth proxy is listening on.
When you are ready to connect to the Cloud SQL Auth proxy, you use the email address for the IAM user or service account as the database username. For a service account, this is the service account's email without the .gserviceaccount.com domain suffix.For the password, use either no password, or a blank password.

For more information on how to connect to the Cloud SQL Auth proxy, see Connecting using the Cloud SQL Auth proxy.

Note: When the Cloud SQL Auth proxy is started using TCP sockets and with the -enable_iam_login flag, then a PostgreSQL client hangs during TCP connection. One workaround is to use sslmode=disable in the PostgreSQL connection string. For example:

psql "host=127.0.0.1 dbname=postgres user=me@google.com sslmode=disable"

Another workaround is to start the Cloud SQL Auth proxy using Unix sockets. This turns off PostgreSQL SSL encryption and lets the Cloud SQL Auth proxy do the SSL encryption instead.

Logging in with IAM database authentication without the Cloud SQL Auth proxy

Using the Cloud SDK, you can explicitly request an OAuth 2.0 token with the Cloud SQL Admin API scope that is used to log in to the database through the psql client. When you log in as a database user with IAM database authentication, you use your email address as the username and the access token as the password.

To use the Cloud SDK to generate this token and log in, use the following script:

Authenticate and create the token.

User
Authenticate to IAM using gcloud auth login..
For more information, see Authorizing with a user account.

Service account
Authenticate to IAM using gcloud auth activate-service-account..
For more information, see Authorizing with a service account.
Log in with a client using the saved access token.

Warning: You can use your OAuth 2.0 token to make authenticated requests on your behalf. Make sure to keep it secure, and be careful where you store it or who has access to your instance.

Replace the following:
- HOSTNAME: The IP address of the instance, or 127.0.0.1 if using the Cloud SQL proxy.
- EMAIL: The user email address to use to connect to the host machine. For a service account, this is the service account's email without the .gserviceaccount.com domain suffix.
- DATABASE_NAME: The name of the database to connect to.
```
PGPASSWORD=$(gcloud auth print-access-token) psql --host=HOSTNAME \
                                                --username=EMAIL \
                                                --dbname=DATABASE_NAME
```
Note: The OAuth 2.0 token can't be entered or pasted directly into a password field, because that token is longer than the field's buffer size (100 characters). Always use the PGPASSWORD environment variable.

What's next

Learn more about IAM database authentication.
Learn how to enable and view login information in audit logs.
Learn how to create users and service accounts that use Cloud SQL IAM database authentication.
Learn how to grant login access to a user or service account.
Learn how to manage users and service accounts for IAM database authentication.