RLS Pattern — TECH

RLS Pattern — TECH

Status: [CURRENT-CANONICAL] — NEW-S239. Companion to PRODUCT.md. Per-invariant implementation references grounded in current code (file:line + migration draft refs); spike requirements + ratification gates noted inline.

How to use this doc

This file carries implementation references for each P-N invariant in PRODUCT.md. Each T-N entry contains:

• Current state: code / migration file:line that today implements the invariant, or “greenfield” if no code exists.
• Target state: what the platform must do after the combined migration applies.
• Gate: any STILL-OPEN dependency.
• Validation: how the invariant is verified (CI guard / parity test / manual).

Source-of-truth pointers

• ./PRODUCT.md — numbered invariants P-1..P-5.
• supabase/migrations/20260514150238_enable_rls_auto_event_trigger_and_grants_pattern.sql — combined migration draft, APPLY GATED ON LIAM REVIEW.
• docs/plans/phase-0-investigation/supabase-db-action-items.md Items 1 + 2 — source pattern + Liam-curated SQL.
• docs/plans/phase-0-investigation/10-feedback-investigation-findings/00-synthesis-v2.md §3.16 + §3.17 — ratification record.
• .github/workflows/migration-revoke-guard.yml — anon-EXECUTE lint (covers P-4).
• .github/workflows/schema-parity.yml — prod ↔ staging diff guard.

Audience

Engineers writing migrations + reviewers verifying compliance; CI guard authors; DBAs evaluating the auto-RLS pattern.

---

T-1 — RLS auto-enabled on new public tables (implements P-1)

Current state: Greenfield. No event trigger present in production schema as of 14/05/2026.

Target state: rls_auto_enable() event-trigger function + ensure_rls event trigger applied to the database. Fires on ddl_command_end for CREATE TABLE / CREATE TABLE AS / SELECT INTO command tags; iterates DDL commands; for objects in public schema (pg_catalog / information_schema / pg_toast* / pg_temp* skipped), executes ALTER TABLE ... ENABLE ROW LEVEL SECURITY.

Function signature:

CREATE OR REPLACE FUNCTION rls_auto_enable()
RETURNS EVENT_TRIGGER
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = pg_catalog
AS $$ ... $$;
REVOKE EXECUTE ON FUNCTION rls_auto_enable() FROM anon;

Event trigger:

CREATE EVENT TRIGGER ensure_rls
ON ddl_command_end
WHEN TAG IN ('CREATE TABLE', 'CREATE TABLE AS', 'SELECT INTO')
EXECUTE FUNCTION rls_auto_enable();

Lands in: supabase/migrations/20260514150238_enable_rls_auto_event_trigger_and_grants_pattern.sql lines 39-81.

Gate: Liam ratification on the migration draft (synthesis-v2 §3.16 disposition row). No technical blocker.

Validation:

• Apply the migration to staging branch (turayklvaunphgbgscat).
• Create a test table (CREATE TABLE public.test_rls_autoenable (id int)) and assert pg_class.relrowsecurity = true immediately.
• Drop the test table.
• Schema-parity workflow (.github/workflows/schema-parity.yml) flags any drift between prod ↔ staging once both apply.

---

T-2 — Per-role grants helper (implements P-2)

Current state: Greenfield.

Target state: grant_standard_public_table_access(target_table regclass) RETURNS void helper. Applies the standard 3-role pattern:

Role	Grants applied
anon	SELECT (read-only)
authenticated	SELECT, INSERT, UPDATE, DELETE
service_role	SELECT, INSERT, UPDATE, DELETE

Function signature:

CREATE OR REPLACE FUNCTION grant_standard_public_table_access(target_table regclass)
RETURNS void
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = pg_catalog
AS $$ ... $$;
REVOKE EXECUTE ON FUNCTION grant_standard_public_table_access(regclass) FROM anon;

Usage in future migrations:

