​

Database View & Migration RLS Safety

​

The three footguns that caused CFO-7727

1. pg_get_viewdef() never emits view options. Copying a view definition from production (or from pg_views) gives you the SELECT — and silently loses WITH (security_invoker = on). A definition “pulled verbatim from prod” is NOT verbatim.
2. CREATE OR REPLACE VIEW resets unspecified options. It preserves ownership and grants, but any reloption you don’t restate (including security_invoker) is removed. There is no warning.
3. A view without security_invoker runs as its owner. Our views are owned by postgres, which owns the base tables — and table owners bypass RLS. The result is not an error and not zero rows: it is all rows, for every caller, even when the caller carefully sets SET ROLE authenticated + JWT claims (as the sql.cfosilvia.com query service does).

​

Rules (enforced in review)

​

R1 — Every view in public MUST declare security_invoker

CREATE OR REPLACE VIEW public.<name>
WITH (security_invoker = on) AS
SELECT ...

​

R2 — The clause is the requirement; CI and the daily audit enforce it

• Statically, on every PR: lib/migrations/__tests__/view-security-invoker.test.ts parses every CREATE [OR REPLACE] VIEW in supabase/migrations/** and fails CI when a clerk_id-scoped view does not declare the option in its WITH clause. This catches the footgun at the point it is introduced — a definition copied from prod, pasted into a new migration. A same-file ALTER VIEW ... SET (security_invoker = on) does not satisfy it: a statement that is only safe once a later statement runs does not survive a partial hand-apply, which is how CFO-7727 happened. Repair migrations that restore the flag on a view they do not redefine (such as 20260828_02) contain no CREATE for that view and are simply not examined.
• At runtime, daily: public.audit_rls_drift() (migration 20260828_03, run by app/api/cron/audit-rls-drift/route.ts at 06:00 UTC) lists every public view whose reloptions lack security_invoker, plus four other RLS checks, and alerts Slack on any row. This catches anything that reached the database another way — a manual apply, a hotfix, a migration applied out of order. The SECURITY DEFINER check skips event trigger functions (20261001_01): Postgres only runs them from an event trigger, so no client can call one, and extensions such as pgaudit install them into public owned by supabase_admin, where the suggested REVOKE is a no-op. The skip is by return type, never by extension, so an extension’s ordinary SECURITY DEFINER functions still alert. EXECUTE on the audit itself is service_role only (revoked from anon and authenticated by name, since Supabase grants them directly).

​

R3 — New tables get RLS before they get data

ALTER TABLE public.<name> ENABLE ROW LEVEL SECURITY;

• User-owned tables — write explicit policies scoping on clerk_id = auth.jwt()->>'sub'.
• Server-write-only tables (populated by service_role from API routes or crons, never read by the client) — enabling RLS and writing no policies at all is correct and sufficient. Postgres denies every row to authenticated/anon when no policy applies, so a deny-all policy would be redundant. Mark these with the existing -- Server-write only comment convention so the absence of policies reads as deliberate rather than forgotten.

​

R4 — Functions: SECURITY DEFINER is opt-in and audited

​

R5 — Migrations go through the blessed flow, never applied by hand

​

R6 — After any view change, verify before merging

SELECT c.relname, c.reloptions
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE n.nspname = 'public' AND c.relkind = 'v'
  AND (c.reloptions IS NULL
       OR NOT (c.reloptions && ARRAY['security_invoker=on',
                                     'security_invoker=true']));
-- Expect: zero rows.

SELECT clerk_id, count(*) FROM public.<view>
WHERE clerk_id IN ('<user_a>', '<user_b>') GROUP BY clerk_id;
-- note both numbers, and the fleet total:
SELECT count(*) FROM public.<view>;

BEGIN;
SET LOCAL request.jwt.claims = '{"sub": "<user_a>"}';
SET LOCAL ROLE authenticated;
SELECT count(*) AS rows_seen,
       count(*) FILTER (WHERE clerk_id <> '<user_a>') AS foreign_rows
FROM public.<view>;
-- MUST be user_a's count above, and foreign_rows MUST be 0.
ROLLBACK;

BEGIN;
SET LOCAL request.jwt.claims = '{"sub": "<user_b>"}';
SET LOCAL ROLE authenticated;
SELECT count(*) AS rows_seen,
       count(*) FILTER (WHERE clerk_id <> '<user_b>') AS foreign_rows
FROM public.<view>;
-- MUST be user_b's count above, and foreign_rows MUST be 0.
ROLLBACK;

​

For reviewers (human and CodeRabbit)

• A CREATE [OR REPLACE] VIEW in supabase/migrations/** lacks WITH (security_invoker = on).
• A CREATE TABLE in public is not followed by ENABLE ROW LEVEL SECURITY in the same migration. Policies are then required for user-owned tables (clerk_id = auth.jwt()->>'sub'), while a server-write-only table correctly has none (R3); RLS left disabled is always a finding.
• A function is declared SECURITY DEFINER without SET search_path and an auth check.
• A PR description mentions SQL already applied to prod and any of the three R5 conditions is missing: an emergency justification, the matching migration file committed the same day, and the manual apply disclosed in the PR description.

​

Related docs

• docs/security/SECURITY-INCIDENT-RPC-FUNCTIONS.md — the SECURITY DEFINER incident behind R4.
• docs/SUPABASE-PAGINATION.md — the other silent-data-corruption footgun (1,000-row cap).
• Agent skill: .agents/skills/database-migration-safety/SKILL.md — the trigger wrapper that routes coding agents here before they write a migration.