aurora-postgresql-integration

Files (2)

• SKILL.md
• SKILL.md.lock

SKILL.md (first lines)

<!-- bodyRef: skills/sources/aurora-postgresql-integration.md -->

<!-- Generated by `harness skills bootstrap`. Edit to tune; re-run to regenerate. -->
<!-- Static skills point at live docs by design — do not hardwire version-pinned API here. -->

# Aurora PostgreSQL — correct & secure integration

Guidance for wiring Amazon Aurora PostgreSQL into this stack (TypeScript/Python, Next.js, LangGraph workers running in GitHub Actions, dashboard on Vercel). Aurora is the provenance/ledger + pgvector store. This skill is **concept-first and doc-pointer style** — AWS rotates cert bundles, engine versions, and limits, so confirm specifics against the live docs below before you hardcode anything.

## Pull current docs first

Fetch these CURRENT pages before implementing (they drift):

- IAM database authentication — https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/UsingWithRDS.IAMDBAuth.html
- SSL/TLS to encrypt a connection (CA bundles) — https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/UsingWithRDS.SSL.html
- Password management with Secrets Manager — https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/rds-secrets-manager.html
- RDS Proxy for Aurora — https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/rds-proxy.html
- Aurora endpoints & failover — https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/Aurora.Overview.Endpoints.html
- RDS Data API — https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/data-api.html
- pgvector / vector store on Aurora PostgreSQL — https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/AuroraPostgreSQL.VectorDB.html

Current CA bundle download (trust store): `https://truststore.pki.rds.amazonaws.com/global/global-bundle.pem` (use the regional bundle when you can pin a region).

## Core concepts (timeless)

- **Aurora is a cluster, not a host.** You connect to an *endpoint*, never an instance IP. Use the **cluster (writer) endpoint** for read/write and the **reader endpoint** for read-only; these reroute automatically on failover. Instance endpoints do not — never hardcode them in app config.
- **Three ways in, pick deliberately:**
  1. **Pooled SQL connection** (psycopg/asyncpg/SQLAlchemy in Python, `pg`/`postgres.js` in Node) — lowest latency, full transactions, needs VPC reachability + a pooler.
  2. **RDS Proxy** in front of the cluster — connection pooling, IAM auth, transparent failover; recommended for many short-lived connections (Lambda, fan-out workers).
  3. **RDS Data API** — HTTPS, no persistent connection, IAM-authorized, creds via Secrets Manager; ideal when the caller (a CI job, a Vercel route) is *outside* the VPC. Tradeoffs: per-call latency, result-size limits, transactions only via an explicit `transactionId`.
- **Credentials should never live in code.** Prefer (a) IAM database auth (short-lived tokens) or (b) Secrets Manager managed master password with rotation. Static passwords in env vars are a dev-only fallback.
- **TLS is mandatory in production.** Verify the server cert against the RDS CA bundle (`sslmode=verify-full`), don't just `require`.
- **Idempotency is the app's job**, enforced at the DB with unique constraints + `INSERT ... ON CONFLICT`, advisory locks, or idempotency keys — because retries and failovers *will* re-run your statements.

## Auth & secrets

- **IAM DB auth:** the app calls `generate-db-auth-token` (AWS SDK/boto3 `generate_db_auth_token`) and uses the token *as the password*. Tokens are SigV4-signed and live **15 minutes** — generate per connection, don't cache past expiry. TLS is **required** for IAM auth. Grant the DB user the `rds_iam` role. Note: once `rds_iam` is granted to a PostgreSQL user, IAM auth *takes precedence over* password auth for that user. Best when < ~200 new connections/sec; beyond that, pool or front with RDS Proxy. The token is ~1KB+ — make sure your driver/DSN doesn't truncate it. See IAMDBAuth doc.
- **Secrets Manager managed password:** create/modify the cluster with `--manage-master-user-password`; Aurora generates a 28-char password and **rotates every 7 days by default** (no app change needed). Read it at runtime with `secretsmanager:GetSecretValue` against the cluster's `MasterUserSecret.SecretArn` — never copy it into env. The secret JSON holds `username`/`password` (and host/port/dbname for DB secrets). Cache the fetched value in-process to avoid Secrets Manager throttling, and re-fetch on auth failure (it may have rotated). Grant least-privilege read on that one secret ARN only.
- **Don't use the master user for the app.** Create a least-privilege role (e.g. `app_rw`, `app_ro`) scoped to the schemas it needs. The Bedrock/pgvector path uses a dedicated `bedrock_user` on a `bedrock_integration` schema — mirror that separation.

## TLS / encryption in transit

