Skip to content

Cloud SQL - IAM Authentication

Imported from Confluence

Content may be outdated. Verify before following any procedures. View original | Last updated: April 2024

Reference task: DEVOPSBLN-4155

To provide a more secure way for authenticating to our Cloud SQL databases in GCP, we have decided to adopt Cloud IAM Database Authentication as our preferred method moving forward. This page explains what you would need to be aware of when using the new authentication method.

By using Cloud IAM Database Authentication, we:

  • eliminate the need for administration tasks relating to managing passwords like rotation, resetting, and storage etc.
  • reduce password related vulnerabilities and risks such as weak passwords or password reuse.

Steps to setup your environment

  1. Install the Cloud SQL Auth Proxy Client:
    Refer to: Google Cloud - Mysql Connect Auth Proxy for your OS.
    Note that example link below is for macOS 64-bit.
cd ~
curl -o cloud-sql-proxy https://storage.googleapis.com/cloud-sql-connectors/cloud-sql-proxy/v2.7.1/cloud-sql-proxy.darwin.amd64
chmod +x cloud-sql-proxy
  1. Determine the instance connection name for the CloudSQL Instance you intend to connect to.

Note: The most straightforward method to get your instance's connection name is to check from the DB replica list found in Step 3.
(else if your DB instance is new, and does not exist in the list) Please check through the GCP console.

To find the instance connection name on the GCP console. Refer to: GCP Console - Instances

Ensure that you are in the appropriate project, such as "agp-offerwall-prod-oi" to see a list of databases in that project.
The instance connection name is usually in the form : ::, for example, agp-offerwall-prod-oi:us-east1:acp-edge-rds-se-replica 3. DB Replica list

S/N Project Database Name (used in DBeaver) Database Instance Instance connection name DNS Endpoint
1 Offerwall ofw_production acp-edge-replica agp-offerwall-prod-oi:us-east1:acp-edge-rds-se-replica acp-edge-replica.db-offerwall-prod-useast1.fyber.com
2 supply_production supply-db-replica agp-offerwall-prod-oi:us-east1:aws-supply-db-rds-se-replica supplydb-replica.db-offerwall-prod-useast1.fyber.com
4. Authenticate the gcloud cli 
# Check if gcloud cli is already installed on your computer. You only need to do this once.
❯ gcloud -v
Google Cloud SDK 437.0.1
beta 2023.06.30

# if not installed, install the gcloud cli for your OS from: 
https://cloud.google.com/sdk/docs/install

# Login with your user

❯ gcloud auth application-default login

# Note: This command will redirect to your browser and authenticate your gcloud as your user.

❯ export PROJECT_ID="agp-offerwall-prod-oi"
❯ gcloud config set project $PROJECT_ID
Updated property [core/project].
  1. Start the Cloud SQL Auth Proxy in one terminal on your local like so:
./cloud-sql-proxy --address 0.0.0.0 --port 1234 --private-ip --auto-iam-authn INSTANCE_CONNECTION_NAME

Keep it running so you can monitor its output. Proxy should be listening for connections in your terminal.

❯ ./cloud-sql-proxy --address 0.0.0.0 --port 1234 --private-ip --auto-iam-authn agp-offerwall-prod-oi:us-east1:acp-edge-rds-se-replica
2023/11/07 11:18:11 Authorizing with Application Default Credentials
2023/11/07 11:18:12 [agp-offerwall-prod-oi:us-east1:acp-edge-rds-se-replica] Listening on [::]:1234
2023/11/07 11:18:12 The proxy has started successfully and is ready for new connections!
  1. Login to DBeaver, selecting PostreSQL connection, without need for a password as set in your proxy start command
host 0.0.0.0
port 1234
username: YOUR_DT_EMAIL
database: database_you_want_to_connect_with (see step 3 for database names)

Google Cloud - Postgres Authentication