Skip to content

RLS.md

Latest commit

 

History

History
35 lines (24 loc) · 1.66 KB

RLS.md

File metadata and controls

35 lines (24 loc) · 1.66 KB
Raw

Row-Level Security

Why

RLS makes data isolation a database guarantee, not just an application convention. If a handler forgets a WHERE owner_id = …, Postgres still refuses to return another owner's rows. For a multi-tenant product handling personal data, that backstop is worth the complexity.

How it works here

  1. The Worker resolves a principal (owner id) per request.
  2. withPrincipal(db, ownerId, fn) opens a transaction and runs select set_config('app.current_owner', <ownerId>, true) - a transaction-scoped GUC.
  3. Every table has policies for the oche_app role:
    • sessions: owner_id = current_setting('app.current_owner')::uuid
    • players / score_events: ownership via a subquery to the parent session.
  4. Tables are FORCE ROW LEVEL SECURITY, so even the table owner is subject to policies.
  5. oche_app is NOBYPASSRLS and granted only CRUD - never DDL or bypass.

score_events has SELECT + INSERT policies but no UPDATE/DELETE → an append-only audit trail.

Verifying

  • npm run db:rls:check fails if any table isn't enabled+forced, or if oche_app can bypass.
  • npm run test:rls proves owner A cannot read/update owner B's rows.

Production upgrade - JWT

Replace the GUC stand-in with real auth. With Neon RLS, a verified JWT exposes auth.user_id(); policies become, e.g.:

import { crudPolicy, authenticatedRole, authUid } from 'drizzle-orm/neon';
// on sessions:
crudPolicy({ role: authenticatedRole, read: authUid(t.ownerId), modify: authUid(t.ownerId) });

The Worker forwards the user's JWT to Postgres; no app-set GUC needed. Everything else (FORCE, append-only events, least-privilege role) stays.