Skip to content
Querycop Docs

Google AlloyDB for PostgreSQL Community

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

Google AlloyDB is GCP’s PostgreSQL-compatible managed database. Operationally it differs from Cloud SQL for PostgreSQL in two ways that matter to a Querycop deployment:

  1. Private-IP by default. AlloyDB clusters expose connectivity through a VPC-private IP; public-IP is an explicit opt-in per instance. The Querycop host must therefore live in (or be peered/PSC-connected to) the AlloyDB cluster’s VPC.
  2. Cluster topology. A cluster has a primary instance, zero-or-more read-pool instances, and optionally a secondary cluster in another region. Each instance has its own endpoint; you point Querycop at the instance you want.

This page covers direct TLS + IAM auth against an AlloyDB instance. The AlloyDB Auth Proxy sidecar deployment is summarized in Gotchas as a complementary style; the cookbook recipe uses direct connections because they exercise Querycop’s own backend TLS / IAM-token paths and avoid a second long-running process per pod.

TL;DR

Section titled “TL;DR”

For an AlloyDB primary instance with IAM auth on a private IP, the production-ready config is:

Env varValue
GATEKEEPER_BACKEND_HOST10.20.30.40 (instance private IP)
GATEKEEPER_BACKEND_PORT5432
GATEKEEPER_BACKEND_TLS_MODEverify-ca (cookbook default; see Gotchas for the verify-full recipe)
GATEKEEPER_BACKEND_TLS_CA_FILE/etc/ssl/certs/alloydb-cluster-ca.pem (downloaded per-cluster)
GATEKEEPER_BACKEND_TOKEN_CMDgcloud auth print-access-token

For AlloyDB with password auth, drop the BACKEND_TOKEN_CMD row and supply the password through your client. verify-ca is the default for the same reason as Cloud SQL: the per-cluster server cert’s SAN does not match a raw IP, so verify-full against an IP fails the hostname check. verify-full is achievable when you have a DNS name that resolves to the AlloyDB endpoint AND the cert SAN includes that name (Service Directory, Private DNS zones, or a managed PSC DNS) — see Gotchas.

Prerequisites

Section titled “Prerequisites”

  • An AlloyDB cluster with at least a primary instance. The cluster ID, region, and project ID are visible in the Cloud Console under Databases → AlloyDB → Clusters.

  • The instance’s private IP (or public IP, if you’ve opted in). Available via gcloud alloydb instances describe INSTANCE_ID --cluster=CLUSTER_ID --region=REGION --format='value(ipAddress)'.

  • The Querycop host has VPC reachability to the instance — either living inside the cluster’s VPC, peered, or attached via PSC.

  • For IAM auth: the alloydb.iam_authentication=on database flag set on every instance that will accept IAM-authenticated connections (primary, read pool, and any instance in a secondary cluster). The flag is an instance-level setting, not a cluster-level setting — set it once per instance:

    Terminal window
    # Primary instance
    gcloud alloydb instances update my-primary \
      --cluster=my-cluster --region=us-central1 \
      --database-flags=alloydb.iam_authentication=on
    

    # Each read-pool instance, repeated
    gcloud alloydb instances update my-readpool-1 \
      --cluster=my-cluster --region=us-central1 \
      --database-flags=alloydb.iam_authentication=on

    Setting the flag may trigger a restart of the instance.

  • An AlloyDB user created for the IAM principal:

    Terminal window
    gcloud alloydb users create alice@example.com \
      --cluster=my-cluster --region=us-central1 \
      --type=IAM_BASED
    

    # For a service account: use the email with .gserviceaccount.com
    # stripped (same rule as Cloud SQL).
    gcloud alloydb users create my-sa@my-project.iam \
      --cluster=my-cluster --region=us-central1 \
      --type=IAM_BASED

  • The IAM principal Querycop runs as has both roles on the project / cluster:

    • roles/alloydb.databaseUser (or equivalent custom role granting alloydb.users.dbConnect / alloydb.instances.connect) — required to authenticate to the database
    • roles/serviceusage.serviceUsageConsumer — required so the principal can mint the OAuth2 access token against the AlloyDB service; without this the token mint succeeds but the AlloyDB server rejects the auth with permission denied

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

  • gcloud CLI installed on the Querycop host with an active credential (gcloud auth, Application Default Credentials, GKE Workload Identity, or Compute Engine instance metadata).

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

Pattern A — Password auth (evaluation / early production)

Section titled “Pattern A — Password auth (evaluation / early production)”

AlloyDB supports per-user passwords created via gcloud alloydb users create … --type=BUILT_IN --password=…. Querycop forwards the client’s password unchanged.

Step 1: Download the per-cluster server CA

Section titled “Step 1: Download the per-cluster server CA”

