Overprivileged application role

Overprivileged application role
Type: incident simulation · Topic: Security & Access · Level: L3 · Duration: 10–15 min
Launch: ride postgres start stage-10/02-overprivileged-application-role

POSTMORTEM (root cause · how it was found · the fix · lesson)
Root cause: `app_user` had been granted dangerous cluster attributes — CREATEDB and
CREATEROLE — on top of the table privileges it actually needs. The application
worked, so nothing alerted, but the role was a standing security liability: a
compromised app connection could create databases and roles.

How it was found: pg_roles showed rolcreatedb / rolcreaterole set on app_user;
information_schema.role_table_grants showed the legitimate, minimal table grants it
should keep.

The fix (least-privilege): strip the attributes, keep the grants:
  ALTER ROLE app_user NOSUPERUSER NOCREATEDB NOCREATEROLE NOREPLICATION;

Lesson: application roles must be least-privilege — no SUPERUSER / CREATEDB /
CREATEROLE / REPLICATION, only the object privileges the app uses. Never "fix"
access incidents by granting superuser, and don't over-correct by revoking the
privileges the app legitimately needs (that just trades a security bug for an outage).

INVESTIGATION HINTS (the staged path to diagnose and fix)
1. The app works, but a security review flagged app_user. Inspect its cluster attributes: SELECT rolname, rolsuper, rolcreatedb, rolcreaterole, rolreplication FROM pg_roles WHERE rolname = 'app_user'; An application role should hold none of those.
2. app_user has CREATEDB and CREATEROLE it never needs. Check what table access it actually uses (information_schema.role_table_grants) so you can trim attributes without breaking the app.
3. Strip the excess attributes while keeping the table grants: ALTER ROLE app_user NOSUPERUSER NOCREATEDB NOCREATEROLE NOREPLICATION; Don't grant superuser, and don't revoke the app's SELECT/INSERT/UPDATE on app_orders (that breaks production).