Skip to content

Insights

How do you check Supabase row-level security in an AI-built app?

By Rare Agentics engineering teamLast updated First published 9 minute read

Check that row-level security is switched on for every table in an exposed schema, read every policy to see who it actually lets in, and then test it by calling the API yourself as an anonymous visitor and as two different signed-in users. AI tools often leave tables open or write policies that allow everything, because the app still works on screen. The browser key is public by design, so these policies are the only thing between a stranger and your data.

Why does row-level security matter so much in a Supabase app?

A Supabase app talks to its database straight from the browser. The key that makes that possible, the anon key (called the publishable key in newer projects), ships in your front-end code. Anyone can open the browser's developer tools, copy it and call your database's API directly, without your screens in the way.

That is how Supabase is meant to work. The key identifies the project, not the person. What decides who can read or change a row is row-level security: a policy on each table that checks who is asking. If a table has row-level security switched off, or a policy that lets everyone in, the key is all a stranger needs.

The screens hide the problem. Your app may only ever show a user their own invoices, so it looks right in testing. But the screen is a filter you chose to apply. The API will return whatever the policies allow, to whoever asks.

Why do AI tools so often get it wrong?

Tools such as Lovable, Bolt, Cursor, Replit and v0 are good at producing an app that works on the happy path. Row-level security fails in the opposite direction: when it is too tight, the app breaks and the tool notices; when it is too loose, nothing visibly breaks, so nothing prompts a fix.

The patterns we see most:

  • Security switched off to make an error go away. A query fails with a permissions error, and the quickest change that makes it pass is to disable row-level security on the table.
  • Policies that allow everything. A policy whose rule is simply true, which makes a table look protected while protecting nothing.
  • Tables added later with no policy. The first tables were set up carefully; the ones added in week three were not.
  • Logic moved into views and functions that quietly run with more privilege than the user has.

None of this means the tool was a bad choice. It means the access rules need a person to read them before real customer data goes in.

How do you check row-level security is switched on for every table?

Start with the database itself. In the SQL editor, list every table in the public schema (and any other schema your API exposes) with its row-level security setting:

select schemaname, tablename, rowsecurity
from pg_tables
where schemaname = 'public'
order by tablename;

Every row should say true. Any false is a table the API serves to anyone with the key, subject only to ordinary grants, which Supabase gives broadly by default.

Supabase's own Security Advisor in the dashboard flags tables in the public schema without row-level security too. Run it, but do not stop there: it catches missing row-level security and several risky patterns, but it cannot tell you whether a policy lets in the right people.

How do you read the policies to see who they let in?

Next, list every policy:

select tablename, policyname, cmd, roles, qual, with_check
from pg_policies
where schemaname = 'public'
order by tablename, cmd;

qual is the using rule, which decides which existing rows a user can see or touch. with_check decides which new or changed rows they are allowed to write. Read each one and ask, in plain words, "who does this let in?"

A good policy for a table of private notes reads something like: a signed-in user can see a row when the row's owner is them.

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

A table with row-level security on and no policies denies everything except the service role, which is safe. A table with a policy whose rule is true allows everything for the roles it names, which is not.

What are the most common row-level security mistakes?

MistakeWhy it is a problemWhat to do instead
Row-level security off on a tableAnyone with the public key can read and often write the whole tableSwitch it on, then add the narrowest policies the app needs
A policy using trueIt looks protected but lets every user, or every visitor, inWrite a rule that checks who owns or may see the row
A policy with no to clause, or granted to anonA policy with no to clause applies to every role, including visitors who never signed inGrant read policies to authenticated unless the data is genuinely public
Anonymous sign-ins treated as real usersWith anonymous sign-ins on, those visitors get the authenticated role, so "to authenticated" lets them inCheck the is_anonymous claim in policies that should need a real account, or turn anonymous sign-ins off
An insert policy with with check (true)A user can create rows that belong to someone elseCheck the new row's owner is the user: with check ((select auth.uid()) = user_id)
Owners can update every columnRow-level security works on rows, not columns, so a user can edit their own role or plan fieldKeep privileged fields in a separate table only the server writes, or restrict column privileges
Policies that trust user_metadataUsers can edit their own user metadata from the browser, so they can grant themselves a rolePut roles in app_metadata, which only the server can set (it reaches the token at the next refresh), or in your own table
A view without security_invokerViews run with their owner's rights by default, which bypasses row-level securityCreate views with (security_invoker = true), or keep them out of exposed schemas
security definer functions in an exposed schemaThey run with their owner's rights and can be called through the APIKeep them in a schema the API does not expose, set a fixed search_path, and revoke execute from public, anon and authenticated (revoke execute on function ... from public, anon, authenticated;)
The service role or secret key in the browserIt bypasses row-level security entirelyUse it only on the server, rotate it if it ever shipped, and check the repository history
Open storage bucketsA public bucket serves any file to anyone with the linkMake buckets private and write policies on storage.objects

Two of those deserve a little more explanation.

The update trap. Row-level security answers "which rows may this person touch?", not "which fields?". If users can update their own profile row, they can update every column in it unless you stop them. If that row also holds is_admin, plan or credit_balance, a user can promote themselves with one API call. The fix is structural: privileged fields belong somewhere the user cannot write.