AlloyDB issues a per-cluster server CA. The reliably scriptable retrieval paths are:

Option A — Cloud Console (recommended for first setup). Open Databases → AlloyDB → Clusters → <your cluster> → Connectivity and use the Download server certificate authority action. Save the PEM to a path the Querycop process can read:

Terminal window
# After the console download lands the file in your local Downloads:
mv ~/Downloads/server-ca.pem /etc/ssl/certs/alloydb-cluster-ca.pem
grep -c BEGIN /etc/ssl/certs/alloydb-cluster-ca.pem  # should be ≥ 1

This is the path documented under Connect using IAM account and is the most stable retrieval method — it does not depend on gcloud SDK version or component installation.

Option B — REST API + curl (for automation). The AlloyDB API method projects.locations.clusters.generateClientCertificate returns the CA cert in its caCert field (GenerateClientCertificateResponse). This is suitable for unattended CA fetches in CI / image-bake pipelines:

Terminal window
PROJECT=my-project
REGION=us-central1
CLUSTER=my-cluster
TOKEN=$(gcloud auth print-access-token)


curl -fsS -X POST \
  -H "Authorization: Bearer ${TOKEN}" \
  -H "Content-Type: application/json" \
  -d '{"certDuration": "3600s"}' \
  "https://alloydb.googleapis.com/v1/projects/${PROJECT}/locations/${REGION}/clusters/${CLUSTER}:generateClientCertificate" \
  | python3 -c 'import json,sys; print(json.load(sys.stdin)["caCert"])' \
  > /etc/ssl/certs/alloydb-cluster-ca.pem


grep -c BEGIN /etc/ssl/certs/alloydb-cluster-ca.pem  # should be ≥ 1

The response also includes pemCertificateChain (the client cert, short-lived, used for client-cert-based connections). The cookbook only needs caCert for password and IAM auth, so we extract that field and discard the rest.

A note on gcloud alloydb clusters generate-client-certificate. Earlier drafts of this page recommended that command, but it is not available in current stable gcloud releases (it lives under gcloud alpha alloydb / gcloud beta alloydb depending on the SDK version, and the surface has shifted between releases). For reproducible copy-paste recipes use Option A (Console) or Option B (REST + curl) above.

No in-process hot reload of BACKEND_TLS_CA_FILE — restart Querycop after replacing the file.

Step 2: Configure Querycop

Section titled “Step 2: Configure Querycop”
Terminal window
# Required: where to find the backend
export GATEKEEPER_BACKEND_HOST=10.20.30.40    # instance private IP
export GATEKEEPER_BACKEND_PORT=5432


# Required: TLS chain validation against the per-cluster CA.
# verify-ca (not verify-full) is the cookbook default because
# AlloyDB's per-cluster cert SAN doesn't match a raw IP. See
# Gotchas for the verify-full recipe via a matching DNS name.
export GATEKEEPER_BACKEND_TLS_MODE=verify-ca
export GATEKEEPER_BACKEND_TLS_CA_FILE=/etc/ssl/certs/alloydb-cluster-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

Section titled “Step 3: Connect the client”
Terminal window
psql -h 127.0.0.1 -p 15432 -U postgres -d appdb
# Password prompt → enter the AlloyDB password

verify-ca validates the cert chain against the per-cluster CA but skips the hostname check (which would fail against a raw IP). require (no certificate verification at all) is evaluation-only — useful when standing up against a brand-new cluster and you haven’t yet fetched the CA. disable is not appropriate for a managed cloud DB: it strips the backend TLS leg entirely, and in Pattern B it would expose the IAM token. Querycop rejects disable and prefer when BACKEND_TOKEN_CMD is set (see §1.6).

Pattern B — IAM auth (recommended for production)

Section titled “Pattern B — IAM auth (recommended for production)”

AlloyDB IAM auth substitutes a Google OAuth2 access token for the database password. The token is presented in the PostgreSQL PasswordMessage (cleartext or MD5 — both handled).

Step 1: Confirm the GCP-side IAM auth wiring

Section titled “Step 1: Confirm the GCP-side IAM auth wiring”
Terminal window
# 1. The cluster has IAM auth enabled.
gcloud alloydb clusters describe my-cluster \
  --region=us-central1 \
  --format='value(databaseFlags)' \
  | grep alloydb.iam_authentication
# Expected: alloydb.iam_authentication=on


# 2. The AlloyDB user exists as IAM_BASED.
gcloud alloydb users list --cluster=my-cluster --region=us-central1 \
  --filter='userType=IAM_BASED'


# 3. The IAM principal Querycop runs as can mint a token.
gcloud auth print-access-token | head -c 80
# Expected: an opaque OAuth2 access token (ya29.…)

