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 Cloud SQL IAM database 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 the database. For more information, see Options for authenticating the Cloud SQL Auth proxy.

To log in using automatic IAM database authentication:

Cloud SQL Auth proxy

  1. Start the Cloud SQL Auth proxy with the --auto-iam-authn flag.

    Replace the following:

    ./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.

  2. When you are ready for the client to connect to the Cloud SQL Auth proxy, 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 more information on how to connect to the Cloud SQL Auth proxy, see Connect with the psql client.

Go

cloudsql/mysql/database-sql/connect_connector_iam_authn.go 
import (
	"context"
	"database/sql"
	"fmt"
	"log"
	"net"
	"os"

	"cloud.google.com/go/cloudsqlconn"
	"github.com/go-sql-driver/mysql"
)

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'
		dbName                 = mustGetenv("DB_NAME")                  // e.g. 'my-database'
		instanceConnectionName = mustGetenv("INSTANCE_CONNECTION_NAME") // e.g. 'project:region:instance'
		usePrivate             = os.Getenv("PRIVATE_IP")
	)

	d, err := cloudsqlconn.NewDialer(context.Background(), cloudsqlconn.WithIAMAuthN())
	if err != nil {
		return nil, fmt.Errorf("cloudsqlconn.NewDialer: %w", err)
	}
	var opts []cloudsqlconn.DialOption
	if usePrivate != "" {
		opts = append(opts, cloudsqlconn.WithPrivateIP())
	}
	mysql.RegisterDialContext("cloudsqlconn",
		func(ctx context.Context, addr string) (net.Conn, error) {
			return d.Dial(ctx, instanceConnectionName, opts...)
		})

	dbURI := fmt.Sprintf("%s:empty@cloudsqlconn(localhost:3306)/%s?parseTime=true",
		dbUser, dbName)

	dbPool, err := sql.Open("mysql", dbURI)
	if err != nil {
		return nil, fmt.Errorf("sql.Open: %w", err)
	}
	return dbPool, nil
}

Java JDBC

cloud-sql/mysql/servlet/src/main/java/com/example/cloudsql/ConnectorIamAuthnConnectionPoolFactory.java 
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 INSTANCE_UNIX_SOCKET = System.getenv("INSTANCE_UNIX_SOCKET");
  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:mysql:///<DB_NAME>?cloudSqlInstance=<INSTANCE_CONNECTION_NAME>&
    // socketFactory=com.google.cloud.sql.mysql.SocketFactory&user=<DB_USER>&password=<DB_PASS>
    // 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 user to connect with.
    config.setJdbcUrl(String.format("jdbc:mysql:///%s", DB_NAME));

    config.addDataSourceProperty("socketFactory", "com.google.cloud.sql.mysql.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);
    // 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");


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

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

Java R2DBC

r2dbc/mysql/src/test/java/com/google/cloud/sql/core/R2dbcMysqlIamAuthIntegrationTests.java 
// Set up ConnectionFactoryOptions
ConnectionFactoryOptions options =
    ConnectionFactoryOptions.builder()
        .option(DRIVER, "gcp")
        .option(PROTOCOL, "mysql")
        .option(USER, DB_USER)
        .option(DATABASE, DB_NAME)
        .option(HOST, CONNECTION_NAME)
        .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

cloud-sql/mysql/sqlalchemy/connect_connector_auto_iam_authn.py 
import os

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

import sqlalchemy


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

    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. 'service-account-name'
    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()

    def getconn() -> pymysql.connections.Connection:
        conn: pymysql.connections.Connection = connector.connect(
            instance_connection_name,
            "pymysql",
            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(
        "mysql+pymysql://",
        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:

To use the gcloud CLI to generate this token and log in:

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.
    • USERNAME: For an IAM user account, this is the user's email address, without the @ or domain name. For example, for test-user@gmail.com, enter test-user. For a service account, this is the service account's email address without the @project-id.iam.gserviceaccount.com suffix. 

    MYSQL_PWD=`gcloud sql generate-login-token` mysql --enable-cleartext-plugin 
    
         --ssl-ca=server-ca.pem --ssl-cert=client-cert.pem 
    
         --ssl-key=client-key.pem --host=HOSTNAME 
    
         --user=USERNAME

What's next