Realtime and storage. These have their own rules. Check which tables are published for realtime changes and that subscribers can only receive rows the policies allow, and that Broadcast and Presence use private channels, which are governed by policies on realtime.messages, because a public channel is open to anyone with the key. Check storage separately from the database: bucket settings and storage.objects policies decide who can read and upload files.

How do you test it as a real user and as a visitor?

Reading policies tells you what should happen. Calling the API tells you what does. You need the project URL, the public key and two test accounts.

First, as an anonymous visitor, sending only the public key in the apikey header:

curl "https://YOUR-PROJECT.supabase.co/rest/v1/notes?select=*" \
  -H "apikey: $PUBLIC_KEY"

For a private table, the answer should be an empty list or a permissions error, never rows.

Then sign in as test user A, take that session's access token, for example from POST /auth/v1/token?grant_type=password with the test user's email and password, and repeat the call with Authorization: Bearer $USER_A_TOKEN. You should see A's rows and nobody else's. Try to read one of user B's rows by its id, update it, and insert a row with B's id as the owner. The read should return an empty list. The update will usually still return a success code, because row-level security hides the row rather than raising an error, so send Prefer: return=representation and check that the response is empty, then confirm as B that nothing changed. The insert should fail with a "new row violates row-level security policy" error.

Do this for every table that holds anything personal or commercial, not only the obvious ones. The table you forgot is the one that leaks.

How do you stop it breaking again in the next release?

A manual check is a snapshot. The next feature, especially one generated in a hurry, can undo it. The answer is to turn the checks into tests that run on every change.

Supabase supports database tests written with pgTAP, run with supabase test db. A test seeds rows for two users, acts as one of them and asserts what they can and cannot see. Seed both users, or the test passes on an empty table:

begin;
create extension if not exists pgtap with schema extensions;
select plan(3);

-- Seed as the table owner, before switching role
insert into public.notes (id, user_id, body) values
  ('00000000-0000-0000-0000-0000000000a1', '00000000-0000-0000-0000-00000000000a', 'A'),
  ('00000000-0000-0000-0000-0000000000b1', '00000000-0000-0000-0000-00000000000b', 'B');

set local role authenticated;
select set_config('request.jwt.claims', '{"sub":"00000000-0000-0000-0000-00000000000a","role":"authenticated"}', true);

select isnt_empty($$ select id from public.notes $$, 'User A sees their own notes');
select is_empty($$ select id from public.notes where user_id <> '00000000-0000-0000-0000-00000000000a' $$, 'User A sees nobody else''s notes');

reset role;
select is_empty(
  $$ select tablename from pg_tables where schemaname = 'public' and not rowsecurity $$,
  'Every public table has row-level security on'
);

select * from finish();
rollback;

Add one test for each rule that must never break: a user cannot read another user's rows, a visitor cannot read private tables, nobody but the server can change a role. Add a test that fails if any table in the public schema has row-level security switched off. Run them in your deployment pipeline so a release that breaks them never reaches production. We run tests like these on our own Supabase builds for exactly this reason.

What checklist should you run before launch?

CheckDone when
Row-level security on every exposed tableThe pg_tables query shows true for every row
Every policy read and understoodYou can say in one sentence who each policy lets in
No policy that is simply true on private dataNone in the pg_policies output
No privileged fields users can writeRoles, plans and balances live where only the server writes
No roles in user_metadataPolicies use app_metadata or your own tables
Views use security_invokerOr sit outside exposed schemas
No callable security definer functions you did not mean to exposeExecute revoked from public, anon and authenticated, search_path fixed
Secret keys only on the serverNot in front-end code, not in the repository history
Storage buckets private by defaultPolicies on storage.objects for each bucket
Tested as a visitor and as two usersEvery cross-user read and write fails
Tests in the pipelineA release that opens a table cannot ship

If any row is not done, the app is not ready for real customer data. Under UK GDPR, a leak of personal data can mean reporting the breach to the ICO within 72 hours and, where the risk to them is high, telling the people affected, which is a far harder conversation than a delayed launch.

How Rare Agentics can help

Row-level security is one of the first things our security review for AI-built apps checks, alongside keys, sign-in and recovery, rate limits and what leaves your systems for AI providers. Every finding is reproduced and ranked, we fix what we find or guide your team, every fix gets a test, and a second pass confirms it. It is part of our security and hardening service, takes 1 to 3 weeks and is quoted to scope.

For the wider picture beyond the database, read how to make an app built with AI tools safe before launch. If you are not sure whether you need a review or a penetration test, our comparison of the two sets out when each is right. Or tell us about your app and you will get a first view within two working days.

Related reading

Related services

Start a conversation

Built it fast with AI tools? Send us the app.

Tell us what it does and what it was built with. We will tell you what we would check first, before a launch, a raise or a sale. Within two working days you get a first view, and within a week a written plan, a fixed price and the risks.

See what a security review covers

In plain language: what you want to exist, who it is for, and anything that worries you. It does not need to be technical.

Read by a senior engineer. Used only to reply to you.

Tell us what you need built.