GCP Cloud SQL Auth Proxy & IAM

draft

Industry standard and most secure way to grant developers access.

March 25, 2026database1377 words · 7 min read
#gcp#sql#iam#cloud#access#security

Table of Contents

Introduction

In 2026, the industry standard and most secure way to grant developers access to a Google Cloud SQL database is a combination of the Cloud SQL Auth Proxy and IAM Database Authentication.

This approach avoids the security risks of managing static IP allowlists (authorized networks) and the hassle of manually distributing SSL certificates or database passwords.

The Best Practice Architecture

Instead of exposing your database to the public internet, you keep it on a Private IP. Developers then connect through a secure tunnel.

  • Cloud SQL Auth Proxy: A small binary developers run locally. It creates a secure, encrypted TLS 1.3 tunnel to the instance.
  • IAM Database Authentication: Instead of a traditional username/password, developers log in using their Google Workspace/IAM identity. Google handles the token exchange and rotation automatically.

Configure the Cloud SQL Instance

To set up Cloud SQL with IAM authentication via Terraform, you need to configure three main components: the Database Instance settings, the IAM User, and the Project-level permissions.

resource "google_sql_database_instance" "postgres_stage" {
  ...
  settings {
    ...
    database_flags {
      name  = "cloudsql.iam_authentication"
      value = "on"
    }
  }
  ...
}

Google Cloud's API distinguishes between engine-specific flags:

  • Postgres: cloudsql.iam_authentication
  • MySQL: cloudsql_iam_authentication
  • SQL Server: cloudsql_iam_authentication

Grant GCP Permissions: The developer needs the following IAM roles on the project or instance:

  • roles/cloudsql.client to connect via the proxy
  • roles/cloudsql.instanceUser to log in using their IAM identity

Add the Developer as a DB User and allow them to use proxy:

locals {
  developer_emails = [
    "username@email.com",
  ]
}

# Database Users
resource "google_sql_user" "developers" {
  for_each = toset(local.developer_emails)

  name     = each.value
  instance = google_sql_database_instance.postgres_stage.name
  type     = "CLOUD_IAM_USER"
}

# allow to use the proxy
resource "google_project_iam_member" "sql_client" {
  for_each = toset(local.developer_emails)

  project = var.project_id
  role    = "roles/cloudsql.client"
  member  = "user:${each.value}"
}

The user needs permission to actually "log in" via the GCP control plane. Without these project-level roles, the connection will be rejected even if the user exists in the DB.

resource "google_project_iam_member" "sql_instance_user" {
  for_each = toset(local.developer_emails)

  project = var.project_id
  role    = "roles/cloudsql.instanceUser"
  member  = "user:${each.value}"
}

Permissions required:

gcloud projects add-iam-policy-binding project-prod \
  --member="serviceAccount:terraform-mgr@project-prod.iam.gserviceaccount.com" \
  --role="roles/resourcemanager.projectIamAdmin"

Developer Setup (Local Machine)

Your developers will perform these three steps to connect:

Authenticate gcloud:

gcloud auth application-default login

How to ensure login credentials:

curl -H "Authorization: Bearer $(gcloud auth application-default print-access-token)" \
     https://www.googleapis.com/oauth2/v1/userinfo
cat ~/.config/gcloud/application_default_credentials.json

The developer runs the proxy locally, pointing it to your instance's connection name.

You can get instance name from terraform:

