Database ownership / privileges mismatch

Database ownership / privileges mismatch
Type: incident simulation · Topic: Multi-Database · Level: L3 · Duration: 10–15 min
Launch: ride postgres start stage-06/07-database-ownership-privileges-mismatch

POSTMORTEM (root cause · how it was found · the fix · lesson)
Root cause: the application role app_user had the privileges it needed in app_db
but not in billing_db — it lacked SELECT on billing_db.billing_events. Privileges
are per-database, so the same role worked in one database and was denied in
another, which reads like an intermittent app bug.

How it was found: has_table_privilege('app_user','billing_events','SELECT') was
false in billing_db while the role worked in app_db.

The mitigation: GRANT SELECT ON billing_events TO app_user, run in billing_db.

Lesson: privileges (and ownership) are per-database — diagnose them in the
database that's failing, and grant the minimal missing privilege there. Granting
SUPERUSER, or granting in the wrong database, is wrong; an index is unrelated.

INVESTIGATION HINTS (the staged path to diagnose and fix)
1. app_user works in app_db but fails in billing_db. Check privileges per database: \connect billing_db then SELECT has_table_privilege('app_user','billing_events','SELECT'); it's false. Compare with app_db where the same role works.
2. It's a privilege mismatch, not data: app_user lacks SELECT on billing_db.billing_events. Privileges are per-database, so a grant in app_db doesn't help billing_db.
3. Grant the missing privilege IN billing_db: \connect billing_db then GRANT SELECT ON billing_events TO app_user; Don't grant SUPERUSER, don't grant in the wrong database, and don't add an index.