Same role, different privileges per database

Same role, different privileges per database
Type: incident simulation · Topic: Security & Access · Level: L3 · Duration: 10–15 min
Launch: ride postgres start stage-10/05-same-role-different-privileges-per-database

POSTMORTEM (root cause · how it was found · the fix · lesson)
Root cause: PostgreSQL roles are cluster-wide, but object privileges are per
database. `app_user` had the table grants it needed in `app_db` (SELECT/INSERT on
orders) but no SELECT on `billing_db.invoices`, so the billing feature failed even
though the "same" role worked fine elsewhere.

How it was found: comparing has_table_privilege('app_user', …, 'SELECT') in app_db
(orders → true) against billing_db (invoices → false), and information_schema.role_
table_grants in billing_db, showed the missing per-database grant.

The fix: grant the missing privilege in the right database:
  \connect billing_db
  GRANT SELECT ON invoices TO app_user;

Lesson: don't assume a role's grants are identical across databases — a cluster-wide
role still needs object privileges granted per database/schema/table. Grant the
minimum that's missing; don't paper over it with superuser or ALL PRIVILEGES, and
don't re-grant in the database that already works.

INVESTIGATION HINTS (the staged path to diagnose and fix)
1. The same app_user works in app_db but fails reading billing_db. Roles are cluster-wide, but table privileges are per database — compare: has_table_privilege('app_user','orders','SELECT') in app_db vs has_table_privilege('app_user','invoices','SELECT') in billing_db.
2. app_user has SELECT on app_db.orders but lacks SELECT on billing_db.invoices. Granting in one database does nothing for the other; check information_schema.role_table_grants in billing_db.
3. Grant exactly what's missing, in billing_db: \connect billing_db; GRANT SELECT ON invoices TO app_user; Don't grant superuser or ALL on everything, and don't 'fix' app_db (it's already fine).