AWS RDS / Aurora PostgreSQL Community

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

This page covers running Querycop in front of:

• AWS RDS for PostgreSQL (single-instance)
• AWS Aurora PostgreSQL (writer + reader endpoints)

Both share the same CA bundle, the same IAM-token tooling, and the same verify-full recipe — only the endpoint shape and (for Aurora) the writer / reader topology differ.

TL;DR

For Aurora cluster writer endpoint with IAM auth, the production-ready config is:

Env var	Value
GATEKEEPER_BACKEND_HOST	mydb.cluster-xxxxxxxx.us-east-1.rds.amazonaws.com
GATEKEEPER_BACKEND_PORT	5432
GATEKEEPER_BACKEND_TLS_MODE	verify-full
GATEKEEPER_BACKEND_TLS_CA_FILE	/etc/ssl/certs/rds-global-bundle.pem (downloaded from AWS)
GATEKEEPER_BACKEND_TLS_SERVER_NAME	(unset — derived from host)
GATEKEEPER_BACKEND_TOKEN_CMD	aws rds generate-db-auth-token --hostname "$QUERYCOP_BACKEND_HOST" --port 5432 --region us-east-1 --username "$QUERYCOP_BACKEND_USER"

For RDS PostgreSQL with password auth (no IAM), drop the BACKEND_TOKEN_CMD row and supply the password through your client.

The rest of this page expands what each row does, where the CA bundle comes from, and the per-auth-pattern step-by-step.

Prerequisites

• A running RDS PostgreSQL instance OR Aurora PostgreSQL cluster.

• For IAM auth: IAM database authentication enabled on the DB instance (RDS) or the DB cluster (Aurora). This is an instance/cluster-level setting — not a DB parameter group entry — and applies dynamically (no reboot required):

  # RDS for PostgreSQL (single instance)
  aws rds modify-db-instance \
    --db-instance-identifier mydb \
    --enable-iam-database-authentication \
    --apply-immediately
  
  # Aurora PostgreSQL (cluster)
  aws rds modify-db-cluster \
    --db-cluster-identifier mydb \
    --enable-iam-database-authentication \
    --apply-immediately

  Confirm it took:

  aws rds describe-db-instances --db-instance-identifier mydb \
    --query 'DBInstances[0].IAMDatabaseAuthenticationEnabled'
  # or for Aurora:
  aws rds describe-db-clusters --db-cluster-identifier mydb \
    --query 'DBClusters[0].IAMDatabaseAuthenticationEnabled'
  # Expected: true

• A PostgreSQL role created for IAM auth, e.g.:

  CREATE USER iam_user WITH LOGIN;
  GRANT rds_iam TO iam_user;
  GRANT CONNECT ON DATABASE appdb TO iam_user;

• An AWS IAM principal (user or role) that holds rds-db:connect for arn:aws:rds-db:<region>:<account>:dbuser:<resource-id>/iam_user.

• The host running Querycop has the aws CLI installed and credentials available (env vars / instance profile / IRSA / Workload Identity — whatever your stack uses). The CLI must be able to call aws rds generate-db-auth-token from that host.

• Network reachability from the Querycop host to port 5432 on the RDS endpoint. RDS Proxy / VPC peering / Transit Gateway etc. all work as long as TCP gets through.

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

Pattern A — Password auth (evaluation / early production)

The simplest path. Querycop forwards the client’s password unchanged; you put a normal PostgreSQL password on the DB user and on the client connection string.

Step 1: Download the AWS root CA bundle

AWS publishes a global bundle that chains every RDS region’s root CA. Pin it from the official URL — don’t bundle the file inside Querycop (CA rotation is AWS’s problem; Querycop just reads what you place).

curl -fsSL https://truststore.pki.rds.amazonaws.com/global/global-bundle.pem \
  -o /etc/ssl/certs/rds-global-bundle.pem

# Verify the file looks sane (a non-zero PEM with at least one CERTIFICATE)
grep -c BEGIN /etc/ssl/certs/rds-global-bundle.pem  # should be ≥ 1

AWS rotates the root certificates on a published schedule; when they do, re-download and restart Querycop. There’s no in-process hot-reload of the CA.

Step 2: Configure Querycop

# Required: where to find the backend
export GATEKEEPER_BACKEND_HOST=mydb.cxxxxxxxxxxxxxxxx.us-east-1.rds.amazonaws.com
export GATEKEEPER_BACKEND_PORT=5432

# Required: full TLS verification against the AWS-published CA
export GATEKEEPER_BACKEND_TLS_MODE=verify-full
export GATEKEEPER_BACKEND_TLS_CA_FILE=/etc/ssl/certs/rds-global-bundle.pem
# SERVER_NAME not set — Querycop derives it from BACKEND_HOST,
# which matches the SAN AWS issues for the endpoint.

