# Rls Enforce

> Harden a Supabase/Postgres database by enforcing Row-Level Security — enable RLS, apply battle-tested policy patterns, and close every gap, as revert-safe migrations. Runs in parity after an RLS audit. Use for "enforce RLS", "harden the database", "lock down the tables", "apply RLS policies".

- Skill: `danielimad/rls-enforce` (Agent Skill)
- Install (CLI): `npx skillmds@latest add danielimad/rls-enforce`
- Raw SKILL.md: https://api.skillmd.com/api/skills/danielimad/rls-enforce/raw
- Safety review: pending
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: Security
- Author: danielimad (https://skillmd.com/u/danielimad)
- Updated: 2026-09-17
- Page: https://skillmd.com/skills/danielimad/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.