Google Cloud SQL for PostgreSQL Community

Status: tested against querycop HEAD on 2026-05-18. Endpoint and gcloud CLI conventions cited as of 2026-05.

This page covers running Querycop in front of Google Cloud SQL for PostgreSQL via a direct TLS connection. The Cloud SQL Auth Proxy is mentioned in Gotchas as a complementary deployment style; the cookbook recipe uses a direct connection because it requires no extra long-running process next to Querycop and exercises Querycop’s own backend TLS / IAM-token paths.

For Google AlloyDB, see alloydb.md (Tier P1) — the endpoint shape and IAM-token mint differ enough to warrant a separate page.

TL;DR

For a Cloud SQL instance with IAM auth, connecting via direct IP, the production-ready config is:

Env var	Value
GATEKEEPER_BACKEND_HOST	34.123.45.67 (public IP) or 10.20.30.40 (private IP)
GATEKEEPER_BACKEND_PORT	5432
GATEKEEPER_BACKEND_TLS_MODE	verify-ca (default for IP-based connections — see Gotchas for the verify-full recipe)
GATEKEEPER_BACKEND_TLS_CA_FILE	/etc/ssl/certs/cloudsql-server-ca.pem (downloaded per-instance)
GATEKEEPER_BACKEND_TOKEN_CMD	gcloud sql generate-login-token

verify-ca is the cookbook default because Cloud SQL’s server cert SAN is not the instance IP, so verify-full against a raw IP won’t pass hostname verification. verify-full is still achievable when the instance has a DNS name populated in its dnsNames field (PSC instances and instances with the newer cluster-DNS records) — the recipe for that case lives in Gotchas.

For Cloud SQL with password auth, drop the BACKEND_TOKEN_CMD row and supply the password through your client.

Prerequisites

• A running Cloud SQL for PostgreSQL instance with public IP or private IP reachable from the host running Querycop. (If only private IP is exposed, Querycop must run inside the same VPC or have Private Service Connect / VPN reachability.)

• For IAM auth: the instance flag cloudsql.iam_authentication set to on. This is a true DB flag (in contrast to AWS RDS, where the equivalent is an instance setting, not a parameter group):

  gcloud sql instances patch my-instance \
    --database-flags=cloudsql.iam_authentication=on
  # Restart prompt may appear; the flag itself applies on next start.

• A Cloud SQL user created for an IAM principal (user email or service account email):

  # For a human user
  gcloud sql users create [email protected] \
    --instance=my-instance --type=cloud_iam_user
  
  # For a service account (strip the .gserviceaccount.com suffix from
  # the service account email; e.g. [email protected]
  # becomes [email protected])
  gcloud sql users create [email protected] \
    --instance=my-instance --type=cloud_iam_service_account

• The IAM principal Querycop will mint tokens as MUST have the roles/cloudsql.instanceUser role (or equivalent custom role with cloudsql.instances.login) on the project / instance.

• The PostgreSQL-side GRANT for whichever schema / tables the user needs to touch. Cloud SQL gives IAM users no default privileges.

• The host running Querycop has the gcloud CLI installed and an active credential (gcloud auth, Application Default Credentials, or a GKE Workload Identity-bound service account).

• Querycop with backend IAM token injection support (GATEKEEPER_BACKEND_TOKEN_CMD).

Pattern A — Password auth (evaluation / early production)

Cloud SQL supports per-user passwords on top of (or instead of) IAM auth. Querycop forwards the client’s password unchanged.

Step 1: Download the per-instance server CA

Cloud SQL issues a per-instance server certificate signed by a per-instance CA — unlike AWS RDS, there is no global bundle. The CA must be fetched once per instance and re-fetched on rotation.

gcloud sql instances describe my-instance \
  --format='value(serverCaCert.cert)' \
  > /etc/ssl/certs/cloudsql-server-ca.pem

# Sanity-check it parsed
grep -c BEGIN /etc/ssl/certs/cloudsql-server-ca.pem  # should be 1

The CA can also be downloaded from the Cloud Console under Connections → Security → Manage certificates. Cloud SQL rotates the server CA on a published schedule; when it does, re-run the above and restart Querycop (no in-process hot-reload of BACKEND_TLS_CA_FILE).

Step 2: Configure Querycop

# Required: where to find the backend
export GATEKEEPER_BACKEND_HOST=34.123.45.67          # instance public IP
export GATEKEEPER_BACKEND_PORT=5432

