Enable IAM Database Authentication for Cloud SQL Database Instances

Sign in to Google Cloud Management Console.

Select the Google Cloud Platform (GCP) project that you want to access from the console top navigation bar.

Navigate to Cloud SQL Instances at https://console.cloud.google.com/sql/instances.

Click on the name (ID) of the PostgreSQL or MySQL database instance that you want to examine.

In the navigation panel, select Overview to access the configuration details available for the selected instance.

In the Configuration section, under Database flags, check the configuration value set for the IAM authentication database flag applicable to the selected instance's database engine – cloudsql.iam_authentication for PostgreSQL instances, or cloudsql_iam_authentication for MySQL instances. If the applicable flag is not listed in the Database flags section, or is set to off, IAM database authentication is disabled for the selected Cloud SQL database instance.

Repeat step no. 4 – 6 to check the IAM database authentication flag configuration for other PostgreSQL and MySQL database instances available within the selected project.

Repeat steps no. 2 – 7 for each project deployed in your Google Cloud account.

Run projects list command (Windows/macOS/Linux) using output query filters to list the IDs of all the Google Cloud Platform (GCP) projects available in your cloud account:

gcloud projects list
--format="table(projectId)"

The command output should return the requested GCP project identifiers:

PROJECT_ID
cc-web-project-112233
cc-mobile-project-123123

Run sql instances list command (Windows/macOS/Linux) using output filtering to describe the name and database engine of each PostgreSQL and MySQL database instance provisioned for the selected Google Cloud project:

gcloud sql instances list
--project cc-web-project-112233
--filter='DATABASE_VERSION:(POSTGRES* OR MYSQL*)'
--format="(NAME,DATABASE_VERSION)"

The command output should return the requested database instance name(s) and their database engine:

NAME                       DATABASE_VERSION
cc-web-postgres-instance   POSTGRES_15
cc-app-mysql-instance      MYSQL_8_0

For a PostgreSQL database instance, run sql instances describe command (Windows/macOS/Linux) using the name of the instance that you want to examine as identifier parameter and output query filters to describe the "cloudsql.iam_authentication" flag configuration value set for the selected database instance:

gcloud sql instances describe cc-web-postgres-instance
--project cc-web-project-112233
--format=json | jq '(.settings.databaseFlags // []) | map(select(.name == "cloudsql.iam_authentication"))[0].value // null'

The command output should return the requested flag configuration value:

"on"

If the sql instances describe command output returns null or "off", the "cloudsql.iam_authentication" database flag is currently disabled for the selected Google Cloud PostgreSQL database instance, therefore IAM database authentication is not enabled.

For a MySQL database instance, run sql instances describe command (Windows/macOS/Linux) using the name of the instance that you want to examine as identifier parameter and output query filters to describe the "cloudsql_iam_authentication" flag configuration value set for the selected database instance:

gcloud sql instances describe cc-app-mysql-instance
--project cc-web-project-112233
--format=json | jq '(.settings.databaseFlags // []) | map(select(.name == "cloudsql_iam_authentication"))[0].value // null'

The command output should return the requested flag configuration value:

"on"

If the sql instances describe command output returns null or "off", the "cloudsql_iam_authentication" database flag is currently disabled for the selected Google Cloud MySQL database instance, therefore IAM database authentication is not enabled.

Repeat step no. 5 – 8 to verify the IAM database authentication flag configuration value for other PostgreSQL and MySQL database instances created for the selected project.

Repeat steps no. 3 – 9 for each project available within your Google Cloud account.

Sign in to Google Cloud Management Console.

Select the Google Cloud Platform (GCP) project that you want to access from the console top navigation bar.

Navigate to Cloud SQL Instances at https://console.cloud.google.com/sql/instances.

Click on the ID of the PostgreSQL or MySQL database instance that you want to reconfigure (see Audit section part I to identify the right resource).

In the navigation panel, select Overview to access the configuration details of the selected instance.

Click on the Edit button from the console top menu to enter the instance edit mode.

In the Configuration options section, click on the Flags tab to expand the panel with the database flags configured for the selected instance.

Find the IAM authentication flag applicable to the selected instance's database engine – cloudsql.iam_authentication for PostgreSQL instances, or cloudsql_iam_authentication for MySQL instances – and turn it on by selecting On from the flag configuration dropdown list. If the applicable flag has not been set on the selected instance before, click Add item, choose the flag from the Choose one dropdown menu, and set its value to On. Click Close to close the panel.

Choose Save to apply the configuration changes.

IMPORTANT: Reconfiguring an existing Cloud SQL database instance can produce downtime while the instance is being restarted.

Select the Users tab, choose ADD USER ACCOUNT, select Cloud IAM as the authentication type, provide the email address of the IAM user or the name of the service account that you want to grant database access to, then choose ADD to create the IAM-authenticated database user.

