The Supabase row-level security mistakes in almost every AI-generated app

We audit AI-built applications for a living, and row-level security is the finding we file more often than any other. Not occasionally. Most Supabase projects that reach us have at least one table that any visitor can read, and a meaningful share have tables that any visitor can write. The apps look fine. They pass every manual test the founder ran, because the founder was always logged in as themselves.

This is not a criticism of the builders. Lovable, Bolt, v0 and Claude Code all generate reasonable Supabase code. What they cannot do is know your tenancy model, and a policy generator that has to guess will guess permissively, because a permissive policy makes the demo work. The demo working is the whole problem.

What the anon key actually is

The Supabase anon key is public by design. It ships in your JavaScript bundle, it is visible in devtools, and it is supposed to be. It is a signed JWT whose only meaningful claim is a Postgres role: anon. Every unauthenticated request to your PostgREST endpoint arrives as that role. The key does not grant access; it identifies a role, and row-level security decides what that role can see.

That means RLS is not a hardening step you add later. It is the entire authorization layer. If it is missing or permissive, your database is a public API with a nice client library in front of it.

Mistake 1: RLS was never enabled on the table

Tables created through the SQL editor, through a migration written by an agent, or through a raw create table do not have row-level security on by default. The Supabase dashboard shows a warning, but the warning lives on a screen nobody visits after the first week. Run this against your own database before reading further:

select
  c.relname                as table_name,
  c.relrowsecurity         as rls_enabled,
  c.relforcerowsecurity    as rls_forced,
  (select count(*) from pg_policies p
     where p.schemaname = 'public' and p.tablename = c.relname) as policy_count
from pg_class c
join pg_namespace n on n.oid = c.relnamespace
where n.nspname = 'public'
  and c.relkind = 'r'
order by c.relrowsecurity asc, c.relname;

Every row where rls_enabled is false is world-readable and world-writable through the REST API. Rows where RLS is on and policy_count is zero are safe: Postgres denies by default, which is the correct failure mode. A table with RLS off is the dangerous one, and it is invisible from inside the app.

Mistake 2: the USING (true) trap

This is the policy we see most, usually with a name that reads like a description of a bug report:

-- Generated. Reviewed. Shipped. Wrong.
create policy "Enable read access for all users"
on public.profiles
for select
using (true);

It is not subtle, and that is exactly why it survives review. The name says what it does, RLS is enabled, the dashboard shows a green shield, and the app works. Everyone moves on. Meanwhile every row of profiles, including email addresses and Stripe customer ids, is one curl away.

The near miss is worse because it looks defensive:

create policy "Authenticated users can read"
on public.orders
for select
using (auth.role() = 'authenticated');

That reads like a security control. It is not. If your app has open signup, anyone can become an authenticated user in fifteen seconds and then read every order in the table, including other customers' addresses and totals. Any policy whose predicate does not reference the current user's identity is a public read with extra steps.

Mistake 3: authorization data the user can edit

The second most common pattern: a profiles table with a role column, a policy letting users update their own profile row, and admin checks written against that column.

-- profiles(id uuid, display_name text, role text default 'user')

create policy "Users can update own profile"
on public.profiles for update
to authenticated
using (id = auth.uid());

-- elsewhere
create policy "Admins can read everything"
on public.invoices for select
to authenticated
using (
  exists (select 1 from public.profiles
          where id = auth.uid() and role = 'admin')
);

A user can update their own profile row. Their own profile row contains the role column. One PATCH request and they are an admin. Privilege escalation, three lines of policy, no exploit required.

The fix is structural: roles live in a table users cannot write, and the check goes through a stable function so the policy stays readable.

create table public.user_roles (
  user_id uuid primary key references auth.users(id) on delete cascade,
  role    text not null check (role in ('user','staff','admin'))
);

alter table public.user_roles enable row level security;
-- No insert/update/delete policy at all. Only the service role writes here.

create policy "read own role"
on public.user_roles for select
to authenticated
using (user_id = (select auth.uid()));

create or replace function public.has_role(target text)
returns boolean
language sql
stable
security definer
set search_path = public
as $$
  select exists (
    select 1 from public.user_roles
    where user_id = auth.uid() and role = target
  );
