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 Overview of 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 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. 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 -enable_iam_login flag.

    Replace the following:

    ./cloud_sql_proxy -enable_iam_login -instances=INSTANCE_CONNECTION_NAME=tcp:5432
    

    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 Connecting using the Cloud SQL Auth proxy.

Java JDBC

private static final String CONNECTION_NAME = System.getenv("POSTGRES_IAM_CONNECTION_NAME");
private static final String DB_NAME = System.getenv("POSTGRES_DB");
private static final String DB_USER = System.getenv("POSTGRES_IAM_USER");
  // Set up URL parameters
  String jdbcURL = String.format("jdbc:postgresql:///%s", DB_NAME);
  Properties connProps = new Properties();
  connProps.setProperty("user", DB_USER);
  // Password must be set to a nonempty value to bypass driver validation errors
  connProps.setProperty("password", "password");
  connProps.setProperty("sslmode", "disable");
  connProps.setProperty("socketFactory", "com.google.cloud.sql.postgres.SocketFactory");
  connProps.setProperty("cloudSqlInstance", CONNECTION_NAME);
  connProps.setProperty("enableIamAuth", "true");

  // Initialize connection pool
  HikariConfig config = new HikariConfig();
  config.setJdbcUrl(jdbcURL);
  config.setDataSourceProperties(connProps);
  config.setConnectionTimeout(10000); // 10s

  this.connectionPool = new HikariDataSource(config);

Java R2DBC

private static final String CONNECTION_NAME = System.getenv("POSTGRES_IAM_CONNECTION_NAME");
private static final String DB_NAME = System.getenv("POSTGRES_DB");
private static final String DB_USER = System.getenv("POSTGRES_IAM_USER");
  // 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(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

# The Cloud SQL Python Connector can be used along with SQLAlchemy using the
# 'creator' argument to 'create_engine'
def init_connection_engine() -> sqlalchemy.engine.Engine:
    def getconn() -> pg8000.dbapi.Connection:
        conn: pg8000.dbapi.Connection = connector.connect(
            os.environ["POSTGRES_IAM_CONNECTION_NAME"],
            "pg8000",
            user=os.environ["POSTGRES_IAM_USER"],
            db=os.environ["POSTGRES_DB"],
            enable_iam_auth=True,
        )
        return conn

    engine = sqlalchemy.create_engine(
        "postgresql+pg8000://",
        creator=getconn,
    )
    engine.dialect.description_encoding = None
    return engine

Log in with manual IAM database authentication

Note: If you plan to use the Cloud SQL Auth proxy, the Java connector, or the Python connector, see Logging in with automatic.

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, 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 these steps, 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.

To use the gcloud 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 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.

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

    Replace the following:

    • HOSTNAME: The IP address of the instance.
    • USERNAME: The username is the email address to use for connecting 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 sql generate-login-token) psql --host=HOSTNAME 
    --username=USERNAME
    --dbname=DATABASE_NAME

What's next