# Required: TLS chain validation against the per-instance CA.
# verify-ca (not verify-full) is the default for IP-based connections
# because Cloud SQL's server cert SAN is NOT the IP — verify-full
# against an IP would fail hostname check. To run verify-full, see
# the Gotchas section on the dnsNames-based recipe.
export GATEKEEPER_BACKEND_TLS_MODE=verify-ca
export GATEKEEPER_BACKEND_TLS_CA_FILE=/etc/ssl/certs/cloudsql-server-ca.pem

# Standard Querycop runtime
export GATEKEEPER_LISTEN_PORT=15432
export GATEKEEPER_API_PORT=8080
export ADMIN_API_KEY=$(openssl rand -hex 16)

querycop

Step 3: Connect the client

psql -h 127.0.0.1 -p 15432 -U postgres -d appdb
# Password prompt → enter the password set in Cloud SQL

verify-ca is the cookbook default for direct IP connections — it validates that the server’s cert was issued by the per-instance CA you downloaded, skipping the hostname check that would fail against a raw IP. require (no certificate verification at all) is evaluation-only — reach for it when you’re first spinning up an instance and haven’t yet fetched the per-instance CA, then switch back. disable is not an appropriate choice for a managed cloud DB: it strips the backend TLS leg, which would expose query traffic — and in Pattern B, the IAM token — on the wire. Querycop explicitly rejects disable and prefer when BACKEND_TOKEN_CMD is set (see §1.6).

For verify-full (including the hostname check), see the dnsNames-based recipe in Gotchas.

Pattern B — IAM auth (recommended for production)

Cloud SQL IAM auth substitutes a short-lived OAuth2 access token for the database password. The token is issued by Google’s IAM endpoint via gcloud sql generate-login-token and presented in the PostgreSQL PasswordMessage (cleartext or MD5 — both handled).

Step 1: Confirm the GCP-side IAM auth wiring

# 1. The instance flag is on.
gcloud sql instances describe my-instance \
  --format='value(settings.databaseFlags)' \
  | grep cloudsql.iam_authentication
# Expected: cloudsql.iam_authentication=on

# 2. The Cloud SQL user exists with type cloud_iam_user (or _service_account).
gcloud sql users list --instance=my-instance --filter="type:CLOUD_IAM_*"

# 3. The IAM principal Querycop runs as has cloudsql.instanceUser.
# Test by minting a token from the Querycop host:
gcloud sql generate-login-token | head -c 80
# Expected: an opaque OAuth2 access token (ya29.…)

If generate-login-token fails with “permission denied” / “API not enabled”, fix on the GCP side before configuring Querycop — Querycop will faithfully propagate the mint failure as a connection abort.

Step 2: Configure Querycop

Same BACKEND_HOST / TLS config as Pattern A, plus the token command:

export GATEKEEPER_BACKEND_HOST=34.123.45.67
export GATEKEEPER_BACKEND_PORT=5432

# verify-ca is the production default for IP-based Cloud SQL connections
# (see Pattern A for rationale and the Gotchas section for the
# verify-full + dnsNames recipe).
export GATEKEEPER_BACKEND_TLS_MODE=verify-ca
export GATEKEEPER_BACKEND_TLS_CA_FILE=/etc/ssl/certs/cloudsql-server-ca.pem

# IAM token mint per connection.
export GATEKEEPER_BACKEND_TOKEN_CMD='gcloud sql generate-login-token'

export GATEKEEPER_LISTEN_PORT=15432
export GATEKEEPER_API_PORT=8080
export ADMIN_API_KEY=$(openssl rand -hex 16)

querycop

Querycop enforces at startup that BACKEND_TOKEN_CMD ships with a TLS mode of require / verify-ca / verify-full — prefer is rejected because its plaintext-fallback path would leak the token.

Step 3: Connect the client

The client points at Querycop with the IAM-mapped username; the password field is irrelevant (Querycop discards and replaces).

# For human-user IAM mapping
psql -h 127.0.0.1 -p 15432 -U [email protected] -d appdb

# For service-account IAM mapping — use the email with the
# .gserviceaccount.com suffix stripped, matching what
# `gcloud sql users create … --type=cloud_iam_service_account`
# stored.
psql -h 127.0.0.1 -p 15432 -U [email protected] -d appdb

PGPASSWORD=ignored works for clients that won’t accept an empty password.

Smoke test

# Terminal 1: bring up Querycop with the env above.
docker compose up -d  # or `querycop` directly

