Postgres on Google Cloud / Reference

Connection cheat sheet

Everything you type, on one page. Print it.

Placeholders

Ports and destinations

Laptop — proxy and psql

# install (macOS)
brew install cloud-sql-proxy libpq

# authenticate — the application-default variant
gcloud auth application-default login

# proxy, left running in its own terminal
cloud-sql-proxy --port 5432 --auto-iam-authn \
  PROJECT_ID:REGION:INSTANCE_NAME

# connect — no password, none needed
psql -h 127.0.0.1 -p 5432 -U you@example.com -d appdb

# first thing you run, every time
SELECT current_user, current_database();

GUI clients

Impersonating a service account, to test

# requires roles/iam.serviceAccountTokenCreator ON that service account
cloud-sql-proxy --port 5434 --auto-iam-authn \
  --impersonate-service-account=app@PROJECT_ID.iam.gserviceaccount.com \
  PROJECT_ID:REGION:INSTANCE_NAME

psql -h 127.0.0.1 -p 5434 -U "app@PROJECT_ID.iam" -d appdb

Application — Python connector

pip install "cloud-sql-python-connector[pg8000]" sqlalchemy

import os
from google.cloud.sql.connector import Connector
import sqlalchemy

connector = Connector()          # ONCE per process, at startup

def getconn():
    return connector.connect(
        os.environ["INSTANCE_URI"],
        "pg8000",
        user=os.environ["DB_USER"],
        db=os.environ["DB_NAME"],
        enable_iam_auth=True,
    )

engine = sqlalchemy.create_engine(
    "postgresql+pg8000://",
    creator=getconn,
    pool_size=5, max_overflow=2, pool_recycle=1800,
)

The flag, in each language

Setup — the three layers

# LAYER 1 — project IAM. Both roles. To a group.
gcloud projects add-iam-policy-binding PROJECT_ID \
  --member="group:db-users@example.com" --role="roles/cloudsql.client"
gcloud projects add-iam-policy-binding PROJECT_ID \
  --member="group:db-users@example.com" --role="roles/cloudsql.instanceUser"

# LAYER 2 — IAM database users on the instance
gcloud sql users create db-users@example.com \
  --instance=INSTANCE_NAME --type=cloud_iam_group
gcloud sql users create you@example.com \
  --instance=INSTANCE_NAME --type=cloud_iam_user
gcloud sql users create app@PROJECT_ID.iam.gserviceaccount.com \
  --instance=INSTANCE_NAME --type=cloud_iam_service_account
  # ^ full email WITH suffix when creating; WITHOUT it when connecting

# the instance needs the flag on, once
gcloud sql instances patch INSTANCE_NAME \
  --database-flags=cloudsql.iam_authentication=on   # RESTARTS the instance

# LAYER 3 — PostgreSQL, via psql
GRANT app_rw_appdb TO "db-users@example.com";

Useful checks

# who am I, and where
SELECT current_user, session_user, current_database();

# what IAM users exist on the instance
gcloud sql users list --instance=INSTANCE_NAME

# who holds project IAM
gcloud projects get-iam-policy PROJECT_ID \
  --flatten="bindings[].members" \
  --filter="bindings.role:roles/cloudsql"

# my role memberships
SELECT r.rolname AS member, g.rolname AS granted_role
FROM pg_auth_members m
JOIN pg_roles r ON r.oid = m.member
JOIN pg_roles g ON g.oid = m.roleid
ORDER BY 1, 2;

# can this principal actually read this table?
SELECT has_table_privilege('app@PROJECT_ID.iam', 'public.customers', 'SELECT');

# user-managed keys — more than one after a rotation is a finding
gcloud iam service-accounts keys list \
  --iam-account=app@PROJECT_ID.iam.gserviceaccount.com --managed-by=user

Rules that are always true

• Egress is 3307, never 5432.
• Client SSL is off when going through the proxy.
• Service-account usernames drop .gserviceaccount.com when connecting.
• One connector per process, not per request.
• Access tokens last one hour; let the library refresh them.
• Restart a proxy left running overnight before debugging anything.
• Grant to groups, not to individuals.
• Re-run grants after every restore.