# 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 RDS

verify-full is the right default for production. require (no certificate verification) is evaluation-only — reach for it when you’re standing up a brand-new instance and haven’t yet downloaded the CA bundle, then switch back to verify-full once the bundle is in place. disable is not an appropriate choice for a managed cloud DB regardless of the engine’s defaults: it strips the backend TLS leg entirely, which would expose query traffic — and, in Pattern B, the IAM token — on the wire between Querycop and the DB. Querycop explicitly rejects disable and prefer when BACKEND_TOKEN_CMD is set for exactly this reason (see §1.6).

Pattern B — IAM auth (recommended for production)

IAM auth eliminates static database passwords. Querycop mints a 15-minute token per connection via aws rds generate-db-auth-token and substitutes it into the PostgreSQL PasswordMessage (cleartext or MD5 — both handled). Client-side the auth flow looks the same as password auth; the password the client sends is discarded and replaced.

Step 1: Confirm the AWS-side IAM auth wiring

# 1. The DB cluster / instance parameter has IAM auth enabled.
aws rds describe-db-instances --db-instance-identifier mydb \
  --query 'DBInstances[0].IAMDatabaseAuthenticationEnabled'
# Expected: true

# 2. The PostgreSQL role exists and has rds_iam granted.
psql -h <direct connection> -U <admin> -d appdb -c '\du iam_user'
# Expected: Member of {rds_iam}

# 3. The IAM principal Querycop runs as has rds-db:connect for this user.
# Test by trying to mint a token from the Querycop host:
aws rds generate-db-auth-token \
  --hostname mydb.cluster-xxxxxxxx.us-east-1.rds.amazonaws.com \
  --port 5432 --region us-east-1 --username iam_user \
  | head -c 80
# Expected: an opaque pre-signed-URL-like string (~800 chars)

If any of those don’t produce the expected output, fix on the AWS 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=mydb.cluster-xxxxxxxx.us-east-1.rds.amazonaws.com
export GATEKEEPER_BACKEND_PORT=5432

export GATEKEEPER_BACKEND_TLS_MODE=verify-full
export GATEKEEPER_BACKEND_TLS_CA_FILE=/etc/ssl/certs/rds-global-bundle.pem

# IAM token mint per connection. Querycop sets
# QUERYCOP_BACKEND_USER and QUERYCOP_BACKEND_HOST in the child env,
# so the command picks them up by name — no Querycop-side shell
# interpolation.
export GATEKEEPER_BACKEND_TOKEN_CMD='aws rds generate-db-auth-token \
    --hostname "$QUERYCOP_BACKEND_HOST" \
    --port 5432 \
    --region us-east-1 \
    --username "$QUERYCOP_BACKEND_USER"'

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. See docs/configuration.md §1.6.

Step 3: Connect the client

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

psql -h 127.0.0.1 -p 15432 -U iam_user -d appdb
# Password prompt: just press Enter (Querycop replaces it with the IAM token)

For tools that won’t allow an empty password, set the env to anything:

PGPASSWORD=ignored psql -h 127.0.0.1 -p 15432 -U iam_user -d appdb

The connection itself uses the same verify-full TLS to the backend, so the token never traverses an unencrypted channel.

Aurora-specific endpoint considerations

Aurora exposes three logical endpoints for a cluster:

Endpoint	Hostname shape	Use for
Writer (cluster endpoint)	mydb.cluster-xxxxxxxx.<region>.rds.amazonaws.com	All writes + reads from the primary
Reader (cluster reader endpoint)	mydb.cluster-ro-xxxxxxxx.<region>.rds.amazonaws.com	Reads from any healthy reader, automatic load-balance
Instance	mydb-instance-1.xxxxxxxx.<region>.rds.amazonaws.com	Pinning to a specific instance

Querycop today runs one instance against one endpoint. SQL-aware read/write splitting is deferred to a future proxy-topology epic. For Aurora deployments that need both:

• Run two Querycop processes — one with BACKEND_HOST = writer endpoint, one with the reader endpoint.
• Point write-intent clients (your app’s primary connection pool) at the writer-side Querycop; read-intent clients at the reader-side.
• The IAM-auth setup is identical on both — the AWS IAM permission rds-db:connect is per-user, not per-endpoint.

Set GATEKEEPER_BACKEND_ROLE=primary or =replica (advisory env) on the respective instances so audit / dashboard logs record which path is being proxied. Today this env is logged-only; it doesn’t change behavior.

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 iam_user -d appdb -c 'select 1'
#  ?column?
# ----------
#         1
# (1 row)

If you see select 1 return 1, the proxy-side TLS, the IAM mint, the PasswordMessage rewrite, and the backend TLS all worked. If you see something else, check (in order):

