RLS policy mistake

RLS policy mistake
Type: incident simulation · Topic: Security & Access · Level: L4 · Duration: 10–15 min
Launch: ride postgres start stage-10/07-rls-policy-mistake

POSTMORTEM (root cause · how it was found · the fix · lesson)
Root cause: row-level security was enabled on `tenant_orders`, but the policy used
`USING (true)` — so `app_user` could read every tenant's rows. A cross-tenant data
exposure that's easy to miss, because checking as an admin (who bypasses RLS) shows
nothing wrong.

How it was found: pg_policies showed the policy's USING expression was `true`;
probing AS app_user with `app.tenant_id` set revealed rows from other tenants.

The fix: replace the broad policy with a tenant-scoped one:
  DROP POLICY tenant_orders_policy ON tenant_orders;
  CREATE POLICY tenant_orders_policy ON tenant_orders FOR SELECT TO app_user
    USING (tenant_id = current_setting('app.tenant_id', true));

Lesson: RLS must be verified as the application role with its tenant context — a
superuser or the table owner bypasses RLS, so an admin check is misleading. A policy
that's too broad leaks across tenants; too narrow hides a tenant's own rows. Don't
"fix" visibility by disabling RLS or granting superuser — that removes the control
entirely.

INVESTIGATION HINTS (the staged path to diagnose and fix)
1. tenant_orders has RLS enabled, but tenants can see each other's rows. Inspect the policy: SELECT * FROM pg_policies WHERE tablename = 'tenant_orders'; — the USING expression is `true`, which lets the app role read everything.
2. Test RLS as the application role, not as admin: a superuser (and the table owner) BYPASSES row-level security, so an admin SELECT is misleading. The policy must scope rows to the tenant via current_setting('app.tenant_id').
3. Replace the broad policy: DROP POLICY tenant_orders_policy ON tenant_orders; then CREATE POLICY ... FOR SELECT TO app_user USING (tenant_id = current_setting('app.tenant_id', true)); Don't disable RLS, and don't grant superuser.