Log in using IAM database authentication

This page describes how users and service accounts can log in to Cloud SQL databases using Cloud SQL IAM database authentication. For more information, see IAM authentication.

Before you begin

Log in with automatic IAM database authentication

You can configure a Cloud SQL connector to automatically handle authentication to the Cloud SQL instance on behalf of a user or an application. Connectors include the Cloud SQL Auth Proxy, the Go connector, the Java connector, and the Python connector, all of which support automatic IAM database authentication. When using a Cloud SQL connector with automatic IAM database authentication, the IAM account that you use to start the connector must be the same account that authenticates to the database.

To log in using automatic IAM database authentication:

Cloud SQL Auth Proxy

  1. Authenticate to Google Cloud.

    User

    Authenticate to Google Cloud using Application Default Credentials (ADC).

    Use the gcloud auth application-default login command. For more information, see Set up Application Default Credentials.

    Service account

    To authenticate to Google Cloud using ADC with a service account, you can use service account impersonation or use a service account key. To use service account impersonation, replace SERVICE_ACCOUNT_EMAIL_ADDRESS, and run the following command:

    gcloud auth application-default login --impersonate-service-account SERVICE_ACCOUNT_EMAIL_ADDRESS

    For more information, see Set up Application Default Credentials.

  2. Start the Cloud SQL Auth Proxy with the --auto-iam-authn flag.

    Replace the following:

    • INSTANCE_CONNECTION_NAME: The connection string to identify a Cloud SQL instance. If you use a port other than the default PostgreSQL port, then specify the port number. For more information on how to find and construct this string, see Options for authenticating the Cloud SQL Auth Proxy.
    ./cloud-sql-proxy --auto-iam-authn INSTANCE_CONNECTION_NAME

    For more information on how to start the proxy, see Start the Cloud SQL Auth Proxy.

  3. When you are ready to connect to the instance by using the Cloud SQL Auth Proxy, log in with the psql client.

    Replace the following:

    • HOSTNAME: IP address used by the Cloud SQL Auth Proxy. By default, the Cloud SQL Auth Proxy uses the localhost address of 127.0.0.1, but you can assign a different IP address when you start the Cloud SQL Auth Proxy.
    • USERNAME: For an IAM, the username is the full email address of the user. For a service account, this is the service account's email without the .gserviceaccount.com domain suffix.
    • PORT_NUMBER: Optional. If you specified a different port in the instance connection string, then specify that port number.
    • DATABASE_NAME: The name of the database to connect to.

    Run the following command:

    psql -h HOSTNAME \
 -U USERNAME \
 --port PORT_NUMBER \
 --dbname=DATABASE_NAME

    For more information on how to connect to the Cloud SQL Auth Proxy, see Connect with the psql client.

Go 

import (
	"context"
	"database/sql"
	"fmt"
	"log"
	"net"
	"os"

	"cloud.google.com/go/cloudsqlconn"
	"github.com/jackc/pgx/v5"
	"github.com/jackc/pgx/v5/stdlib"
)

func connectWithConnectorIAMAuthN() (*sql.DB, error) {
	mustGetenv := func(k string) string {
		v := os.Getenv(k)
		if v == "" {
			log.Fatalf("Warning: %s environment variable not set.", k)
		}
		return v
	}
	// Note: Saving credentials in environment variables is convenient, but not
	// secure - consider a more secure solution such as
	// Cloud Secret Manager (https://cloud.google.com/secret-manager) to help
	// keep secrets safe.
	var (
		dbUser                 = mustGetenv("DB_IAM_USER")              // e.g. 'service-account-name@project-id.iam'
		dbName                 = mustGetenv("DB_NAME")                  // e.g. 'my-database'
		instanceConnectionName = mustGetenv("INSTANCE_CONNECTION_NAME") // e.g. 'project:region:instance'
		usePrivate             = os.Getenv("PRIVATE_IP")
	)

	// WithLazyRefresh() Option is used to perform refresh
	// when needed, rather than on a scheduled interval.
	// This is recommended for serverless environments to
	// avoid background refreshes from throttling CPU.
	d, err := cloudsqlconn.NewDialer(
		context.Background(),
		cloudsqlconn.WithIAMAuthN(),
		cloudsqlconn.WithLazyRefresh(),
	)
	if err != nil {
		return nil, fmt.Errorf("cloudsqlconn.NewDialer: %w", err)
	}
	var opts []cloudsqlconn.DialOption
	if usePrivate != "" {
		opts = append(opts, cloudsqlconn.WithPrivateIP())
	}

	dsn := fmt.Sprintf("user=%s database=%s", dbUser, dbName)
	config, err := pgx.ParseConfig(dsn)
	if err != nil {
		return nil, err
	}

	config.DialFunc = func(ctx context.Context, network, instance string) (net.Conn, error) {
		return d.Dial(ctx, instanceConnectionName, opts...)
	}
	dbURI := stdlib.RegisterConnConfig(config)
	dbPool, err := sql.Open("pgx", dbURI)
	if err != nil {
		return nil, fmt.Errorf("sql.Open: %w", err)
	}
	return dbPool, nil
}