resource "google_sql_database_instance" "postgres_stage" {
  name             = "INSTANCE_NAME"
  region           = "europe-west3"
  ...

https://docs.cloud.google.com/sdk/docs/install-sdk https://docs.cloud.google.com/sql/docs/postgres/sql-proxy?authuser=1 https://docs.cloud.google.com/sql/docs/postgres/sql-proxy?authuser=1#install

Then get connection name and connect:

gcloud sql instances describe INSTANCE_NAME --format='value(connectionName)'
./cloud-sql-proxy --auto-iam-authn [INSTANCE_CONNECTION_NAME]

The --auto-iam-authn flag is crucial; it tells the proxy to handle the IAM token exchange so the developer doesn't need a password.

You can start a single instance of the proxy and tell it to listen on different ports for different databases by repeating the --port flag followed by the instance name.

./cloud-sql-proxy --auto-iam-authn \
  --port 5432 project-dev:region:db-stage \
  --port 5433 project-prod:region:db-prod

They can now point their favorite tool (DBeaver, TablePlus, or CLI) to 127.0.0.1 on the default port 5432 for Postgres, 3306 for MySQL. They use their email address as the username and leave the password blank.

psql "host=127.0.0.1 port=5432 dbname=DB_NAME user=YOUR_EMAIL@DOMAIN.COM"

Or:

psql -h 127.0.0.1 -p 5432 -U YOUR_EMAIL@DOMAIN.COM -d DB_NAME

To psql, it looks like a normal database connection. The Proxy is the one doing the heavy lifting behind the scenes.

Use this for private Cloud SQL configurations:

./cloud-sql-proxy --private-ip --auto-iam-authn project-prod:europe-west3:db-prod --port 5433

Cloud SQL Auth Proxy Magic

Interception. When you run psql -h 127.0.0.1, psql sends a connection request to your local machine on port 5432. The Cloud SQL Auth Proxy is "listening" on that port. It catches the request before it ever leaves your computer.

The IAM Token Exchange. Because you started the proxy with the --auto-iam-authn flag, the proxy performs these steps instantly:

  • It looks at your Application Default Credentials (the JSON file we found earlier) to see who you are.
  • It calls the Google Cloud IAM API to fetch a temporary OAuth2 access token (a long string of random characters).
  • It uses this token as the "password" for the database connection.

Tricking the Database. When the Proxy connects to the actual Cloud SQL instance in GCP it tells the database logging in your-email@company.com credentials. When the database asks for a password, the Proxy sends that OAuth2 access token instead of a real password. The Cloud SQL instance which you configured with the cloudsql.iam_authentication flag sees the token, verifies it with Google’s IAM servers.

Database Permissions

By default, an IAM user has zero permissions inside the database itself. To make the user useful, you (or a DB admin) must log in as the default postgres user and grant them the following:

-- 1. Allow them to use the public schema
GRANT USAGE ON SCHEMA public TO "developer@yourcompany.com";

-- 2. Allow them to see and edit existing tables
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO "developer@yourcompany.com";

-- 3. Ensure they can use future tables too
ALTER DEFAULT PRIVILEGES IN SCHEMA public 
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO "developer@yourcompany.com";

If you want them to be able to create new tables, indexes, or extensions (like PostGIS), grant them the Cloud SQL managed admin role:

GRANT cloudsqlsuperuser TO "developer@yourcompany.com";

If you have "lots of developers" as you mentioned, don't run these GRANT commands for every person. Instead, create a Postgres Group Role:

  • Create the role once
  • Grant permissions to the group
  • Assign developers to the group
-- 1. Create the group role (NOLOGIN means no one logs in as this directly)
CREATE ROLE developer_role NOLOGIN;

-- 2. Grant permissions on the 'public' schema
GRANT USAGE, CREATE ON SCHEMA public TO developer_role;

-- 3. Grant permissions on EXISTING tables
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO developer_role;
GRANT USAGE, SELECT, UPDATE ON ALL SEQUENCES IN SCHEMA public TO developer_role;

-- 4. Grant permissions on FUTURE tables (Crucial!)
ALTER DEFAULT PRIVILEGES IN SCHEMA public 
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO developer_role;
ALTER DEFAULT PRIVILEGES IN SCHEMA public 
GRANT USAGE, SELECT, UPDATE ON SEQUENCES TO developer_role;

Grant this role to a developer:

GRANT developer_role TO "alice@yourcompany.com";

Revocation:

-- This stops the role from seeing objects in the schema ('USAGE')
-- and from creating new objects like tables or views ('CREATE')
REVOKE USAGE, CREATE ON SCHEMA public FROM developer_role;

Run this query to see a list of every user currently logged in, their IP address, and what they are doing:

SELECT pid, usename, client_addr, state, query 
FROM pg_stat_activity 
WHERE datname = current_database();

If you want to see how many sessions each developer has open (useful if you are hitting max_connections limits):

SELECT usename, count(*) 
FROM pg_stat_activity 
GROUP BY usename 
ORDER BY count DESC;

If a developer accidentally runs a query that "hangs" or takes too long, you can find it with this:

SELECT pid, usename, now() - query_start AS duration, query
FROM pg_stat_activity
WHERE state = 'active'
ORDER BY duration DESC;

If you see a developer's connection that needs to be closed (for example, if they left an idle in transaction session open that is locking a table), you can kill it using the pid:

-- This gracefully stops the current query
SELECT pg_cancel_backend(PID);

-- This forcefully kills the entire connection
SELECT pg_terminate_backend(PID);

To prevent the dump restoration from messing up your GCP ownership and permissions, you should use the -O (No Owner) and -x (No Privileges) flags.

pg_restore -h [GCP_IP] -U [ADMIN_USER] -d [DB_NAME] \
  --no-owner \
  --no-privileges \
  --clean \
  [YOUR_DUMP_FILE]

--no-owner (-O): Prevents the restore from trying to make the "Azure Admin" the owner of the tables in GCP (which usually fails anyway). All tables will instead be owned by the user running the restore.

--no-privileges (-x): This tells pg_restore to completely ignore the GRANT and REVOKE commands from Azure. Your tables will be created with "clean" default permissions.

Re-Apply your "Permissions Script": Run the GRANT commands we discussed earlier to give the developer_role access to the newly created tables.

-- Re-run this after the restore:
GRANT USAGE ON SCHEMA public TO developer_role;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO developer_role;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO developer_role;