# Terminal 2: connect via psql.
psql -h 127.0.0.1 -p 15432 -U [email protected] -d appdb -c 'select 1'
#  ?column?
# ----------
#         1
# (1 row)

A green select 1 means proxy-side TLS, the IAM token mint, the PasswordMessage rewrite, and the backend TLS all worked. If you see something else, the most common first-time-setup surfaces are:

1. backend TLS negotiation failed: x509: certificate signed by unknown authority → the per-instance CA file is missing, stale, or wasn’t picked up. Re-run the gcloud sql instances describe --format='value(serverCaCert.cert)' from Pattern A Step 1, then restart Querycop.
2. backend TLS negotiation failed: x509: certificate is valid for ..., not 34.123.45.67 → you set BACKEND_TLS_MODE=verify-full against the raw IP. Either drop back to verify-ca (cookbook default) or switch to the dnsNames-based recipe documented in Gotchas.
3. backend token command failed: PERMISSION_DENIED → IAM principal missing cloudsql.instanceUser or instance flag not on.
4. password authentication failed for user "[email protected]" → the Cloud SQL user wasn’t created with the right --type=cloud_iam_*, or the IAM principal email doesn’t match.

Gotchas

verify-full requires a DNS name from the instance’s dnsNames field

This is the most common first-setup stumble specific to Cloud SQL, and the reason the cookbook’s default is verify-ca instead.

Cloud SQL’s per-instance server cert does NOT have an IP-address SAN matching the public/private IP you connect to, so a verify-full TLS handshake from BACKEND_HOST=<IP> against the per-instance CA fails the hostname check. The verification name that the cert is actually issued for lives in the instance metadata under dnsNames — Google documents this explicitly.

The catch: dnsNames is only populated for instances that have a Cloud-SQL-managed DNS record. As of 2026-05 that means Private Service Connect (PSC) instances and instances created or re-configured with cluster DNS records enabled. A vanilla public-IP / private-IP instance with no DNS configuration will return an empty dnsNames list — for those, stay on verify-ca.

To check and use dnsNames:

gcloud sql instances describe my-instance \
  --format='value(dnsNames[].name)'
# Sample output (PSC instance):
# abcd1234.us-central1.sql.goog

If you got a non-empty result, switch Querycop to verify-full and point BACKEND_HOST at the DNS name. The TLS leg connects through that DNS name (resolution happens at TCP-connect time), and the hostname-check leg of verify-full matches it against the cert SAN.

export GATEKEEPER_BACKEND_HOST=abcd1234.us-central1.sql.goog
export GATEKEEPER_BACKEND_TLS_MODE=verify-full
export GATEKEEPER_BACKEND_TLS_CA_FILE=/etc/ssl/certs/cloudsql-server-ca.pem
# SERVER_NAME unset — Querycop derives it from BACKEND_HOST, which
# now matches a DNS SAN on the cert.

For non-PSC instances without a DNS name, verify-ca remains the right choice — it validates the cert chain against the per-instance CA you downloaded, just without the hostname check that has no matching SAN to check against. Do NOT try to fake the hostname check by setting BACKEND_TLS_SERVER_NAME to a guessed value — the Go TLS stack will fail-close with a cert mismatch.

IAM token is a 60-min OAuth2 access token

gcloud sql generate-login-token produces a Google OAuth2 access token, which by default has a 60-minute lifetime. Like RDS, the PostgreSQL server only validates the token during the initial auth exchange; a successfully-authed connection stays alive for the TCP socket’s lifetime regardless of the 60-minute mark.

Operationally:

• New connections after token expiry need a fresh mint — fine, that’s per-connection.
• If the IAM principal’s underlying credentials (gcloud auth session, service-account JSON file, Workload Identity token) expire or get revoked, all future mints fail until they refresh. Querycop surfaces this as backend token command failed in the WARN log.

Client→proxy TLS vs proxy→DB TLS are SEPARATE legs

Leg	Configured by	Default
Client → Querycop	GATEKEEPER_PROXY_TLS_CERT / _KEY	OFF — plaintext unless you put TLS material in front
Querycop → Cloud SQL	GATEKEEPER_BACKEND_TLS_* (this page)	prefer (default) — upgrade to verify-ca (cookbook default for direct IP) or verify-full (with the dnsNames recipe)