- Download the CA bundle (`global-bundle.pem` or the regional bundle) and point the driver at it. Register **only the root CA**, never intermediates (RDS auto-rotates server certs and stale intermediates break connections).
- Enforce `sslmode=verify-full` (validates hostname + chain). Example DSN: `postgresql://...?sslmode=verify-full&sslrootcert=/path/global-bundle.pem`.
- Set the cluster parameter `rds.force_ssl=1` to reject non-TLS connections server-side — defense in depth so a misconfigured client can't silently downgrade.
- Current CAs: `rds-ca-rsa2048-g1` (default), `rds-ca-rsa4096-g1`, `rds-ca-ecc384-g1`. **Confirm the active CA and its expiry** with `aws rds describe-certificates` / `describe-db-instances` — server certs auto-rotate (1-yr validity when rotation-without-restart is supported, else 3-yr). If you use **RDS Proxy**, it presents ACM certs — you do *not* download or pin RDS CA bundles for proxy connections.

## Idempotency, errors & retry/failover

- **Endpoints & failover:** use writer/reader endpoints. On failover Aurora repoints DNS; clients caching DNS too long keep hitting the dead instance. Keep DNS TTL short and reconnect on connection errors (JVM/driver DNS caches are a classic trap). After a failover the old writer briefly becomes a reader — a write on a stale connection fails with read-only errors; drop and re-establish the connection.
- **Retry transient failures** (connection reset, failover, `too many connections`, serverless scaling pauses) with **exponential backoff + jitter** and a cap. Do *not* blindly retry non-idempotent writes — make them idempotent first (below).
- **Make writes idempotent:**
  - Unique constraint + `INSERT ... ON CONFLICT (key) DO NOTHING/UPDATE` for upserts.
  - An idempotency-key column (e.g. `release_run_id`, evidence/claim id) the caller supplies, so a re-run of a LangGraph node doesn't double-insert evidence/artifacts.
  - `pg_advisory_xact_lock(...)` to serialize a critical section across concurrent workers.
  - Wrap multi-statement work in a transaction so a mid-run crash rolls back cleanly; never assume a partially-applied batch.
- **Set timeouts:** `statement_timeout` and `idle_in_transaction_session_timeout` so a hung LangGraph step can't pin a connection forever (especially behind RDS Proxy, where pinned sessions starve the pool). For PostgreSQL, RDS Proxy can't pin via filters and doesn't support client `CancelRequest` (Ctrl-C in psql won't cancel) — design around it.
- **Connection storms hurt Serverless v2.** Each new connection costs memory/ACUs; a burst of GitHub Actions jobs each opening fresh connections can spike scaling and throttle. Pool within a job and reuse; front with RDS Proxy for fan-out; or use the Data API (no persistent connections) for spiky CI/serverless callers.

## "Webhooks" — what it maps to here

Aurora has **no outbound webhooks**, so there's no Aurora webhook signature to verify. Two honest mappings:

- **DB-side events** (failover, low storage, rotation): use **RDS Event Subscriptions → Amazon SNS**, not webhooks. Authenticity comes from SNS message signature verification + subscription confirmation, not an Aurora HMAC.
- **Trigger-side** in this product: the GitHub release/workflow-dispatch webhook that *kicks off* a run must have its **HMAC-SHA256 signature (`X-Hub-Signature-256`) verified** in the Next.js API route using GitHub's webhook secret, constant-time compared. That's a GitHub concern handled before any Aurora write — keep it out of the DB layer but don't skip it.

## Dev/sandbox vs production modes

