Rls Enforce

RLS Enforce — apply Row-Level Security, correctly

Fixes what /rls-audit finds. Parity partner: /rls-audit enumerates the anon surface and emits findings (surface · table_class · gap · severity · reproduction); this skill consumes exactly that list and closes each. If run standalone (no prior audit), do the coverage scan in Step 0 to produce the same findings shape first. Postgres/Supabase-generic; no assumptions about the schema beyond what you read from it.

Open-source note: this skill is project-agnostic. It reads the target DB's own schema and applies general RLS best practice. Nothing project-specific is hardcoded.

Step 0 — Ingest or map

1. If /rls-audit findings exist, take them as the work list — each finding already carries surface · table_class · gap · severity. Skip re-discovery; go to the plan.
2. If standalone, run the coverage scan to build the same findings: for every base table in public, is RLS enabled (pg_class.relrowsecurity), does any write policy expose anon/authenticated/public (pg_policies), any PII/cost column client-reachable, any world-callable SECURITY DEFINER function? Classify each table: owner-scoped · reference/config · server-internal · public-catalog.
3. Map each gap → the pattern in Step 1 that closes it. Present the plan (finding → migration) and WAIT for green. Nothing applied unsolicited.

Step 1 — Apply the pattern library (pick per table class)

Each becomes a migration. Generalized shapes (<table>, <owner_col>):

• Default-deny baseline — ALTER TABLE <table> ENABLE ROW LEVEL SECURITY; + FORCE ROW LEVEL SECURITY on owner/PII tables. No policy = denied; you only grant narrow exceptions.
• Owner-scoped CRUD — one policy per command, (SELECT auth.uid()) = <owner_col> (wrap auth.uid() in a subselect so it's evaluated once, not per row). UPDATE carries BOTH USING and WITH CHECK — USING gates which rows you may touch, WITH CHECK stops rewriting a row into one you don't own.
• Open-read / closed-write — keep the public SELECT; replace permissive writes with admin/service_role-only FOR ALL … USING(is_admin()) WITH CHECK(is_admin()). For shared reference data (rates, settings).
• PII / cost / token lockdown — lock the base table to owner+admin; expose only a minimal projection via a view (security_invoker=false) or a redacting SECURITY DEFINER RPC that simply never selects the sensitive columns. The column list of the safe object IS the boundary.
• Public whitelist config — is_public boolean NOT NULL DEFAULT false; SELECT policy USING (is_public = true); flip only explicit safe rows true. Fail-closed: new rows private until whitelisted.
• Server-internal tables — RLS on, only a service_role policy (or none). anon/authenticated get zero rows; server code holding the service key is the only path.
• Definer-function gateway — client can't write the table; a SECURITY DEFINER function does, checking ownership/role inside. MANDATORY hygiene: SET search_path = <schema>, public (or '' + schema-qualified) to block search-path hijack, and REVOKE ALL ON FUNCTION … FROM PUBLIC, anon, authenticated (CREATE FUNCTION grants EXECUTE to PUBLIC by default) then GRANT EXECUTE to the exact role.
• DB-level rate/abuse guard (for abuse-prone endpoints) — a definer function logging attempts to an indexed (identifier, created_at) table, counting sliding windows, writing a security event + time-boxed ban (ON CONFLICT de-dupe) on trip, with a scheduled purge.
• Centralized admin check — one is_admin() predicate (membership table or verified JWT claim), referenced everywhere. Never trust a client-settable field or current_user.
• Pre-request edge gate (optional, high-security) — a db_pre_request hook rejecting API traffic without a fresh HMAC-signed edge token (secret in a vault, bounded replay window); internal roles exempt by JWT claim. Keep an easy unwire path for incidents.
• Ban/identity layer (optional) — resolve ip/device/user from request context via STABLE SECURITY DEFINER helpers; is_request_banned() with shadow/block modes + expiry, AND-able into policies.

Step 2 — Migration discipline (this is what makes it safe across 100+ projects)

• One change per file, each with a paired revert migration. In-repo, never DDL via MCP.
• Idempotent: DROP POLICY IF EXISTS before CREATE; IF NOT EXISTS on columns/policies — re-runnable cleanly.
• Deploy ordering: when tightening a read that a server path still needs, move that path to a service-role lookup FIRST, then lock the table — never break the legitimate consumer.
• Revoke default grants on every new function; pin search_path on every definer function; secrets from a vault, never literals.
• Apply only on green.

Step 3 — Verify (parity loop)

Re-run the coverage scan + re-test as anon and as a scratch self-registered user: confirm each gap is closed and no legitimate path broke. A dated enforcement report + the pass/fail checklist below, written to the same docs security folder as the audit findings (.docs/security/ by default, or the repo's runbook-named docs folder) so the audit→enforcement pair sits side by side.

The checklist a project must pass

1. Every base table has RLS enabled; owner/PII tables also FORCE.
2. No USING (true) for anon/authenticated on anything mutable or PII-bearing.
3. Owner tables: per-command policies on auth.uid() = <owner_col>; UPDATE has both predicates.
4. Reference/config: open-read, admin/service-write; public-config via explicit is_public whitelist (default false).
5. No PII/cost/token/secret column reachable by anon/authenticated — curated view or redacting RPC only.
6. Server-internal tables: service_role only (or no policy).
7. Every SECURITY DEFINER function pins search_path + revokes PUBLIC EXECUTE, grants only the intended role.
8. Direct writes go through vetted RPCs, not table grants.
9. Abuse-prone endpoints have a DB-level rate guard (windows, time-boxed bans, alert, purge).
10. Admin checked through one centralized predicate (membership/claim), never a client field.
11. A coverage self-check runs in CI/dashboard, flagging RLS-off tables or public-exposing write policies.
12. Migrations idempotent; read-tightening ships after the consumer is moved to a service path.

Rules: enforce on green only; no secrets in migrations/reports (pointers only); this hardens the user's OWN database — authorized defensive work.