$$;

Mistake 4: views that quietly bypass RLS

Views in Postgres historically execute with the permissions of the view owner, which is usually postgres. A view over a locked-down table returns everything, because RLS on the underlying table is evaluated as the owner. Agents love views: they are an easy way to flatten a join for the frontend, and they punch straight through your policies.

-- Find views that run as their owner
select schemaname, viewname,
       (select option_value from pg_options_to_table(c.reloptions)
        where option_name = 'security_invoker') as security_invoker
from pg_views v
join pg_class c on c.relname = v.viewname
where schemaname = 'public';

-- Fix: evaluate policies as the caller
alter view public.order_summary set (security_invoker = on);

Mistake 5: correct policies that make the app slow

Two performance traps sit inside otherwise correct policies. First, calling auth.uid() bare means Postgres re-evaluates it per row. Wrapping it in a subselect lets the planner hoist it into an initplan and evaluate it once. On a hundred-thousand-row table this is the difference between 4ms and 900ms.

Second, a policy predicate is a filter, and filters need indexes like any other. A policy on owner_id with no index on owner_id turns every query into a sequential scan.

What a correct policy set looks like

alter table public.documents enable row level security;

create policy "documents_select_own"
on public.documents for select
to authenticated
using (owner_id = (select auth.uid()));

create policy "documents_insert_own"
on public.documents for insert
to authenticated
with check (owner_id = (select auth.uid()));

create policy "documents_update_own"
on public.documents for update
to authenticated
using      (owner_id = (select auth.uid()))
with check (owner_id = (select auth.uid()));

create policy "documents_delete_own"
on public.documents for delete
to authenticated
using (owner_id = (select auth.uid()));

create index if not exists documents_owner_id_idx
  on public.documents (owner_id);

Three details are doing real work here. to authenticated keeps the policy off the anon role, where to public would include it. The separate with check on update stops a user from reassigning owner_id to somebody else while updating a row they legitimately own. And the index makes the predicate cheap.

Testing policies per role, in SQL

You can impersonate a role inside a transaction and roll it back. This is the fastest feedback loop available and it belongs in your migration workflow, not in a manual checklist.

begin;

select set_config(
  'request.jwt.claims',
  json_build_object(
    'sub',  '11111111-1111-1111-1111-111111111111',
    'role', 'authenticated'
  )::text,
  true
);
set local role authenticated;

-- Expect: only this user's rows.
select count(*) from public.documents;

-- Expect: 0 rows changed, or an error.
update public.documents set title = 'nope' where owner_id <> auth.uid();

rollback;

Do the same with set local role anon and no claims at all. Every table should return zero rows for anon unless you deliberately intend it to be public, and "deliberately" means someone wrote it down.

Verifying from the outside

SQL tests prove what the database thinks. The client test proves what the internet can reach. Take your anon key out of the deployed bundle and hit the REST API with no session:

PROJECT=your-project-ref
ANON=eyJhbGciOi...   # straight out of the shipped bundle

# List every table the API exposes
curl -s "https://$PROJECT.supabase.co/rest/v1/" -H "apikey: $ANON" | head -50

# Then, for each one, ask for rows as an anonymous visitor
curl -s "https://$PROJECT.supabase.co/rest/v1/profiles?select=*&limit=5" \
  -H "apikey: $ANON"

Keeping it fixed

• Policies live in migration files in git, never only in the dashboard. A policy you cannot diff is a policy nobody reviews.
• Add a CI step that fails the build if any table in public has rls_enabled = false. It is a ten-line query and it catches the next regression for free.
• Run the Supabase Security Advisor after every schema change, and treat its findings as build failures rather than suggestions.
• Use separate Supabase projects for development and production. Sharing one project means every experiment runs against customer data.
• Write the tenancy rule down in one sentence per table: who may read this row, who may write it. Policies that cannot be summarised in a sentence are usually wrong.

None of this is hard. It is just work that nobody schedules, because the app already appears to function. The gap between "appears to function" and "safe to put in front of real users" is where we spend our days, and on a Supabase app built with an AI tool, row-level security is almost always the first thing in it.