Scenario · Security & Access
User has access to the wrong database
A sandboxed PostgreSQL incident — investigate with your own tools, submit a fix, and get deterministic Detect / Fix / Trap scoring.
L3 · 10–15 min · runs locally in Docker
Launch
Start this scenario
Boot it in a real PostgreSQL sandbox and investigate with psql, EXPLAIN and pg_stat_statements.
ride postgres start stage-10/04-user-has-access-to-wrong-databasePart of these paths
Show the postmortem & investigation hints spoilers
User has access to the wrong database
Type: incident simulation · Topic: Security & Access · Level: L3 · Duration: 10–15 min
Launch: ride postgres start stage-10/04-user-has-access-to-wrong-database
POSTMORTEM (root cause · how it was found · the fix · lesson)
Root cause: `app_user` could connect to databases it should never reach. It had been
granted `CONNECT` on `billing_db` and `analytics_db` (with PUBLIC's CONNECT revoked
there) on top of its legitimate `app_db` access. The app worked, so nothing alerted —
but the role's access boundary was far too wide.
How it was found: comparing has_database_privilege('app_user', …, 'CONNECT') across the
three databases showed CONNECT granted on billing_db and analytics_db, not just app_db.
The fix: revoke the unwanted database access, keep app_db:
REVOKE CONNECT ON DATABASE billing_db FROM app_user;
REVOKE CONNECT ON DATABASE analytics_db FROM app_user;
Lesson: a role that "works" isn't safe if it can connect to databases outside its
remit — least-privilege applies to database CONNECT, too. Verify the whole access
boundary, not just that the intended database works. Don't reach for superuser, and
don't over-correct by removing the access the app legitimately needs.
INVESTIGATION HINTS (the staged path to diagnose and fix)
1. Nothing is down — this is a security review finding. app_user should only reach app_db. Check where it can actually connect: SELECT has_database_privilege('app_user', d, 'CONNECT') for app_db, billing_db, analytics_db.
2. app_user was granted CONNECT on billing_db and analytics_db it has no business touching. A working role that can reach databases it shouldn't is an access-boundary problem, not an outage.
3. Revoke the unwanted access, keep app_db: REVOKE CONNECT ON DATABASE billing_db FROM app_user; (and analytics_db). Don't grant superuser, and don't revoke app_db (that breaks the app).