If print-access-token fails with “credentials not found”, set up ADC (gcloud auth application-default login) or attach an appropriate service account / Workload Identity to the Querycop host.

Step 2: Configure Querycop

Section titled “Step 2: Configure Querycop”
Terminal window
export GATEKEEPER_BACKEND_HOST=10.20.30.40
export GATEKEEPER_BACKEND_PORT=5432


# verify-ca is the cookbook default for direct AlloyDB IP connections
# (see Pattern A for rationale and Gotchas for the verify-full recipe).
export GATEKEEPER_BACKEND_TLS_MODE=verify-ca
export GATEKEEPER_BACKEND_TLS_CA_FILE=/etc/ssl/certs/alloydb-cluster-ca.pem


# IAM token mint per connection: the OAuth2 access token IS the PG
# password for an IAM_BASED AlloyDB user.
export GATEKEEPER_BACKEND_TOKEN_CMD='gcloud auth print-access-token'


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


querycop

Step 3: Connect the client

Section titled “Step 3: Connect the client”
Terminal window
# For human-user IAM mapping
psql -h 127.0.0.1 -p 15432 -U alice@example.com -d appdb


# For service-account IAM mapping — strip the .gserviceaccount.com
# suffix from the SA email, matching what `gcloud alloydb users
# create … --type=IAM_BASED` stored.
psql -h 127.0.0.1 -p 15432 -U my-sa@my-project.iam -d appdb

PGPASSWORD=ignored works for clients that won’t accept an empty password (Querycop discards and replaces with the minted token).

Read-pool / secondary cluster routing

Section titled “Read-pool / secondary cluster routing”

AlloyDB exposes:

Instance typeEndpointUse for
Primarythe primary instance’s IPReads + writes
Read pooleach pool instance’s IPReads only — eventually consistent
Secondary clustera separate cluster ID in another regionCross-region read / DR

Querycop today runs one process against one endpoint. SQL-aware read/write splitting is deferred to a future proxy-topology epic; until then, the operational pattern is to run two (or more) Querycop processes, one per endpoint, and let the app direct its connection pools accordingly. The cookbook’s Aurora multi-instance pattern documents the same operational shape.

GATEKEEPER_BACKEND_ROLE=primary|replica is an advisory env that gets logged to audit / dashboard, useful for telling apart which Querycop you’re reading logs from. It doesn’t affect behavior.

Smoke test

Section titled “Smoke test”
Terminal window
# 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 alice@example.com -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 dial failed: i/o timeout → the Querycop host can’t reach the AlloyDB private IP. Check VPC peering / PSC / firewall rules; AlloyDB doesn’t expose public IP by default.
  2. backend TLS negotiation failed: x509: certificate signed by unknown authority → the per-cluster CA file is missing, stale, or wasn’t picked up. Re-download the CA via either the Console path or the REST + curl path documented in Pattern A Step 1.
  3. backend TLS negotiation failed: x509: certificate is valid for ..., not 10.20.30.40 → you set BACKEND_TLS_MODE=verify-full against the raw IP. Either drop to verify-ca (cookbook default) or switch to a matching DNS name as documented in Gotchas.
  4. backend token command failed: PERMISSION_DENIED → either the IAM principal lacks roles/alloydb.databaseUser / roles/serviceusage.serviceUsageConsumer, or the alloydb.iam_authentication=on flag isn’t set on the instance you’re connecting to (the flag is per-instance, not per-cluster).
  5. password authentication failed for user "alice@example.com" → the AlloyDB user wasn’t created with --type=IAM_BASED, or the IAM principal email doesn’t match the user.

Gotchas

Section titled “Gotchas”

verify-full requires a name that matches the cluster’s cert SAN

Section titled “verify-full requires a name that matches the cluster’s cert SAN”

Same shape as the Cloud SQL gotcha: AlloyDB’s per-cluster server cert is NOT issued with an IP-address SAN matching the instance’s private/public IP. verify-full against a raw IP fails the hostname check.

If your environment exposes a DNS name that resolves to the AlloyDB instance — e.g. via Service Directory, a Private DNS zone you manage, or a managed PSC DNS record — and the cluster’s cert SAN includes that name, you can run verify-full:

Terminal window
# Check what SANs the cert was issued with
openssl s_client -showcerts -connect 10.20.30.40:5432 -starttls postgres \
  </dev/null 2>/dev/null \
  | openssl x509 -noout -ext subjectAltName


# If the output includes your DNS name, configure Querycop:
export GATEKEEPER_BACKEND_HOST=alloydb-primary.internal.example.com
export GATEKEEPER_BACKEND_TLS_MODE=verify-full
export GATEKEEPER_BACKEND_TLS_CA_FILE=/etc/ssl/certs/alloydb-cluster-ca.pem
# SERVER_NAME unset — Querycop derives it from BACKEND_HOST.