CREATE TABLE public.my_new_table (...);
SELECT grant_standard_public_table_access('public.my_new_table'::regclass);
-- followed by per-row RLS policies as usual

Lands in: supabase/migrations/20260514150238_enable_rls_auto_event_trigger_and_grants_pattern.sql lines 104-118.

Edge cases:

• Tables needing anon write access (rare): apply explicit grants directly; do NOT use this helper.
• Tables needing service-role-only access (admin / system tables): apply explicit grants directly; do NOT use this helper.

Gate: Same as T-1 — Liam ratification on the combined migration.

Validation:

• Post-apply, run SELECT * FROM information_schema.role_table_grants WHERE table_name = '<new_table>' and assert 3 roles present with the expected grant sets.
• Migration-revoke-guard workflow (.github/workflows/migration-revoke-guard.yml) parses migrations for missing REVOKE-EXECUTE on PL/pgSQL functions — orthogonal but related discipline (see T-4).

---

T-3 — Combined migration (implements P-3 fail-loud behaviour)

Current state: Migration draft committed at supabase/migrations/20260514150238_enable_rls_auto_event_trigger_and_grants_pattern.sql ([skip-doc-freshness-guard] marker present per __tests__/docs/reference-doc-edit-coupled-freshness.test.ts:102 escape hatch convention).

Target state: Migration applied to staging (verify event trigger fires + grants helper callable) then to production.

Rationale for combined migration: RLS + grants in the same transaction guarantees no partial state where a table is exposed to the Data API without RLS, or RLS-enabled without grants. Per synthesis-v2 §3.16+§3.17 disposition: “Pair with §3.17 in the same combined migration so RLS enable + grants land together (RLS-first, then grants).”

Gate: Liam ratification on the migration draft. Apply before Supabase platform deadline 30/05/2026 (when grants-default-deny takes effect).

Post-apply checklist (embedded in migration lines 141-152):

1. Bump docs/reference/SCHEMA-QUICK-REFERENCE.md §32 RPC Functions to include rls_auto_enable() + grant_standard_public_table_access(regclass) + ensure_rls event trigger.
2. Bump Last verified header timestamp in SCHEMA-QUICK-REFERENCE.md citing S239 RLS-PATTERN.
3. Drop the [skip-doc-freshness-guard] marker in the apply commit (only present on draft).

Validation:

• Apply migration to staging; verify event trigger present (SELECT * FROM pg_event_trigger WHERE evtname = 'ensure_rls').
• Create + drop a test table in staging; verify RLS auto-enabled + grants apply correctly when helper called.
• Schema-parity workflow flags any prod ↔ staging drift.

---

T-4 — Per-function anon REVOKE-EXECUTE (implements P-4)

Current state: Pattern enforced ad-hoc per function migration. CI guard .github/workflows/migration-revoke-guard.yml lints PRs for missing REVOKE-EXECUTE blocks on new CREATE FUNCTION public.* migrations.

Target state: Same pattern, no change. P-4 is recorded in this spec to document the orthogonal discipline — the auto-RLS event trigger covers tables only, not functions; anon-EXECUTE on functions remains a per-function migration responsibility.

Required pattern in every function migration:

CREATE OR REPLACE FUNCTION public.foo(...) RETURNS ... LANGUAGE plpgsql AS $$ ... $$;
REVOKE EXECUTE ON FUNCTION public.foo(...) FROM anon;
-- Also: if SECURITY DEFINER and tenant-scoped, REVOKE per tenant_role too.

Gate: None — already enforced.

Validation:

• migration-revoke-guard.yml workflow blocks PRs missing the REVOKE block.
• CLAUDE.md “Supabase auto-grants anon EXECUTE on every new public.* PL/pgSQL function” gotcha codifies the policy for human authors.

---

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

Current state: rls_auto_enable() writes to PostgreSQL RAISE LOG only (migration draft lines 62, 65, 68-69). No structured emission to audit_log. Postgres logs flow to the Cloud Run sidecar log ingest and into the KH observability stack via standard log shipping.

