Based on each vendor’s official docs, checked November 1, 2026

Supabase RLS for AI-built apps: grants, policies and tests

Coding agents create Supabase tables fast, and a table in an exposed schema without Row Level Security is readable and writable by any role with a grant on it. Supabase’s own docs put it plainly: enable RLS on every table in an exposed schema, and on projects that still grant anon and authenticated by default, revoke those grants, because adding policies doesn’t remove them. This guide follows Supabase’s Row Level Security docs, checked November 1, 2026, and turns them into steps you can hand to Claude Code, Codex, Cursor or Copilot.

1. 1. Enable RLS on the table

   Run this in the SQL Editor or, better, in a migration so it is reproducible. Once RLS is on, nothing is readable through the API with a publishable key until you add policies.

   alter table public.reports enable row level security;

2. 2. Revoke the default grants, then grant back only what is needed

   On existing projects a new table in public starts with every privilege granted to anon, authenticated and service_role. Policies decide which rows; grants decide whether a role can run the operation at all. Keep grants and RLS in the same migration.

   revoke all on table public.reports from anon, authenticated;
   grant select, insert, update, delete on table public.reports to authenticated;

3. 3. Write one policy per operation

   Supabase recommends a separate policy for select, insert, update and delete, each naming its role with to. Insert uses with check; update uses both using (which rows) and with check (what the new row may look like), and needs a matching select policy to work.

   create policy "Users can view their own profile."
   on profiles for select
   to authenticated
   using ( (select auth.uid()) = user_id );
   
   create policy "Users can update their own profile."
   on profiles for update
   to authenticated
   using ( (select auth.uid()) = user_id )
   with check ( (select auth.uid()) = user_id );

4. 4. Prove it with a pgTAP test

   Create one test file per table under supabase/tests/ that asserts allow and deny for each operation, for anon and authenticated, then run the suite. Supabase’s point: until the suite passes, you don’t know whether the policies do what you intended.

   supabase test new reports_rls
   supabase test db

Why AI-built apps hit this

An agent asked to “add a todos table” will usually create the table and the client code that reads it, and the app works in the browser. Working is not the same as safe: with default grants and no RLS, the same publishable key in your frontend can read or write every row. Ask your agent to treat enable RLS, revoke, grant and policies as part of every table it creates, in the same migration.

How denials show up in tests

A missing grant or a with check violation raises error 42501. A using clause that filters a row out raises nothing and simply matches zero rows, so assert those with is_empty on the statement with returning, then read the target row to show it is intact. Supabase warns never to prove an allowed write with lives_ok, because it passes when the write matched zero rows.

Mistakes the docs call out

Views bypass RLS by default because they are created by the postgres user; on Postgres 15 and above create them with security_invoker = true. Don’t base policies on user_metadata in the JWT, since signed-in users can change it; store authorization data in app_metadata. auth.uid() returns null for signed-out requests, so make that case explicit. Two tables whose policies read each other fail with 42P17 infinite recursion; break the cycle with a security definer function in a private schema with search_path set to an empty string.

Keep the secret key on the server

A secret key uses the service_role role, which bypasses RLS, so it belongs only in server code, never in the browser. The JWT-based service_role key is legacy; Supabase prefers secret keys. A secret key bypasses RLS only when the request carries no user access token; with a user token, the user’s policies apply.

Keep policies fast

Index every column a policy filters on, such as user_id. Wrap helper calls as (select auth.uid()) so Postgres evaluates them once per statement instead of once per row.

Tips

• Prompt your agent: “For every new table, write one migration that enables RLS, revokes anon and authenticated, grants only what the app needs, adds one policy per operation and a pgTAP test file.”
• Check grants before editing a policy when a request your policy should allow fails with 42501.
• Never let an agent paste a secret key into frontend code or a committed .env file.

Next steps

Sources

• https://supabase.com/docs/guides/database/postgres/row-level-security · checked 2026-11-01