In this cookbook the proxy→DB leg is verify-ca by default for direct IP connections, and verify-full when the instance has a populated dnsNames entry (see the verify-full Gotcha). Either mode validates the cert chain against the per-instance CA; the difference is whether the hostname check is also enforced. The client→proxy leg is your call — psql over plaintext to a localhost proxy is fine for development; for production put a TLS cert on Querycop (GATEKEEPER_PROXY_TLS_CERT / _KEY).

The OAuth2 token never leaves Querycop, so even if the client→proxy leg is plaintext, the token is only ever in flight on the chain-validated (verify-ca or verify-full) backend leg.

Per-instance CA rotation

Cloud SQL CA rotation is per-instance, not regional or global. The server CA rotation procedure gives you an explicit two-step window:

1. Add the upcoming CA: Cloud SQL marks the new CA “Active” alongside the current “Upcoming” one. Both certificates validate.
2. Rotate: the new CA becomes the only Active one; the old is purged.

Practical operator pattern:

1. When you see the “Upcoming” CA in the console, re-run the gcloud sql instances describe … --format='value(serverCaCert.cert)' command — but request both certs:

   gcloud sql ssl server-ca-certs list --instance=my-instance \
     --format='value(cert)' > /etc/ssl/certs/cloudsql-server-ca.pem

   This emits a concatenated bundle of all active certs (current + upcoming), so verify-ca (and verify-full when you’re on the dnsNames recipe) passes during the entire transition window.
2. Restart Querycop to pick up the updated file (no in-process hot reload).
3. After GCP completes the rotation, run the same command once more to drop the now-purged old cert.

gcloud CLI must be on PATH where Querycop runs

The BACKEND_TOKEN_CMD shells out via sh -c; the spawned shell inherits the parent’s PATH. On a container that doesn’t have gcloud installed, the command fails fast with executable file not found in $PATH.

Two options:

• Install google-cloud-cli (or the slim google-cloud-cli-cli package) in the Querycop runtime container.
• Wrap an explicit absolute path: BACKEND_TOKEN_CMD=/usr/lib/google-cloud-sdk/bin/gcloud sql generate-login-token.

Workload Identity / ADC env passthrough

Whatever gives the Querycop host its GCP credentials (gcloud auth application-default login, a GOOGLE_APPLICATION_CREDENTIALS= JSON file, GKE Workload Identity, Compute Engine instance metadata), it MUST be visible to the shell that runs BACKEND_TOKEN_CMD.

Inheriting os.Environ() means GOOGLE_APPLICATION_CREDENTIALS / CLOUDSDK_CONFIG / CLOUDSDK_AUTH_ACCESS_TOKEN_FILE all flow into the child — but the operator still has to set them on the parent (container spec, systemd unit, etc.).

For GKE + Workload Identity, the standard pattern is: bind a Kubernetes service account to a GCP service account that holds cloudsql.instanceUser, deploy Querycop with that KSA, and gcloud picks up the federated credentials automatically — no extra env wiring.

An alternative: Cloud SQL Auth Proxy as a sidecar

If you’d rather not configure Querycop with the cert-SAN override and IAM token command, you can deploy the Cloud SQL Auth Proxy (docs) as a sidecar that handles TLS + IAM upstream of Querycop:

client ──TLS or plaintext──> Querycop ──plaintext, localhost──> Cloud SQL Auth Proxy ──TLS+IAM──> Cloud SQL

Configure Querycop with:

export GATEKEEPER_BACKEND_HOST=127.0.0.1
export GATEKEEPER_BACKEND_PORT=5432
export GATEKEEPER_BACKEND_TLS_MODE=disable    # OK here: backend is a localhost sidecar

This deployment style trades two long-running processes per pod for a simpler Querycop config. It is not the cookbook’s recommended default because:

• It hides the TLS / IAM behavior from Querycop’s audit log (Querycop sees only the localhost hop).
• disable mode on the backend is only safe because the sidecar is on the loopback interface — a misdeployment that puts the Auth Proxy on a different host silently regresses to plaintext over the network.

For a production deployment that wants the Auth Proxy model, run it as a sidecar in the same pod (Kubernetes) or same systemd unit group (VM) so the loopback assumption is enforceable.

Cross-links

• docs/configuration.md §1.5 — backend TLS reference
• docs/configuration.md §1.6 — BACKEND_TOKEN_CMD reference
• GCP docs: Cloud SQL IAM database authentication
• GCP docs: Cloud SQL server CA rotation
• GCP docs: Cloud SQL Auth Proxy