Target state (v1): Identical to current state — option (c) from the originally-three-options framing. No audit_log insertion, no fan-out helper. The RAISE LOG lines plus Postgres-log shipping satisfy the v1 observability requirement.

Gate: Closed at S240 (Liam ratification). The decision: defer richer audit-trail integration to v1.1 if operational signal surfaces a need. Original three-options framing retained below for v1.1 reopening:

• (a) Extend rls_auto_enable() to insert into audit_log(event_type='rls_auto_enable', table_name=..., timestamp=...). Requires audit_log table schema decision (event_type CHECK update or open-vocabulary).
• (b) Add a separate observability event-trigger fan-out — rls_auto_enable_observability() — that calls into the existing audit_log write helper.
• (c) RATIFIED-S240 — Defer to structured logging only (Postgres logs → Cloud Run sidecar log ingest → KH observability stack).

Validation (v1):

• Apply migration to staging; create a test table; tail Postgres logs and assert rls_auto_enable: enabled RLS on public.<table> line is emitted.
• Confirm log line is captured by Cloud Run sidecar shipping in staging environment.
• No additional staging-side validation needed for option (c).

Validation (v1.1 if reopened):

• Audit log entry visible per RLS-auto-enable event in staging post-test-table-create.
• Backfill audit for any tables created between v1 apply + v1.1 observability hook landing.

---

Cross-doc cross-references

Doc	What it references
docs/plans/phase-0-investigation/architecture/04-workspace-types.md §RLS-PATTERN	Forward-reference to this PRODUCT + TECH pair (per S239 user decision — Decision 1 RLS-PATTERN destination).
docs/plans/phase-0-investigation/architecture/02-data-flow.md §ingest write paths	Forward-reference to this spec for the auto-RLS guarantee on any new tables data-flow creates.
docs/runbooks/migration-revoke-guard.md (if/when written)	Operational pattern for the migration-revoke-guard.yml CI workflow.
docs/reference/SCHEMA-QUICK-REFERENCE.md §32	Receives rls_auto_enable() + grant_standard_public_table_access(regclass) + ensure_rls post-apply (T-3 post-apply checklist).

Source-doc supersession

Not applicable — NEW spec. No predecessor. Heritage substrate documented in 00-synthesis-v2.md §3.16 + §3.17 (ratification record) and migration draft inline comments (supabase/migrations/20260514150238_*.sql lines 1-33).

Heritage docs feeding this sub-doc

Per docs/specs/core-docs-pathway-assessment/architecture-sub-doc-construction-guide.md §4.1 — three-tier status taxonomy.

Doc	Date	Status	Useful for
docs/plans/phase-0-investigation/10-feedback-investigation-findings/00-synthesis-v2.md §3.16 + §3.17	14/05/2026 (S236 + S237 refreshes)	[CURRENT-CANONICAL] for ratification record of auto-RLS event trigger + grants compliance.	Ratification source for P-1 + P-2 + P-3; cited verbatim in §3.16+§3.17.
docs/plans/phase-0-investigation/supabase-db-action-items.md Items 1 + 2	14/05/2026	[CURRENT-CANONICAL] for Liam-curated source SQL + Supabase platform-deadline framing.	Source for rls_auto_enable() pattern + grants compliance rationale.
supabase/migrations/20260514150238_enable_rls_auto_event_trigger_and_grants_pattern.sql	14/05/2026 (S238 draft)	[CURRENT-CANONICAL] for the migration draft text — APPLY GATED ON LIAM REVIEW.	Function bodies + event trigger DDL + grants helper signature.
CLAUDE.md “Supabase auto-grants anon EXECUTE on every new public.* PL/pgSQL function” gotcha	(ongoing)	[CURRENT-CANONICAL] for the orthogonal anon-EXECUTE discipline.	P-4 substrate; migration-revoke-guard workflow rationale.

---

End of TECH spec. Numbered invariants in ./PRODUCT.md.