Connect to Cloud SQL for PostgreSQL using GCP IAM authentication

Liquibase connects as whichever Google Cloud identity it finds in Application Default Credentials. That can be your own Google account, which suits local work, or a service account, which is what a pipeline should use. The rest of this article calls it your service account, so substitute your own address throughout if you are connecting as yourself.

You need your project ID for the commands that follow. Read the one your CLI is already set to, or list the projects you can see.

To connect as a service account, create one. Its email address is always the name you choose, followed by @your_project_id.iam.gserviceaccount.com.

Be sure to:

• Replace your_service_account_name with a name for the service account. For example, my-service-account

• Replace your_project_id with your Google Cloud project ID. For example, my-project

Confirm the address, which is the value every later step calls your_service_account_email.

Note: Creating a service account does not let you act as it. If you plan to run Liquibase locally under this service account, you also need roles/iam.serviceAccountTokenCreator on it, which the impersonation step below shows how to grant.

The identity that connects needs two project-level roles. roles/cloudsql.client lets it open a connection through the connector, and roles/cloudsql.instanceUser lets it log in with IAM.

Be sure to:

• Replace your_project_id with your Google Cloud project ID. For example, my-project

• Replace your_service_account_email with the service account Liquibase authenticates as. For example, my-service-account@my-project.iam.gserviceaccount.com

Note: To connect as yourself rather than as a service account, use --member=user:your_email instead.

Cloud SQL maps a Google Cloud identity to a database user. On PostgreSQL the database user name is the service account email with the .gserviceaccount.com suffix removed.

Note: Shorten the name yourself. The Cloud SQL Java Connector, which Liquibase uses, does not shorten a service account email for you, so the full .gserviceaccount.com address fails to log in. Other Cloud SQL connectors do shorten it, so a connection that works elsewhere is not evidence that the full address works here.

To connect as a person instead of a service account, create the user with --type=CLOUD_IAM_USER and the full email address, such as you@example.com. A person's address is not shortened, so that same address is also the user name you log in with.

Be sure to:

• Replace your_database_user with your service account email without the .gserviceaccount.com suffix. For example, my-service-account@my-project.iam

• Replace your_instance_id with the name of your Cloud SQL instance. For example, my-instance

Confirm the user was created before granting it privileges.

An IAM database user can authenticate but starts with no privileges on your database. Connect as an administrative user and grant the privileges Liquibase needs, including the right to create the tracking tables.

Note: Quote the user name. It contains an @, so PostgreSQL rejects it unquoted.

Be sure to:

• Replace your_database with the name of your database. For example, lbcat

• Replace your_database_user with the database user you created. For example, my-service-account@my-project.iam

Liquibase authenticates with Application Default Credentials (ADC). Credentials come from the environment Liquibase runs in, never from Liquibase configuration files, so there is no credential to set in your properties. Use whichever method fits where Liquibase runs.

Google Cloud CLI (local development): Sign in to create local ADC. Liquibase reuses the resulting credentials automatically, so you do not need a service account key.

Workload identity (Google Cloud-hosted workloads such as Cloud Run, GKE, and Compute Engine): Attach a service account to the resource and grant it the roles you granted earlier. Authentication happens automatically with no further configuration.

Service account key file (CI/CD pipelines, containers): Set GOOGLE_APPLICATION_CREDENTIALS to the path of a service account key file.

Be sure to:

• Replace your_key_file with the path to your service account key file. For example, /etc/liquibase/gcp-key.json

Note: ADC signs in as whoever your browser is signed into, so it is easy to end up authenticated as the wrong Google account. A later cloudsql.instances.get permission error usually means the wrong identity rather than a missing role. Confirm which identity ADC holds before you change any role.

Set the instance connection name and a password value of gcp-cloudsql-iam. The JDBC URL needs no host, because the connector dials the instance named by liquibase.cloudsql.instance rather than a host name.

Be sure to:

• Replace your_database with the name of your database. For example, lbcat

• Replace your_database_user with the database user you created. For example, my-service-account@my-project.iam

• Replace your_instance_connection_name with your instance connection name, in project:region:instance format. For example, my-project:us-east1:my-instance

Note: The password value is matched exactly. Letter case and surrounding spaces are ignored, but gcp-cloudsql-iam takes no arguments after a comma, and a value such as gcp-cloudsql-iam,my-project:us-east1:my-instance does not activate IAM authentication. The instance always goes in liquibase.cloudsql.instance.

Liquibase configures TLS on the connection itself, so you do not add an SSL parameter to the JDBC URL. The connector validates the instance's server certificate on your behalf.

Complete this step only if Liquibase should connect as a service account other than the identity in your ADC. This is the usual arrangement for cross-project access, and for pipelines whose own identity holds no database permissions.

The target service account is the one that reaches the database, so it needs the Cloud SQL roles, and the database user and privileges, from the earlier steps. Your own identity needs permission to act as it.

Be sure to:

• Replace your_target_service_account with the service account to impersonate. For example, my-target-account@target-project.iam.gserviceaccount.com

• Replace your_email with the identity your ADC holds. For example, you@example.com

Then name the target in your Liquibase configuration.

Note: An IAM change can take a few minutes to take effect. Impersonation denied immediately after you grant it is usually propagation rather than a missing role, so wait and try again before changing anything.

Complete this step only if your identity cannot impersonate the target directly and has to go through one or more intermediate service accounts. Each account in the chain needs roles/iam.serviceAccountTokenCreator on the next one.

Be sure to:

• Replace your_delegate_service_account with the intermediate service account. For example, my-delegate-account@my-project.iam.gserviceaccount.com

• Replace your_target_service_account with the service account that reaches the database. For example, my-target-account@target-project.iam.gserviceaccount.com

Separate multiple delegates with commas, in the order the chain is traversed.

Note: Delegates apply only alongside a target service account. When liquibase.cloudsql.delegates is set and liquibase.cloudsql.targetServiceAccount is not, Liquibase logs a warning and ignores the delegates.

Create a changelog file in your project directory and add a changeset. Liquibase accepts XML, SQL, YAML, and JSON.

Run status to confirm Liquibase authenticates and reaches your database. It reports which changesets are still pending, so you can verify the connection without applying your changelog.

Then run update to apply the changeset.

Liquibase starts the Cloud SQL connector, authenticates with your IAM identity, and creates the DATABASECHANGELOG and DATABASECHANGELOGLOCK tracking tables. The password value is masked in the log.