Missing CONNECT privilege on the target database

Missing CONNECT privilege on the target database
Type: incident simulation · Topic: Security & Access · Level: L3 · Duration: 10–15 min
Launch: ride postgres start stage-10/03-missing-connect-privilege-on-target-db

POSTMORTEM (root cause · how it was found · the fix · lesson)
Root cause: `app_user` could connect to `app_db` (PUBLIC still had CONNECT there) but
not to `billing_db` — CONNECT had been revoked from PUBLIC on billing_db and never
granted to the role. The default database worked, so a shallow check passed, while
the billing feature was down for lack of a database-level CONNECT privilege.

How it was found: comparing has_database_privilege('app_user', …, 'CONNECT') across
app_db and billing_db showed CONNECT present on app_db, absent on billing_db.

The fix: grant exactly the missing privilege:
  GRANT CONNECT ON DATABASE billing_db TO app_user;

Lesson: in a multi-database cluster, "it works in one database" doesn't mean access
is correct everywhere — verify the privilege on the *target* database. Grant the
minimum that's missing (database CONNECT here); don't reach for superuser, don't
grant an unrelated database, and don't confuse this with a pg_hba problem.

INVESTIGATION HINTS (the staged path to diagnose and fix)
1. The default database works but the billing feature is down. Don't trust that one database succeeds — compare CONNECT across both: SELECT has_database_privilege('app_user','app_db','CONNECT'); SELECT has_database_privilege('app_user','billing_db','CONNECT');
2. app_user can reach app_db but lacks CONNECT on billing_db (it was revoked from PUBLIC and never granted to the role). This is a database-level privilege gap, not a pg_hba or table issue.
3. Grant exactly what's missing: GRANT CONNECT ON DATABASE billing_db TO app_user; Don't grant superuser, don't grant the wrong database, and don't touch pg_hba.