Java JDBC 

import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;
import javax.sql.DataSource;

public class ConnectorIamAuthnConnectionPoolFactory extends ConnectionPoolFactory {

  // Note: Saving credentials in environment variables is convenient, but not
  // secure - consider a more secure solution such as
  // Cloud Secret Manager (https://cloud.google.com/secret-manager) to help
  // keep secrets safe.
  private static final String INSTANCE_CONNECTION_NAME =
      System.getenv("INSTANCE_CONNECTION_NAME");
  private static final String DB_IAM_USER = System.getenv("DB_IAM_USER");
  private static final String DB_NAME = System.getenv("DB_NAME");

  public static DataSource createConnectionPool() {
    // The configuration object specifies behaviors for the connection pool.
    HikariConfig config = new HikariConfig();

    // The following URL is equivalent to setting the config options below:
    // jdbc:postgresql:///<DB_NAME>?cloudSqlInstance=<INSTANCE_CONNECTION_NAME>&
    // socketFactory=com.google.cloud.sql.postgres.SocketFactory&user=<DB_IAM_USER>&
    // password=password
    // See the link below for more info on building a JDBC URL for the Cloud SQL JDBC Socket Factory
    // https://github.com/GoogleCloudPlatform/cloud-sql-jdbc-socket-factory#creating-the-jdbc-url

    // Configure which instance and what database to connect with.
    config.setJdbcUrl(String.format("jdbc:postgresql:///%s", DB_NAME));

    config.addDataSourceProperty("socketFactory", "com.google.cloud.sql.postgres.SocketFactory");
    config.addDataSourceProperty("cloudSqlInstance", INSTANCE_CONNECTION_NAME);

    // If connecting using automatic database authentication, follow the instructions for
    // connecting using the connector, but set the DB_IAM_USER value to an IAM user or
    // service account that has been given access to the database.
    // See https://cloud.google.com/sql/docs/postgres/iam-logins for more details.
    config.addDataSourceProperty("enableIamAuth", "true");
    config.addDataSourceProperty("user", DB_IAM_USER);
    // Password must be set to a nonempty value to bypass driver validation errors.
    config.addDataSourceProperty("password", "password");
    // Explicitly set sslmode to disable to prevent driver from hanging.
    // The Java Connector will handle SSL so it is unneccesary to enable it at the driver level.
    config.addDataSourceProperty("sslmode", "disable");

    // cloudSqlRefreshStrategy set to "lazy" is used to perform a
    // refresh when needed, rather than on a scheduled interval.
    // This is recommended for serverless environments to
    // avoid background refreshes from throttling CPU.
    config.addDataSourceProperty("cloudSqlRefreshStrategy", "lazy");

    // ... Specify additional connection properties here.
    // ...

    // Initialize the connection pool using the configuration object.
    return new HikariDataSource(config);
  }
}

Java R2DBC 

