RLS Pattern — PRODUCT

Status: [CURRENT-CANONICAL] — NEW-S239. Spec destination ratified by Liam at S239 Wave 2 dispatch (separate doc per docs/specs/core-docs-pathway-assessment/INV-architecture-split-readiness.md §7.4 recommendation). Substrate from docs/plans/phase-0-investigation/10-feedback-investigation-findings/00-synthesis-v2.md §3.16 (auto-RLS event trigger) + §3.17 (grants compliance).

How to use this doc

This file holds user-perspective invariants for the RLS pattern — what the platform guarantees about row-level security on new public-schema tables, expressed so each line is independently testable. Companion TECH.md carries implementation references (functions, migration patterns, CI guards).

Invariants are numbered globally (P-1, P-2, …) so TECH.md references by ID. Each carries [RATIFIED-S2XX] with source citation or [STILL-OPEN — gates on X] with explicit gating item.

Source-of-truth pointers

docs/plans/phase-0-investigation/10-feedback-investigation-findings/00-synthesis-v2.md §3.16 (auto-RLS event trigger ratification) + §3.17 (grants compliance ratification).
docs/plans/phase-0-investigation/supabase-db-action-items.md Item 1 (grants compliance) + Item 2 (Liam-curated auto-RLS SQL).
supabase/migrations/20260514150238_enable_rls_auto_event_trigger_and_grants_pattern.sql (combined migration draft; APPLY GATED ON LIAM REVIEW per 00-synthesis-v2.md §3.16+§3.17 disposition).
CLAUDE.md Gotchas: “Supabase auto-grants anon EXECUTE on every new public.* PL/pgSQL function” (orthogonal to this spec — see P-4).
docs/plans/phase-0-investigation/architecture/04-workspace-types.md §RLS-PATTERN cross-reference (forward-ref to this doc).

Audience

Engineers writing migrations that create new public.* tables or PL/pgSQL functions; reviewers verifying migration compliance; CI guard authors.

Invariants

P-1 — RLS auto-enabled on new public tables

When a developer creates a new table in the public.* schema via a migration (CREATE TABLE, CREATE TABLE AS, SELECT INTO), the system enables row-level security on that table without any further developer action.

[RATIFIED-S236] — per 00-synthesis-v2.md §3.16 + supabase-db-action-items.md Item 2. Closes OQ-Q24-B + OQ-Q113-B together.

Cross-refs: [Q24-B], [arch 04-workspace-types §RLS-PATTERN cross-ref].

P-2 — Per-role grants required for Data API exposure

When a developer creates a new table in public.*, the system requires explicit per-role grants (anon / authenticated / service_role) for the Data API surface (PostgREST / supabase-js / GraphQL) to read or write that table. Default Supabase platform behaviour from 30/05/2026 is no grant = no Data API exposure.

[RATIFIED-S236] — per 00-synthesis-v2.md §3.17 + supabase-db-action-items.md Item 1. Gates on Supabase platform deadline.

Cross-refs: [Q24-C], [arch 04-workspace-types §RLS-PATTERN cross-ref].

P-3 — Fail-loud Data API behaviour on missing grants

When a migration creates a public.* table without the grants block applied, the Data API returns an explicit permission error on the table (not a silent empty result-set). Features depending on the table fail fast at the API layer rather than silently returning no rows.

[RATIFIED-S236] — per 00-synthesis-v2.md §3.17 platform-behaviour description.

Cross-refs: [Q24-C], CLAUDE.md “Silent failures in Supabase calls” gotcha (orthogonal — that gotcha covers TS-side handling).

P-4 — Per-function anon REVOKE-EXECUTE for PL/pgSQL functions

When a developer writes a new PL/pgSQL function in public.*, the migration includes an explicit REVOKE EXECUTE ON FUNCTION public.foo() FROM anon in the same migration as the CREATE FUNCTION statement. pg_default_acl Supabase defaults make the default REVOKE ... FROM PUBLIC a no-op against the anon role.

[RATIFIED-S203+] — per CLAUDE.md “Supabase auto-grants anon EXECUTE on every new public.* PL/pgSQL function” gotcha. Orthogonal to P-1 + P-2 — auto-RLS event trigger covers tables only, not functions.

Cross-refs: migration-revoke-guard.yml workflow (.github/workflows/migration-revoke-guard.yml) enforces the REVOKE block at PR time.

P-5 — Auto-RLS event-trigger observability (DEFERRED-v1.1)

When the RLS auto-enable event trigger fires on a new public.* table, the system records the trigger invocation in PostgreSQL structured logs (RAISE LOG) only at v1 — no audit_log row is written, no separate observability fan-out is invoked. Structured-log emission per migration draft supabase/migrations/20260514150238_*.sql lines 62, 65, 68-69 provides table name + timestamp + invoking role at the Postgres-log layer, which flows to the Cloud Run sidecar log ingest and into the KH observability stack via standard log shipping.

[DEFERRED-v1.1] — per Liam S240 ratification (option (c) per companion TECH.md T-5 originally-three-options framing: defer to structured logging only). The decision closes the v1 scope; option (a) audit_log insert and option (b) fan-out helper remain candidates for v1.1 if the operational picture surfaces a need for richer audit-trail integration.

Cross-refs: [Q24-B] ratification did NOT decide on observability hook; S240 closes the gap as DEFERRED-v1.1 rather than STILL-OPEN.

Out of scope (v1)

Per-tenant RLS policy authoring patterns (handled per-domain — see 04-workspace-types.md §workspaces.application_type_id usage for application-type-driven access patterns).
Cross-schema grants (auth.* / storage.* / extensions.*) — Supabase-managed; outside this spec.
Data API revocation pattern (taking grants AWAY post-rollout) — covered in docs/runbooks/migration-revoke-guard.md operationally.

v1.1 candidates (DEFERRED — not blocking)

P-5 observability hook (above).
Per-table audit policy registry (mapping table name → audit pattern) — currently ad-hoc per table.

End of PRODUCT spec. Implementation references in ./TECH.md.