Search path risk

Search path risk
Type: incident simulation · Topic: Security & Access · Level: L3 · Duration: 10–15 min
Launch: ride postgres start stage-10/08-search-path-risk

POSTMORTEM (root cause · how it was found · the fix · lesson)
Root cause: `app_user`'s `search_path` was `public, app`, and a shadow
`public.orders` masked the real `app.orders`. Unqualified references to `orders`
resolved to the wrong (public) table — a schema-resolution hazard that can expose or
corrupt the wrong object even while everything "works".

How it was found: SHOW search_path showed public ahead of app; pg_tables showed
`orders` in both schemas; resolving `orders` as app_user pointed at public.orders.

The fix: give the role a safe, schema-qualified search_path so it resolves the
intended schema first:
  ALTER ROLE app_user IN DATABASE app_db SET search_path = app, pg_catalog;

Lesson: don't rely on an implicit, public-first search_path — it invites object
shadowing. Resolve application objects from an explicit, app-owned schema (and keep
`public` locked down). Adding an index, granting superuser, or dropping a schema
doesn't address the resolution problem.

INVESTIGATION HINTS (the staged path to diagnose and fix)
1. The app reads `orders` but gets the wrong table. There are two: app.orders (real) and public.orders (a shadow). Check the role's resolution: SHOW search_path; and SELECT schemaname, tablename FROM pg_tables WHERE tablename = 'orders';
2. app_user's search_path is `public, app`, so the unqualified `orders` resolves to public.orders — the shadow masks the real app.orders. This is a schema-resolution / search_path safety problem.
3. Set a safe, app-first search_path for the role: ALTER ROLE app_user IN DATABASE app_db SET search_path = app, pg_catalog; Don't add an index, don't grant superuser, and don't drop the app schema.