1. Querycop’s stderr for backend token command failed / backend TLS negotiation failed — those are the two most common first-time-setup surfaces.
2. AWS-side IAM permission via the aws rds generate-db-auth-token manual run from the same host.
3. Whether verify-full is failing on hostname mismatch — see Gotchas.

Gotchas

IAM token lifetime is 15 minutes

aws rds generate-db-auth-token produces a token that expires 15 minutes after issuance, and the validity window is enforced by the RDS server, not the client.

In Querycop: a token is minted once per connection, on the initial handshake. The 15-minute lifetime applies to the mint → handshake window only — once the RDS server has accepted the token during the auth exchange, it does not re-check it for the rest of the connection. A successfully-authed connection stays alive for as long as the underlying TCP socket lives (hours, days), and does NOT get torn down at the 15-minute mark.

What this DOES affect:

• New connections after 15 minutes of an idle Querycop need a fresh mint — fine, that’s automatic because mint is per-connection.
• A connection that drops and re-establishes needs a new mint; the client retry path observes that as “auth-related disconnect” and should reconnect with the same connection string.
• If your IAM principal’s credentials expire (e.g. expiring STS session), all future mints fail until the underlying credentials refresh — Querycop surfaces this as backend token command failed in the WARN log.

In-connection token rotation isn’t implemented; operators relying on multi-hour connections should expect occasional reconnects.

verify-full requires hostname match

The certificate AWS issues for the RDS endpoint has a SAN matching the public DNS name of the endpoint — i.e. the *.rds.amazonaws.com form. verify-full honors that match.

If you’re connecting via a CNAME (e.g. db.internal.example.com pointing at the RDS endpoint), set GATEKEEPER_BACKEND_TLS_SERVER_NAME to the RDS endpoint hostname explicitly so verification checks against the cert SAN, not your CNAME:

export GATEKEEPER_BACKEND_HOST=db.internal.example.com
export GATEKEEPER_BACKEND_TLS_SERVER_NAME=mydb.cluster-xxxxxxxx.us-east-1.rds.amazonaws.com

If you point BACKEND_HOST at an IP literal (rare but it happens), verify-full fail-fasts at startup — set BACKEND_TLS_SERVER_NAME to the DNS name or drop to verify-ca if hostname verification is intentionally skipped.

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

This is the most common conceptual stumble. Querycop has two independent TLS surfaces:

Leg	Configured by	Default
Client → Querycop	GATEKEEPER_PROXY_TLS_CERT / GATEKEEPER_PROXY_TLS_KEY	OFF — connections are plaintext unless you put TLS material in front
Querycop → RDS	GATEKEEPER_BACKEND_TLS_* (this page)	prefer (default) — auto-upgraded to verify-full per recipe

In this cookbook we make the proxy→DB leg verify-full. The client→proxy leg is your call — psql over plaintext to a localhost proxy is fine for development; for production you put a TLS cert on Querycop (GATEKEEPER_PROXY_TLS_CERT / _KEY) so the leg from app to proxy is also encrypted.

The token never leaves Querycop, so even if the client→proxy leg is plaintext, the IAM token is only ever in flight on the verify-full-protected backend leg.

AWS CA rotation

AWS rotates the RDS root CAs on a published schedule. When they do:

1. Download the new global-bundle.pem to the same path
2. Restart Querycop (no in-process hot-reload of BACKEND_TLS_CA_FILE)

global-bundle.pem includes both the outgoing and incoming roots during the transition window, so re-downloading well in advance and restarting on your own schedule avoids the cliff edge.

aws 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 minimal container that doesn’t have aws installed, the command fails fast with executable file not found in $PATH.

Two options:

• Install aws (or aws-cli/v2) in the Querycop runtime container.
• Wrap an explicit absolute path in the command: BACKEND_TOKEN_CMD=/usr/local/bin/aws rds generate-db-auth-token ….

IRSA / Workload Identity / instance profile

Whatever mechanism gives the Querycop host its AWS credentials (env vars, instance profile, IRSA-based projected token, Workload Identity), it MUST be visible to the shell that runs BACKEND_TOKEN_CMD. Inheriting os.Environ() means AWS_PROFILE / AWS_DEFAULT_REGION / AWS_ROLE_ARN / AWS_WEB_IDENTITY_TOKEN_FILE all flow into the child — but the operator still has to set them in the parent, e.g. via the container spec or systemd unit.

For EKS + IRSA the recommended pattern is to let the service account inject the credentials via env; Querycop’s child inherits them through os.Environ() and aws picks them up.

Cross-links

• docs/configuration.md §1.5 — backend TLS reference
• docs/configuration.md §1.6 — BACKEND_TOKEN_CMD reference
• AWS docs: RDS IAM authentication
• AWS docs: RDS root CA bundles
• AWS docs: Aurora endpoints