Logging in to a database with 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

Logging 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

jdbc/postgres/src/test/java/com/google/cloud/sql/postgres/JdbcPostgresIamAuthIntegrationTests.java 
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

r2dbc/postgres/src/test/java/com/google/cloud/sql/core/R2dbcPostgresIamAuthIntegrationTests.java 
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

tests/system/test_pg8000_iam_auth.py 
# 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

Logging 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 Cloud SDK, you can explicitly request an OAuth 2.0 token with the Cloud SQL 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.

To use the Cloud SDK 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 auth print-access-token) psql --host=HOSTNAME 
    
                                                --username=USERNAME 
    
                                                --dbname=DATABASE_NAME

Java

Service account

  1. Create your credentials using the steps in Creating service account keys.

  2. Use the following code to generate an access token string, then use that token as your password to authenticate into the database.

    import com.google.api.client.googleapis.auth.oauth2.GoogleCredential;
import com.google.api.services.sqladmin.SQLAdminScopes;
// ...
GoogleCredential credentials = GoogleCredential.fromStream(new FileInputStream("PATH_TO_SERVICE_ACCT_KEY")).createScoped(Collections.singleton(SQLAdminScopes.SQLSERVICE_ADMIN));
String accessToken = credentials.getAccessToken();

  3. Initialize the connection pool. Use the access token from the previous step as the password, and the email as the username.

    To see this snippet in the context of a web application, view the README on GitHub.

    cloud-sql/mysql/servlet/src/main/java/com/example/cloudsql/ConnectionPoolContextListener.java 
    // Note: For Java users, the Cloud SQL JDBC Socket Factory can provide authenticated connections
// which is preferred to using the Cloud SQL Proxy with Unix sockets.
// See https://github.com/GoogleCloudPlatform/cloud-sql-jdbc-socket-factory for details.

// 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.setUsername(DB_USER); // e.g. "root", "mysql"
config.setPassword(DB_PASS); // e.g. "my-password"

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

// The ipTypes argument can be used to specify a comma delimited list of preferred IP types 
// for connecting to a Cloud SQL instance. The argument ipTypes=PRIVATE will force the 
// SocketFactory to connect with an instance's associated private IP. 
config.addDataSourceProperty("ipTypes", "PUBLIC,PRIVATE");

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

// Initialize the connection pool using the configuration object.
DataSource pool = new HikariDataSource(config);

  4. Connect to your instance.

    cloud-sql/mysql/servlet/src/main/java/com/example/cloudsql/IndexServlet.java 
    // Using a try-with-resources statement ensures that the connection is always released back
// into the pool at the end of the statement (even if an error occurs)
try (Connection conn = pool.getConnection()) {

  // PreparedStatements can be more efficient and project against injections.
  String stmt = "INSERT INTO votes (time_cast, candidate) VALUES (?, ?);";
  try (PreparedStatement voteStmt = conn.prepareStatement(stmt);) {
    voteStmt.setTimestamp(1, now);
    voteStmt.setString(2, team);

    // Finally, execute the statement. If it fails, an error will be thrown.
    voteStmt.execute();
  }
} catch (SQLException ex) {
  // If something goes wrong, handle the error in this section. This might involve retrying or
  // adjusting parameters depending on the situation.
  // ...
}

Python

Service account

  1. Create your credentials using the steps in Creating service account keys.

  2. Use the following code to generate an access token string. Then use that token as your password to authenticate into the database.

    from google.oauth2 import service_account
SCOPES = ['https://www.googleapis.com/auth/sqlservice.admin']
SERVICE_ACCOUNT_FILE = '/PATH_TO_SERVICE_ACCT_KEY'
credentials = service_account.Credentials.from_service_account_file( SERVICE_ACCOUNT_FILE, scopes=SCOPES)
access_token = credentials.token

  3. Initialize the connection pool. Use the access token from the previous step as the password, and the email as the username.

    To see this snippet in the context of a web application, see the README on GitHub.

    cloud-sql/mysql/sqlalchemy/main.py 
    # Remember - storing secrets in plaintext is potentially unsafe. Consider using
# something like https://cloud.google.com/secret-manager/docs/overview to help keep
# secrets secret.
db_user = os.environ["DB_USER"]
db_pass = os.environ["DB_PASS"]
db_name = os.environ["DB_NAME"]
db_host = os.environ["DB_HOST"]

# Extract port from db_host if present,
# otherwise use DB_PORT environment variable.
host_args = db_host.split(":")
if len(host_args) == 1:
    db_hostname = db_host
    db_port = os.environ["DB_PORT"]
elif len(host_args) == 2:
    db_hostname, db_port = host_args[0], int(host_args[1])

pool = sqlalchemy.create_engine(
    # Equivalent URL:
    # mysql+pymysql://<db_user>:<db_pass>@<db_host>:<db_port>/<db_name>
    sqlalchemy.engine.url.URL.create(
        drivername="mysql+pymysql",
        username=db_user,  # e.g. "my-database-user"
        password=db_pass,  # e.g. "my-database-password"
        host=db_hostname,  # e.g. "127.0.0.1"
        port=db_port,  # e.g. 3306
        database=db_name,  # e.g. "my-database-name"
    ),
    **db_config
)

  4. Connect to your instance.

    cloud-sql/mysql/sqlalchemy/main.py 
    # Preparing a statement before hand can help protect against injections.
stmt = sqlalchemy.text(
    "INSERT INTO votes (time_cast, candidate)" " VALUES (:time_cast, :candidate)"
)
try:
    # Using a with statement ensures that the connection is always released
    # back into the pool at the end of statement (even if an error occurs)
    with db.connect() as conn:
        conn.execute(stmt, time_cast=time_cast, candidate=team)
except Exception as e:
    # If something goes wrong, handle the error in this section. This might
    # involve retrying or adjusting parameters depending on the situation.
    # ...

What's next