If your DNS name does NOT appear in the cert’s SAN list (most common when you manage your own private DNS pointing at the AlloyDB IP), stay on verify-ca. Do NOT try to fake the hostname check by setting BACKEND_TLS_SERVER_NAME to a guessed value — Go’s TLS stack fail-closes on cert mismatch.

OAuth2 access token is a 60-min credential

Section titled “OAuth2 access token is a 60-min credential”

gcloud auth print-access-token returns an OAuth2 access token with a 60-minute default lifetime. AlloyDB validates the token during the initial auth exchange and does not re-check it during steady-state traffic, so a successfully-authed connection stays alive for the TCP socket’s lifetime regardless of the 60-minute mark — same shape as the RDS / Cloud SQL stories.

  • New connections after token expiry mint fresh; this is automatic because mint is per-connection.
  • If the IAM principal’s underlying credentials expire or get revoked (gcloud session timeout, service-account key rotation, Workload Identity binding removed), all future mints fail with backend token command failed in the WARN log.

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

Section titled “Client→proxy TLS vs proxy→AlloyDB TLS are SEPARATE legs”
LegConfigured byDefault
Client → QuerycopGATEKEEPER_PROXY_TLS_CERT / _KEYOFF — plaintext unless you put TLS material in front
Querycop → AlloyDBGATEKEEPER_BACKEND_TLS_* (this page)prefer (default) — upgrade to verify-ca (cookbook default for direct IP) or verify-full (with the matching-DNS-name recipe)

In this cookbook the proxy→DB leg is verify-ca for direct IP connections, and verify-full when you have a matching DNS name. Either mode validates the cert chain against the per-cluster CA; the difference is whether the hostname check is also enforced. The client→proxy leg is your call.

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-cluster CA rotation

Section titled “Per-cluster CA rotation”

AlloyDB CA rotation is per-cluster, on the schedule documented under AlloyDB SSL/TLS certificate management. When a rotation is upcoming:

  1. Re-fetch the CA bundle via either of the Pattern A Step 1 paths:
    • Console: Cluster → Connectivity → Download server certificate authority, then mv the file to /etc/ssl/certs/alloydb-cluster-ca.pem.
    • REST + curl (for automation):
      Terminal window
      PROJECT=my-project; REGION=us-central1; CLUSTER=my-cluster
      TOKEN=$(gcloud auth print-access-token)
      curl -fsS -X POST \
        -H "Authorization: Bearer ${TOKEN}" \
        -H "Content-Type: application/json" \
        -d '{"certDuration": "3600s"}' \
        "https://alloydb.googleapis.com/v1/projects/${PROJECT}/locations/${REGION}/clusters/${CLUSTER}:generateClientCertificate" \
        | python3 -c 'import json,sys; print(json.load(sys.stdin)["caCert"])' \
        > /etc/ssl/certs/alloydb-cluster-ca.pem
  2. Restart Querycop (no in-process hot-reload).

gcloud CLI must be on PATH where Querycop runs

Section titled “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 ship gcloud, the command fails fast with executable file not found in $PATH.

Either install google-cloud-cli in the Querycop runtime container, or pin an absolute path: BACKEND_TOKEN_CMD=/usr/lib/google-cloud-sdk/bin/gcloud auth print-access-token.

Workload Identity / ADC env passthrough

Section titled “Workload Identity / ADC env passthrough”

The os.Environ() inheritance means GOOGLE_APPLICATION_CREDENTIALS / CLOUDSDK_CONFIG / CLOUDSDK_AUTH_ACCESS_TOKEN_FILE and the standard Workload Identity env all flow into the child shell — provided you set them on the parent (container spec, systemd unit, etc.).

For GKE + Workload Identity, bind a Kubernetes service account to a GCP service account that holds roles/alloydb.databaseUser, deploy Querycop with that KSA, and gcloud auth print-access-token picks up the federated credentials automatically — no extra env wiring.

An alternative: the AlloyDB Auth Proxy as a sidecar

Section titled “An alternative: the AlloyDB Auth Proxy as a sidecar”

If you’d rather not configure Querycop with the per-cluster CA and IAM token command, the AlloyDB Auth Proxy can be deployed as a sidecar that handles TLS + IAM upstream of Querycop:

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

Querycop config in this layout:

Terminal window
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

Same trade-offs as the Cloud SQL Auth Proxy sidecar pattern:

  • The TLS / IAM behavior is hidden 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 — misdeploying the proxy to 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 Kubernetes pod (or the same systemd unit group on a VM) so the loopback assumption is enforceable.

Section titled “Cross-links”