private static final String CONNECTION_NAME = System.getenv("POSTGRES_CONNECTION_NAME");
private static final String DB_NAME = System.getenv("POSTGRES_DB");
private static final String DB_USER = System.getenv("POSTGRES_IAM_USER");
private static final String IP_TYPE =
    System.getenv("IP_TYPE") == null ? "PUBLIC" : System.getenv("IP_TYPE");
  // Set up ConnectionFactoryOptions
  ConnectionFactoryOptions options =
      ConnectionFactoryOptions.builder()
          .option(DRIVER, "gcp")
          .option(PROTOCOL, "postgresql")
          // Password must be set to a nonempty value to bypass driver validation errors
          .option(PASSWORD, "password")
          .option(USER, DB_USER)
          .option(DATABASE, DB_NAME)
          .option(HOST, CONNECTION_NAME)
          .option(IP_TYPES, IP_TYPE)
          .option(ENABLE_IAM_AUTH, true)
          .build();

  // Initialize connection pool
  ConnectionFactory connectionFactory = ConnectionFactories.get(options);
  ConnectionPoolConfiguration configuration =
      ConnectionPoolConfiguration.builder(connectionFactory).build();

  this.connectionPool = new ConnectionPool(configuration);

Python 

import os

from google.cloud.sql.connector import Connector, IPTypes
import pg8000

import sqlalchemy


def connect_with_connector_auto_iam_authn() -> sqlalchemy.engine.base.Engine:
    """
    Initializes a connection pool for a Cloud SQL instance of Postgres.

    Uses the Cloud SQL Python Connector with Automatic IAM Database Authentication.
    """
    # Note: Saving credentials in environment variables is convenient, but not
    # secure - consider a more secure solution such as
    # Cloud Secret Manager (https://cloud.google.com/secret-manager) to help
    # keep secrets safe.
    instance_connection_name = os.environ[
        "INSTANCE_CONNECTION_NAME"
    ]  # e.g. 'project:region:instance'
    db_iam_user = os.environ["DB_IAM_USER"]  # e.g. 'sa-name@project-id.iam'
    db_name = os.environ["DB_NAME"]  # e.g. 'my-database'

    ip_type = IPTypes.PRIVATE if os.environ.get("PRIVATE_IP") else IPTypes.PUBLIC

    # initialize Cloud SQL Python Connector object
    connector = Connector(refresh_strategy="LAZY")

    def getconn() -> pg8000.dbapi.Connection:
        conn: pg8000.dbapi.Connection = connector.connect(
            instance_connection_name,
            "pg8000",
            user=db_iam_user,
            db=db_name,
            enable_iam_auth=True,
            ip_type=ip_type,
        )
        return conn

    # The Cloud SQL Python Connector can be used with SQLAlchemy
    # using the 'creator' argument to 'create_engine'
    pool = sqlalchemy.create_engine(
        "postgresql+pg8000://",
        creator=getconn,
        # ...
    )
    return pool

Log in with manual IAM database authentication

A user or an application can authenticate to the database using IAM by manually requesting an access token from Google Cloud and presenting it to the database. Using the gcloud CLI, 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. When you log in as a database user with manual IAM database authentication, you use your email address as the username and the access token as the password. You can use this method with either a direct connection to the database or with a Cloud SQL connector.

In this procedure, you authenticate to Google Cloud, request an access token, and then connect to the database by passing in the token as the password for the IAM database user. Use these steps to connect without the Cloud SQL Auth Proxy.

For these steps, you must:

  • If you are connecting to an instance with public IP, authorize external access to the instance. For more information, see Authorize your machine's IP address for public IP.
  • If you are connecting to an instance with private IP, run the command within the Virtual Private Cloud (VPC) network.
  • Use the gcloud sql generate-login-token command to generate your authentication token.

    • To log in using manual IAM database authentication:

    gcloud

    1. Authenticate to Google Cloud.

      User

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

      Service account

      Authenticate to IAM using gcloud auth activate-service-account. For more information, see Authorize with a service account.

    2. Request the access token, and log in with a client.

      Replace the following:

      • HOSTNAME: The IP address of the instance, either the public IP address or private IP address.
      • USERNAME: For an IAM, the username is the full email address of the user. 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 sql generate-login-token` \
 psql "sslmode=require \
 hostaddr=HOSTNAME \
 user=USERNAME \
 dbname=DATABASE_NAME" \
 --no-password

      If ssl_mode on your Cloud SQL instance is configured to TRUSTED_CLIENT_CERTIFICATE_REQUIRED, then we recommend that you log in with automatic IAM database authentication to enforce client identity validation.

    What's next