Skip to content

RLS Rollout Plan — real row-level security for tenant isolation

Status: Phase 1 in progress (2026-07-03) · Owner: backend Origin: 2026-07-03 DB/isolation audit (same audit that produced the auth-guard fix #1220): RLS is ENABLEd on two tables with zero CREATE POLICY, so all tenant isolation rests on application WHERE org_unit_id = … clauses. One forgotten filter leaks cross-org data (DPDP-reportable for minor students).

Current state (audited 2026-07-03)

Piece State
evaluation_rubrics, ai_evaluations ENABLE ROW LEVEL SECURITY (migration 00008), no policies
All other tables (~105) No RLS at all
Session plumbing set_rls_context() in src/db/session.py sets app.current_user_id / current_org_unit_id / current_org_id / current_user_role / bypass_rls via SET LOCAL
UserWithRLS dependency Exists in deps.py, zero consumers across the API
Enforcement reality None. The app connects as the table owner; Postgres exempts owners from RLS unless FORCE ROW LEVEL SECURITY is set. Even for non-owners, no policies means nothing is evaluated.

Why we cannot just flip FORCE today

grep finds 7 files touching the two RLS-enabled tables, several without any user context to key policies on:

  • src/api/v1/internal.py — eval-service result callbacks (service-to-service, OIDC/task auth, no user)
  • src/services/cloud_tasks.py, physical_exam_batch_prepare.py — background task paths
  • src/api/v1/ai_evaluation.py, physical_exam.py, services/ai_evaluation.py, marks_register_service.py — user paths that today use plain DB (no RLS context set)

Forcing RLS before those paths set context (or are granted bypass) returns zero rows and silently breaks the evaluation pipeline. Silent, because RLS failures look like empty result sets, not errors.

Design decisions

  1. Policy shape (both tables key on org_unit_id):
CREATE POLICY org_isolation ON ai_evaluations
USING (
  current_setting('app.bypass_rls', true) = 'true'
  OR current_setting('app.current_user_role', true) = 'platform_admin'
  OR org_unit_id::text = current_setting('app.current_org_unit_id', true)
);

current_setting(..., true) returns NULL instead of erroring when the variable is unset → unset context means no rows, fail-closed.

  1. Service paths use bypass_rls, not policy holes. Internal callbacks and background jobs call set_rls_context(session, bypass_rls=True) at their entry points. The bypass is explicit and greppable.

  2. SET LOCAL + transaction discipline. SET LOCAL evaporates at transaction end. Any handler that commits mid-request and queries again loses context — the second query sees no rows (fail-closed, but a correctness bug). Phase 2 must re-set context after commits or move context-setting into a session event hook (begin event re-applies).

  3. Parameterized set_config(), not f-string SET. Fixed in Phase 1.

  4. Migrations & the shared Cloud SQL instance. Staging and prod share one instance (separate DBs). Policy DDL is transactional and cheap, but the prod deploy migration-ordering hazard applies: a failed post-migration canary strands the old image against a new schema. Policy-only migrations are backward-compatible by construction (no FORCE = no behavior change), which is exactly why FORCE gets its own later, separately-revertable migration per table.

Rollout phases

  • Phase 1 (this PR): parameterize set_rls_context (set_config()); this design doc. Zero behavior change.
  • Phase 2: CREATE POLICY on the two eval tables (inert without FORCE — owner connections ignore them); dockerized-Postgres integration tests that connect as a non-owner test role to prove the policy filters correctly; wire bypass_rls=True into internal.py + background-task entry points; wire UserWithRLS into the eval endpoints; fix the mid-request-commit context loss.
  • Phase 3: ALTER TABLE … FORCE ROW LEVEL SECURITY — one table per migration, each independently revertable (NO FORCE), staging soak between tables, verified by the Phase-2 tests running against FORCEd tables.
  • Phase 4 (separate decision): extend to other tenant tables (submissions, grades, conversations, …) using the same recipe. Big surface; needs its own audit of context-less writers per table.

Non-goals

  • Replacing application-level org_unit_id WHERE clauses — RLS is defense in depth behind them, not a substitute (CLAUDE.md already mandates both).
  • Per-user (row-owner) policies — org-level isolation only, for now.
  • RLS on the memory library's tables (alembic_kwilo_memory tree) — owned by the kwiloai-memory package, out of scope.

Rejected alternatives

  • Flip FORCE now, fix breakage as found — evaluation callbacks fail silently (empty reads), risking grading data loss during the pilot.
  • Connect the app as a dedicated non-owner role (RLS applies without FORCE) — cleaner long-term, but changing the Cloud SQL connection role touches every deploy secret and the shared-instance permissions for both environments at once; revisit in Phase 4 if FORCE-per-table proves noisy.
  • SQLAlchemy with_loader_criteria global filters — app-level, same class of bug as the WHERE clauses this plan exists to back-stop.