- **Dev/hackathon:** static creds in GitHub/Vercel/AWS env secrets, `sslmode=require`, Serverless v2 with low min ACU, single writer. Acceptable to bootstrap (the spec defers Secrets Manager) — but gate it behind an env flag, never the default.
- **Production:** Secrets Manager managed password **or** IAM auth, `sslmode=verify-full` + `rds.force_ssl=1`, cluster in **private subnets** (not publicly accessible), security groups scoped to the app/proxy SG only, **KMS encryption at rest** (set at creation — can't be toggled later), least-privilege DB roles, audit logging (pgaudit + CloudWatch logs export). Use the same code path for both, switching only via config — don't fork connection logic.

## pgvector (provenance / RAG store)

- Enable with `CREATE EXTENSION IF NOT EXISTS vector;`. Requires a supported engine (per the doc: 16.1+, 15.4+, 14.9+, 13.12+, 12.16+) and **pgvector 0.5.0+** for HNSW. Verify with `SELECT extversion FROM pg_extension WHERE extname='vector';`.
- Column: `embedding vector(N)` where N matches your embedding model (e.g. Titan v2 `1024/512/256`, Titan v1.2 `1536`, Cohere `1024`).
- Index: prefer **HNSW** (`USING hnsw (embedding vector_cosine_ops)`) over IVFFlat for recall/latency; tune `ef_construction` (256 is the doc's suggestion on 0.6.0+ parallel builds). Match the operator class to your distance metric (`vector_cosine_ops` / `vector_l2_ops` / `vector_ip_ops`) and the query operator (`<=>` cosine, `<->` L2, `<#>` inner product).

## Checklist

- [ ] Pulled the current docs above; confirmed active CA bundle + expiry via `describe-certificates`.
- [ ] App connects via **writer/reader endpoint** (or RDS Proxy / Data API), never an instance endpoint.
- [ ] No credentials in code: IAM auth tokens (15-min, regenerated) or Secrets Manager (cached + re-fetch on auth error).
- [ ] App uses a least-privilege role, not the master user.
- [ ] `sslmode=verify-full` with the RDS root CA; `rds.force_ssl=1` set server-side (or RDS Proxy + ACM).
- [ ] Cluster in private subnets, not publicly accessible; SG scoped; KMS-at-rest on (set at creation).
- [ ] Connection pooling in place; Data API/RDS Proxy considered for spiky CI/serverless callers.
- [ ] Transient errors retried with backoff+jitter; reconnect on failover; `statement_timeout` + `idle_in_transaction_session_timeout` set.
- [ ] Writes idempotent (unique constraint + `ON CONFLICT`, idempotency key, or advisory lock) so retries/re-run nodes don't duplicate.
- [ ] GitHub trigger webhook signature (`X-Hub-Signature-256`) verified constant-time before any DB write; SNS used for DB-side events.
- [ ] pgvector extension version + index type (HNSW) + vector dimension match the embedding model.
- [ ] Dev vs prod selected by config flag, single shared code path.

## Gotchas

- **`rds_iam` overrides password auth.** Grant it to a PostgreSQL user (even the master) and that user can *only* authenticate via IAM token thereafter — granting it to your master user can lock you out of password login. Use a dedicated IAM-auth role.
- **IAM auth tokens expire in 15 minutes** and must be regenerated per connection; long-lived pools that cache the token as a static password silently start failing after 15 min. Also: temporary AWS creds used to *mint* the token must still be valid at *connect* time.
- **CloudTrail/CloudWatch don't log `generate-db-auth-token`** — IAM DB auth connection attempts are largely invisible to those logs; don't rely on them for an auth audit trail.
- **Registering intermediate CA certs (not just the root) breaks connections when RDS auto-rotates the server cert.** Trust the root only.
- **`sslmode=require` does NOT verify identity** — it encrypts but accepts any cert (MITM-able). Production needs `verify-full`.
- **Secrets Manager managed master password rotates every 7 days by default**; code that reads the secret once at boot and caches forever breaks on rotation. Re-fetch on auth failure.
- **You cannot change the KMS key (or enable encryption-at-rest) after cluster creation** — get it right at create time or rebuild.
- **RDS Proxy + PostgreSQL quirks:** no session-pinning filters, no client `CancelRequest` (psql Ctrl-C won't cancel a query), `lastval()` can be wrong (use `INSERT ... RETURNING`), and switching DB auth to SCRAM-only makes an existing proxy unavailable for up to ~60s — keep both SCRAM and MD5 enabled or create a new proxy.
- **Statements > 16 KB pin the RDS Proxy session** to one connection, defeating pooling — watch large `INSERT`/batch payloads (e.g. big evidence blobs; put those in S3, store the pointer in Aurora).
- **Connection storms quietly drive Serverless v2 ACU/cost up and trigger throttling** — a matrix of GitHub Actions jobs each opening raw connections is a classic offender. Pool, proxy, or use the Data API.
- **Stale DNS after failover** keeps you talking to the old writer (now read-only) → writes fail with read-only errors until you reconnect; cap DNS TTL and treat read-only errors as a reconnect signal.
- **Data API isn't a drop-in for a connection:** result-size limits, no implicit cross-call transactions (must thread a `transactionId`), and added per-call latency — fine for CI/edge callers, wrong for chatty inner loops.
- **The pgvector doc page is framed as Bedrock Knowledge Base setup**, but it's the authoritative source for engine-version/pgvector requirements — don't assume pgvector is missing just because the page title says "Knowledge Base."
- **Aurora has no webhook to sign** — if a spec asks for "Aurora webhook signature verification," it's a category error; verify the GitHub trigger webhook HMAC and use SNS (with signature verification) for DB-side events instead.
- **Serverless v2 doesn't support Secrets Manager managed master password on Serverless v1**, global databases, or Blue/Green — check the limitations list before assuming managed rotation is available for your cluster topology.

<!-- sources fetched at generation: https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/UsingWithRDS.IAMDBAuth.html, https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/UsingWithRDS.SSL.html, https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/rds-secrets-manager.html, https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/rds-proxy.html, https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/Aurora.Overview.Endpoints.html, https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/data-api.html, https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/AuroraPostgreSQL.VectorDB.html -->

From ichiorca/shipsignal, Apache-2.0.

More from ichiorca/shipsignal