PostgreSQL ReadOnly Role Discovery Failure

The Incident (What Does the Error Mean?)

psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed:
FATAL:  role "readonly" does not exist

PostgreSQL's authentication pipeline checks pg_catalog.pg_roles before evaluating pg_hba.conf rules. If the role doesn't exist in pg_roles, the connection dies at the role-lookup stage — before any password is checked, before any SSL handshake completes, before your app pool even gets a connection object back. Every thread waiting on that pool slot is now blocked. In a high-concurrency app, this cascades into a full connection pool exhaustion within seconds.

The immediate consequences:

• All application services using the readonly DSN string return 500 or equivalent DB connection errors.
• Read replicas are inaccessible, pushing all query load to the primary.
• Monitoring dashboards lose their read-path queries.

The Attack Vector / Blast Radius

This is a provisioning gap, not a runtime failure — which makes it more dangerous than it looks.

Scenario 1 — Partial Infrastructure Rollout: A Terraform apply or Ansible playbook ran the compute/RDS provisioning but the SQL role-creation step failed silently (e.g., wrong database target, migration runner lacked CREATEROLE privilege). The role exists in staging but not production. Your deploy pipeline showed green. Production is broken.

Scenario 2 — Cross-Database Role Assumption: Roles in PostgreSQL are cluster-level, but GRANT permissions are database-level. A DBA created readonly in database appdb but your connection string targets analyticsdb. The role doesn't exist there. This is the most common cause in multi-tenant RDS setups.

Scenario 3 — Security Regression via Role Drop: Someone ran DROP ROLE readonly during a cleanup sweep without auditing active pg_hba.conf entries or application DSNs. No foreign key constraint stops you from dropping a role that active connection strings depend on.

Blast radius: Read traffic fails entirely. If your application doesn't have a write-fallback circuit breaker, write traffic may also degrade as connection pool threads pile up waiting for the failed read connections to time out.

Prevention in CI/CD

1. Checkov — Scan Terraform for missing role resources:

# .checkov.yml
checks:
  - CKV_PG_1  # Enforce encrypted connections
# Add custom check: assert postgresql_role exists before postgresql_grant

2. OPA/Conftest policy — Block deploys if DB role resources are absent:

package terraform.postgresql

deny[msg] {
  resource := input.resource.postgresql_grant[_]
  role := resource.role
  not input.resource.postgresql_role[role]
  msg := sprintf("postgresql_grant references role '%v' with no corresponding postgresql_role resource.", [role])
}

3. Post-deploy smoke test in CI pipeline:

#!/bin/bash
# Run after terraform apply in pipeline
psql "$DB_ADMIN_DSN" -c "\du readonly" | grep -q readonly || {
  echo "FATAL: readonly role missing post-deploy" && exit 1
}

4. Audit trigger — Alert on DROP ROLE:

-- On PostgreSQL 15+ with pgaudit extension
SET pgaudit.log = 'role';
-- Routes DROP ROLE events to your SIEM via PostgreSQL log stream

5. Separate your DB provisioning Terraform from your infra Terraform. Use a dedicated terraform/db-roles module with its own state file and pipeline stage that runs after the RDS instance is confirmed healthy. Never bundle role creation in the same apply as instance creation — race conditions on RDS availability windows will silently skip role creation.