Row level security is on and policies are correct

What this means

Passing means every table in your public schema has row level security enabled and no policy for the anon role is USING (true). Failing means either a table has RLS off, so the public key reads it freely, or a table has RLS on with a policy that lets everyone through. The second case looks protected in the dashboard and is not.

How we test it

This check runs on your own machine through the CLI: npx securealy check-rls, which reads SUPABASE_URL and SUPABASE_SERVICE_KEY from your environment, or a management token, and queries pg_policies and the table catalogue; nothing leaves your machine and the key is never printed. Pass is zero tables with rowsecurity false and zero anon policies with USING (true). Fail is either, and the check is a launch blocker. If spatial_ref_sys is your only RLS-off table, PostGIS owns it and you cannot alter it; record the exception.

Why it matters

A table with RLS on and a permissive policy is RLS off with extra steps. The anon key ships in every page's source and reaches the database API directly, so the policy is the whole defence for that path. In the Fix Kit's own audit, four tables with row level security off let that key read 493 rows and 17 business addresses straight through the database API, and endpoint gating never had a chance to help because the request never touched an endpoint.

The fix

The default has to be locked, with each opening justified by a policy you can read. The Fix Kit's templates/rls-baseline.sql (Module 3) enables RLS on every table in public, revokes the anon and authenticated roles' direct privileges on all tables and sequences, changes the default privileges so new tables start closed, and leaves the service role untouched for your server code.

1. Run it through the management API (no psql needed), using a personal access token stored in .env.local as SUPABASE_MGMT_TOKEN:

set -a; source .env.local; set +a
sql() { jq -Rs '{query: .}' | curl -s -X POST "https://api.supabase.com/v1/projects/$SUPABASE_PROJECT_REF/database/query" \
  -H "Authorization: Bearer $SUPABASE_MGMT_TOKEN" -H 'Content-Type: application/json' -d @-; }
sql < MODULE-03-lock-it-down/templates/rls-baseline.sql

Stop before running it on a live project: any page that reads a table from the browser with the anon key stops working until a policy exists for it. Most apps need none.

2. Take the permissive policies the CLI named. For each, decide whether the table is public on purpose. If it is, keep the policy, name it so ("TABLE_NAME: public read, intended", the 4c template in the SQL file) and list the table in templates/public-tables.txt. If it is not, drop the policy and write the narrowest one that serves the page:

grant select on public.TABLE_NAME to authenticated;
create policy "TABLE_NAME: read own rows" on public.TABLE_NAME
  for select to authenticated
  using (user_id = auth.uid());

Both lines, every time: a policy without a grant does nothing and a grant without a policy opens nothing. Writes from the browser are the exception; the server writes with the service key after the gate has run.

3. Tell the API layer, because new grants are invisible until it reloads:

notify pgrst, 'reload schema';

4. Verify from the catalogue, then with the public key, then with the CLI:

echo "select tablename from pg_tables where schemaname='public' and not rowsecurity;" | sql
bash MODULE-03-lock-it-down/templates/audit.sh rls
npx securealy check-rls

Expect [], then rls: 0 tables readable with the anon key, then zero findings. If spatial_ref_sys is listed, back it up to a schema the public roles cannot reach (section 5 of the SQL file) and record the exception. Do not flip a security definer view to invoker without a reason; a view that serves a public page from locked tables goes blank the moment it runs as the caller.

A deeper runbook for this class is in the Fix Kit, Module 03.

Prompt for your AI

Paste this into the coding agent that built the app.

Run `npx securealy check-rls` with SUPABASE_URL and SUPABASE_SERVICE_KEY loaded from .env.local and show me the output; never print either value.
For each table it lists with RLS off, and each policy it flags as USING (true) for anon, tell me what the table holds and whether the client code reads it directly (grep for .from('TABLE').
Read MODULE-03-lock-it-down/templates/rls-baseline.sql and propose, per table, either "public on purpose" or the narrowest select policy from section 4.
STOP. Show me that list and wait for my yes; the baseline changes a live database and can blank a page that reads a table from the browser.
After my yes, run the baseline through the sql() helper from Module 3 step 4, apply only the policies I approved, then run notify pgrst, 'reload schema'.
Run `npx securealy check-rls` once more and show me the output; expected: zero tables with RLS off and zero anon policies with USING (true).

Copy the prompt