Default privileges surprise

Default privileges surprise
Type: incident simulation · Topic: Security & Access · Level: L3 · Duration: 10–15 min
Launch: ride postgres start stage-10/06-default-privileges-surprise

POSTMORTEM (root cause · how it was found · the fix · lesson)
Root cause: `app_user` had an explicit SELECT on the existing table, but a release
created a new table (`new_orders`) that it couldn't read. The grants on existing
objects say nothing about future ones — that's what `ALTER DEFAULT PRIVILEGES`
governs, and none had been configured for the role creating the tables (`migrator`).

How it was found: has_table_privilege showed SELECT on existing_orders but not
new_orders; pg_default_acl had no default grant for app_user, so every newly created
table started inaccessible.

The fix: cover the current table AND future ones:
  GRANT SELECT ON new_orders TO app_user;
  ALTER DEFAULT PRIVILEGES FOR ROLE migrator IN SCHEMA public
    GRANT SELECT ON TABLES TO app_user;

Lesson: a GRANT on today's tables is not enough — without default privileges, the
next migration's table is born without the app's access and the incident repeats.
Configure ALTER DEFAULT PRIVILEGES for the creating role. Granting only the current
table (or reaching for superuser) is not a fix.

INVESTIGATION HINTS (the staged path to diagnose and fix)
1. Existing tables are readable but a newly created one isn't. Compare: has_table_privilege('app_user','existing_orders','SELECT') vs has_table_privilege('app_user','new_orders','SELECT') in app_db. A GRANT on existing tables doesn't cover future ones.
2. Future objects are governed by default privileges. Inspect them: SELECT defaclrole::regrole, defaclnamespace::regnamespace, defaclobjtype, defaclacl FROM pg_default_acl; — there's no default grant for app_user, so every new table starts inaccessible.
3. Fix both the present and the future: GRANT SELECT ON new_orders TO app_user; AND ALTER DEFAULT PRIVILEGES FOR ROLE migrator IN SCHEMA public GRANT SELECT ON TABLES TO app_user; Granting only the current table leaves the next release broken.