> ## Documentation Index > Fetch the complete documentation index at: https://docs.cfosilvia.com/llms.txt > Use this file to discover all available pages before exploring further. # DATABASE VIEW RLS SAFETY # Database View & Migration RLS Safety **Required reading before writing or reviewing ANY migration that creates or replaces a view, creates a table, or touches RLS.** These rules exist because of a real production incident (CFO-7727, 2026-08-28): a routine `CREATE OR REPLACE VIEW` on `transaction_details` silently dropped the view's `security_invoker` flag, and for \~12 hours every chat SQL query and saved dashboard widget against that view returned **every user's transactions** — a cross-tenant data leak caused by two Postgres footguns and one process bypass, all preventable. ## 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` Any `CREATE VIEW` or `CREATE OR REPLACE VIEW` in a migration must include: ```sql theme={null} CREATE OR REPLACE VIEW public. WITH (security_invoker = on) AS SELECT ... ``` This applies to **every** replace, even ones that "only change the SELECT". If the migration you are editing was generated from a prod definition, add the `WITH` clause back — it was stripped by footgun #1. ### R2 — The clause is the requirement; CI and the daily audit enforce it R1 is enforced twice, neither of which lives inside the migration: * 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). Migrations therefore do **not** need a per-file "guard" `DO` block that re-checks `pg_class.reloptions` after the `CREATE OR REPLACE`. Earlier revisions of this document required one; it was redundant with the two checks above and made every view migration longer without adding a case either check misses. Reviewers (human and CodeRabbit) verify the `WITH` clause on the statement itself and stop there. A one-off manual check for a freshly applied migration is the R6 query below. ### R3 — New tables get RLS before they get data Every `CREATE TABLE` in `public` must be immediately followed by: ```sql theme={null} ALTER TABLE public. ENABLE ROW LEVEL SECURITY; ``` The roles that must be shut out are `authenticated` and `anon` — the two the client can ever hold. `service_role` bypasses RLS by role attribute and is unaffected by anything in this section. Then pick exactly one of two shapes: * **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. What is never acceptable is leaving RLS **disabled**, even "temporarily" — that is the one state with no tenant boundary at all. ### R4 — Functions: `SECURITY DEFINER` is opt-in and audited Default to `SECURITY INVOKER`. If a function must be `SECURITY DEFINER`, it must `SET search_path = ''` (or an explicit schema list) and perform its own authorization check on `auth.jwt()->>'sub'`. See `docs/security/SECURITY-INCIDENT-RPC-FUNCTIONS.md` for the incident behind this rule. Materialized views cannot be `security_invoker` — never grant `authenticated`/`anon` SELECT on a matview over user data; wrap access in a scoped function or table instead. ### R5 — Migrations go through the blessed flow, never applied by hand The CFO-7727 view replacement was applied directly to prod with psql/SQL editor at 00:08 UTC and never recorded in `supabase_migrations.schema_migrations` — so no CI policy check ever saw it. Migrations must land as `supabase/migrations/*.sql` files on a PR and be applied by the pipeline. If an emergency hotfix must be applied manually, the matching migration file must be committed the same day and the manual apply called out in the PR description. ### R6 — After any view change, verify before merging Run against the target environment (staging first): ```sql theme={null} 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. ``` And a scoping smoke test. Run it as **two different users** and compare — a single-identity check passes happily against a fleet-wide view, because one number in isolation looks plausible. First establish the expected counts with a service-role query (this bypasses RLS deliberately, to get the ground truth): ```sql theme={null} SELECT clerk_id, count(*) FROM public. WHERE clerk_id IN ('', '') GROUP BY clerk_id; -- note both numbers, and the fleet total: SELECT count(*) FROM public.; ``` Then read the view as each user in turn: ```sql theme={null} BEGIN; SET LOCAL request.jwt.claims = '{"sub": ""}'; SET LOCAL ROLE authenticated; SELECT count(*) AS rows_seen, count(*) FILTER (WHERE clerk_id <> '') AS foreign_rows FROM public.; -- MUST be user_a's count above, and foreign_rows MUST be 0. ROLLBACK; BEGIN; SET LOCAL request.jwt.claims = '{"sub": ""}'; SET LOCAL ROLE authenticated; SELECT count(*) AS rows_seen, count(*) FILTER (WHERE clerk_id <> '') AS foreign_rows FROM public.; -- MUST be user_b's count above, and foreign_rows MUST be 0. ROLLBACK; ``` **Pass** = each identity sees exactly its own count AND `foreign_rows = 0`. **Fail** = the two identities return the same number, either equals the fleet total, or `foreign_rows > 0`. Counts alone are not sufficient: a view can return another tenant's rows while the totals still look right, so the ownership check is what actually proves scoping. Pick two users with clearly different counts, so an accidental match cannot hide a bypass. A suspiciously large count (hundreds of thousands of rows, nine-figure dollar aggregates) from a per-user view is the leak signature — treat it as a failed check, not as "the user has a lot of data". ## For reviewers (human and CodeRabbit) Flag a PR when any of these hold: * 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. This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.