(Optional) Remove non-IAM database users to enforce IAM-only authentication. In the Users tab, identify and delete any legacy users that were created with standard username/password authentication, as these are no longer needed once IAM authentication is in place. Keep only the IAM-authenticated users to minimize the attack surface and ensure consistent access control through Cloud IAM.

Repeat step no. 4 – 11 to enable IAM database authentication and configure IAM database users for other PostgreSQL and MySQL database instances available within the selected project.

Repeat steps no. 2 – 12 for each project deployed in your Google Cloud account.

For a PostgreSQL database instance, run sql instances patch command (Windows/macOS/Linux) using the name of the instance that you want to reconfigure as identifier parameter (see Audit section part II to identify the right resource), to enable IAM database authentication by setting the "cloudsql.iam_authentication" flag value to on:

gcloud sql instances patch cc-web-postgres-instance
--project cc-web-project-112233
--database-flags cloudsql.iam_authentication=on

For a MySQL database instance, use the "cloudsql_iam_authentication" flag instead:

gcloud sql instances patch cc-app-mysql-instance
--project cc-web-project-112233
--database-flags cloudsql_iam_authentication=on

IMPORTANT: The --database-flags parameter overwrites all existing database flags configured on the instance. To prevent breaking existing performance or connection settings, first retrieve your current database flags using gcloud sql instances describe <INSTANCE_NAME> --format="value(settings.databaseFlags)", then include all existing flags in your patch command along with the new cloudsql.iam_authentication or cloudsql_iam_authentication flag.

IMPORTANT: Reconfiguring an existing Cloud SQL database instance can produce downtime while the instance is being restarted.

Type Y and press Enter to confirm the database configuration change:

The following message will be used for the patch API method.
{"name": "cc-web-postgres-instance", "project": "cc-web-project-112233", "settings": {"databaseFlags": [{"name": "cloudsql.iam_authentication", "value": "on"}]}}
WARNING: This patch modifies database flag values, which may require your instance to be restarted. Check the list of supported flags - https://cloud.google.com/sql/docs/postgres/flags - to see if your instance will be restarted when this patch is submitted.
Do you want to continue (Y/n)? Y

The output should return the sql instances patch command request status:

Patching Cloud SQL instance...done.
Updated [https://sqladmin.googleapis.com/sql/v1beta4/projects/cc-web-project-112233/instances/cc-web-postgres-instance].

Run sql users create command (Windows/macOS/Linux) to create a database user configured for IAM authentication on the reconfigured instance. For a user account:

gcloud sql users create jane.doe@cc-example.com
--project cc-web-project-112233
--instance=cc-web-postgres-instance
--type=CLOUD_IAM_USER

For a service account:

gcloud sql users create cc-app-service-account@cc-web-project-112233
--project cc-web-project-112233
--instance=cc-web-postgres-instance
--type=CLOUD_IAM_SERVICE_ACCOUNT

Note: Specify the service account username as "<service-account-name>@<project-id>", without the ".iam.gserviceaccount.com" suffix.

Run projects add-iam-policy-binding command (Windows/macOS/Linux) to grant the IAM principal the Cloud SQL IAM roles required to connect to and log in to the Cloud SQL instance:

gcloud projects add-iam-policy-binding cc-web-project-112233
--member=user:jane.doe@cc-example.com
--role=roles/cloudsql.client

gcloud projects add-iam-policy-binding cc-web-project-112233
--member=user:jane.doe@cc-example.com
--role=roles/cloudsql.instanceUser

For a service account, replace "user:" with "serviceAccount:" and use the full service account email, including the ".iam.gserviceaccount.com" suffix:

gcloud projects add-iam-policy-binding cc-web-project-112233
--member=serviceAccount:cc-app-service-account@cc-web-project-112233.iam.gserviceaccount.com
--role=roles/cloudsql.client

gcloud projects add-iam-policy-binding cc-web-project-112233
--member=serviceAccount:cc-app-service-account@cc-web-project-112233.iam.gserviceaccount.com
--role=roles/cloudsql.instanceUser

Where roles/cloudsql.client allows the IAM principal to connect to Cloud SQL instances, and roles/cloudsql.instanceUser allows the IAM principal to log in to Cloud SQL instances using IAM credentials.

(Optional) Test the new configuration by running beta sql connect command (Windows/macOS/Linux) to connect to the reconfigured database instance using IAM database authentication via the Cloud SQL Auth Proxy:

gcloud beta sql connect cc-web-postgres-instance
--project cc-web-project-112233
--user=jane.doe@cc-example.com
--database=cc-app-db

Repeat step no. 1 – 6 to enable IAM database authentication and configure IAM database users for other PostgreSQL and MySQL database instances provisioned for the selected project.

Repeat steps no. 1 – 7 for each project created within your Google Cloud account.