Pin the Database Connection SSL Certificate
Set sslmode to verify-full and pin the CA so a rogue DB proxy with a valid but different certificate is rejected.
Task
Apply DATABASE_URL=postgresql://host/db?sslmode=verify-full&sslrootcert=/etc/ssl/db-ca.pem so the schema change is safe, reversible, and does not lock the table during peak traffic.
When to use this
A migration deploys DATABASE_URL=postgresql://host/db?sslmode=verify-full&sslrootcert=/etc/ssl/db-ca.pem during peak hours, takes a 40-second lock, and causes a connection pool exhaustion.
Prerequisites
- A database with migration tooling configured
- A staging environment mirroring production schema
Steps
1. Configure DATABASE_URL=postgresql://host/db?sslmode=verify-full&sslrootcert=/etc/ssl/db-ca.pem in the project configuration file with the recommended defaults.
DATABASE_URL=postgresql://host/db?sslmode=verify-full&sslrootcert=/etc/ssl/db-ca.pem
Expected output: The configuration is accepted and DATABASE_URL=postgresql://host/db?sslmode=verify-full&sslrootcert=/etc/ssl/db-ca.pem runs without errors on the first invocation.
2. Run the check locally to verify DATABASE_URL=postgresql://host/db?sslmode=verify-full&sslrootcert=/etc/ssl/db-ca.pem produces the expected result before pushing.
pnpm test
Expected output: Local run matches CI behavior; no surprises after push.
3. Add the gate to the CI pipeline so DATABASE_URL=postgresql://host/db?sslmode=verify-full&sslrootcert=/etc/ssl/db-ca.pem blocks merge on failure.
- name: Run add connection ssl pinning
run: pnpm test
Expected output: Pull requests with failing DATABASE_URL=postgresql://host/db?sslmode=verify-full&sslrootcert=/etc/ssl/db-ca.pem are blocked from merging.
Troubleshooting
Problem
The check passes locally but fails in CI
Solution
Pin dependency versions and the runtime to match CI exactly; reproduce with the same Docker image or act.
Problem
Configuration is rejected with a schema error
Solution
Check the tool version matches the config schema; older versions may not support newer fields.
Limitations
- The gate only runs on events that trigger the workflow; locally pushed commits bypass it.
- Configuration drift is possible if someone edits settings outside version control.
Make migrations safe
Start by measuring the problem. Set sslmode to verify-full and pin the CA so a rogue DB proxy with a valid but different certificate is rejected. The most common mistake is adding the gate without first confirming it catches the issue you care about. Run DATABASE_URL=postgresql://host/db?sslmode=verify-full&sslrootcert=/etc/ssl/db-ca.pem against the current codebase and inspect the output before wiring it into CI.
DATABASE_URL=postgresql://host/db?sslmode=verify-full&sslrootcert=/etc/ssl/db-ca.pemVerify and roll back
Once the gate is in place, make failures actionable. A failing DATABASE_URL=postgresql://host/db?sslmode=verify-full&sslrootcert=/etc/ssl/db-ca.pem should point to the exact file and line, not a generic error. Pin the tool version in the lockfile so a upstream release does not silently change behavior. Review the gate output in every pull request just like a test result.