Row-Level Security

Status & purpose

The motivation is the Supabase experience: the database itself enforces who can see and change which rows, driven by a request's authenticated identity (a JWT's claims and role), so a single auto-generated API surface (PostgREST) can be exposed to untrusted clients safely. The application writes CREATE POLICY once; every query — no matter which client issues it — is filtered by the engine. This page specifies how that experience maps onto Twill without violating the deciding rule or the storage seam.

Classifying RLS with the deciding rule

The Capabilities rule is binary: storage/execution capabilities build IN, interface/service capabilities compose AROUND. RLS does not fall cleanly on one side — and that ambiguity is exactly why it was left open. Splitting it into its two constituent concerns resolves the tension:

Why composed-only enforcement is insufficient

The tempting answer — "let PostgREST or the app filter rows" — fails the security property, and it is worth stating why precisely, because it is the crux of the whole proposal.

This is the same reasoning that put MVCC visibility in the executor rather than in a client library: a per-row filter that some callers can skip is not a guarantee. Composed identity is fine (every path must still present a principal), but composed enforcement is not. Hence the split.

Proposed design — engine-native enforcement

1 · Session principal (reuse, don't reinvent)

The wire layer already "resolves a principal and forwards statements tagged with that principal" (Server Mode). RLS needs that principal readable from inside the executor. The proposal adds a per-connection, per-transaction session context — a small bag of claims plus an active role — set through the existing statement path (no new hot-path export):

-- composed identity layer sets these after verifying the JWT (6E-style SET shims):
SET twill.jwt.claims = '{"sub":"u_42","role":"authenticated","org":"acme"}';
SET ROLE authenticated;          -- pins the active role for policy matching
-- claims are readable inside policies via a tiny built-in accessor:
--   auth.uid()        -> claims->>'sub'
--   auth.role()       -> current role
--   auth.claim('org') -> claims->>'org'

• MUST scope the session context to the connection (and overrideable per transaction), never process-global — multiple connections to one shared Database (see db.rs registry) MUST NOT see each other's principal.
• MUST treat the claims blob as opaque, untrusted input the engine never verifies — verification is the composed layer's job; the engine only reads the already-trusted principal the boundary vouched for.
• SHOULD flow through the existing SET handling added in stage 6E rather than a new C-ABI export, so ENGINE_ABI_VERSION need not bump.

2 · Policy DDL & the catalog fact

Policies are catalog state, added the same additive way Phase 6 added constraints and defaults — a new backward-compatible WAL op, replayed on recovery, branched with the database. No storage-trait change.

ALTER TABLE notes ENABLE ROW LEVEL SECURITY;

CREATE POLICY notes_owner ON notes
  FOR ALL                              -- or SELECT / INSERT / UPDATE / DELETE
  TO authenticated                     -- role this policy applies to
  USING      (user_id = auth.uid())    -- read/visibility predicate
  WITH CHECK (user_id = auth.uid());   -- write-admissibility predicate

• MUST persist each policy as an additive CreatePolicy WAL fact (predicate AST + command + roles), exposed via Connection::catalog like other schema facts; recovery rebuilds the policy set by replay, exactly as Phase 6 catalog facts do.
• MUST keep policy DDL autocommit-only, consistent with the existing DDL scope boundary (ENGINE_ERR_TXN inside an explicit transaction).
• SHOULD default-deny once RLS is enabled on a table and no policy grants access — matching Postgres semantics (enable = "deny all" until a policy opens a hole).

3 · Enforcement in the executor

This is the load-bearing part and the reason it must be in-core. When the executor (exec.rs) scans a table with RLS enabled, it AND-s the matching policies' USING predicate into the scan filter, evaluated per row against the session principal — over the same MVCC snapshot, so visibility and security compose without a second pass. Writes are additionally checked against WITH CHECK before a row version is stamped.

• MUST apply the USING predicate to every read of a protected table — base scans, join inputs, subqueries, and the row set feeding RETURNING — so no query shape leaks an unfiltered row.
• MUST reject a write whose new/updated row fails WITH CHECK (ENGINE_ERR_* permission error), before the WAL op is emitted, so a denied write never becomes durable.
• MUST enforce identically in embedded mode and server mode — the executor is the single chokepoint, so an FFI caller and a pg-wire caller get the same filtering.
• MAY provide a privileged bypass for a designated owner/superuser role and for trusted internal maintenance (compaction, replay), gated explicitly and off by default.

  COMPOSE AROUND (identity)                BUILD IN (enforcement)
  ─────────────────────────                ──────────────────────
  better-auth / GoTrue  ─ verify JWT ─┐
  PostgREST / app ─ SET claims+role ──┤
                                      ▼
                              ┌───────────────────────────────┐
   every client path ───────►│  executor (exec.rs)            │
   (FFI, pgwire, branch)     │   scan ⨯ MVCC visibility       │
                             │        ⨯ policy USING predicate │──► only
                             │   write ⨯ WITH CHECK            │    visible
                             └───────────────┬─────────────────┘    rows
                                             │ same WAL / replay
                                             ▼
                                   policies = catalog facts
                                   (branch + scale-to-zero for free)

Why it inherits branching, scale-to-zero, and S3-backing

Because policies are catalog facts carried in the WAL and rebuilt on replay — never a side file — they ride the same machinery the rows and the vector index already do:

branching

a branch forks the catalog at the fork LSN, so it inherits the parent's policies and can diverge them independently — a staging branch can loosen RLS for debugging without touching production. No policy pages are copied (copy-on-write).

scale-to-zero

policies re-materialize from WAL replay on cold start exactly like tables; there is no separate durable policy store to warm.

S3-backing / PITR

policy changes are LSN-stamped facts, so point-in-time recovery restores the policy set that was in effect at that LSN.

Approaches considered

Normative requirements

Placement discipline

• MUST build only enforcement (the per-row predicate + the session-principal read) into the engine; identity verification, role management, and policy-authoring UX stay composed.
• MUST NOT add JWT verification, secret handling, or an auth schema to the engine core — that is a composed service (better-auth / the boundary), per Capabilities.
• MUST NOT rely on a composed layer for enforcement; a policy that some client path can skip is not a guarantee.

Enforcement correctness

• MUST apply policies in the executor over the same MVCC snapshot, on every read shape, identically in embedded and server mode.
• MUST default-deny on an RLS-enabled table with no matching policy, and reject writes failing WITH CHECK before they become durable.
• MUST persist policies as additive WAL catalog facts so they branch, replay, and PITR-restore with the database, leaving the storage seam unchanged.
• SHOULD evaluate the wire-resolved principal once per statement and treat it as already-trusted, never re-verifying it in the engine.

Proposed roadmap placement — Phase 7

RLS slots naturally after Phase 6 (it extends the same hand-written sql.rs→exec.rs frontend and reuses 6E's SET handling and the Phase-3 per-statement principal). Like Phase 6 it is additive frontend + executor work; unlike Phase 6 it adds one new catalog fact and a security guarantee, so it carries its own gate.

• MAY ship as Phase 7 — Row-level security & session authorization: session-context primitive, CREATE POLICY / ENABLE ROW LEVEL SECURITY DDL, executor enforcement, and the CreatePolicy WAL fact.
• SHOULD gate on a security-focused test battery: an unfiltered-bypass attempt across every client path (FFI, pgwire, branch) returns only authorized rows; denied writes never reach the WAL; a branch inherits then independently diverges policies; PITR restores the policy set at an LSN.
• SHOULD keep STORAGE_TRAIT_VERSION at 2 and the C ABI frozen, proving (as Phase 6 did) that the capability is pure frontend/executor growth over an unmoved seam.
• MUST demonstrate unmodified PostgREST + a composed identity layer (better-auth or a JWT shim) delivering the Supabase experience end-to-end: a single REST surface, safe for untrusted clients, filtered by engine-enforced policies.

Open questions & risks

• MAY — predicate cost: an RLS predicate runs per row on every scan of a protected table. How is it kept cheap (predicate caching per statement, index-aware pushdown), and does it ever change a plan's index choice?
• MAY — claims accessor surface: which auth.* built-ins are in scope (just uid()/role()/claim(), or a broader set), and how do they relate to the 6C function library?
• MAY — role model depth: flat roles (Supabase's anon/authenticated/service_role shape) vs. role hierarchies/membership; the latter edges toward the GRANT surface deliberately kept out of scope in SQL Compatibility.
• MAY — bypass governance: how is the privileged owner/maintenance bypass authenticated so it cannot itself become the bypass an attacker uses?
• SHOULD — interaction with FK-embedding data-path rewrites in crates/server: embedded sub-queries must be filtered by the embedded table's policies too, not just the top-level relation.

Related specifications