Row level security (RLS) is a PostgreSQL feature that decides, row by row, which rows a user may read or change, and the rule runs inside the database. In a Supabase app, RLS is the only thing between your tables and any visitor who copies the public key out of your browser. If a table has no RLS, anyone can read or edit it through the public API.
This guide is for people who build web apps with an AI builder and want to understand what the generated policies do, so they can check them. It covers the four policy types, the usual patterns, storage, and a two-account test that takes ten minutes.
Why RLS is mandatory when the key is in the browser
A Supabase web app talks to the database straight from the browser, so the project URL and the public (anon or publishable) key ship inside the JavaScript that every visitor downloads. That is by design: the key identifies your project, it does not protect it. Protection comes from the policies on each table.
Supabase's documentation describes a policy as an extra WHERE clause added to every query, and notes that once RLS is enabled on a table, no data is accessible through the API with the public key until you create policies. The reverse is the danger: a table in the public schema without RLS is open. Anyone can open the browser console, call your API with your own public key, and run select, insert, update or delete on it.
The secret key (service role) is a different thing. It bypasses RLS, so it must never appear in client code. Secrets belong in server code such as edge functions. Broken access control, where a user reaches data that is not theirs, is the first category of the OWASP Top 10.
What happens without RLS
Without RLS, the app still works in your own tests, which is why the problem goes unnoticed. The screens only ask for your own rows. The API does not care what the screens ask for.
- A visitor lists every row of the orders, clients or messages table.
- A signed-in user changes the price, the role or the owner of a row that belongs to someone else.
- A script deletes a whole table, with no account at all.
Hiding a button in the interface protects nothing, since the API accepts any request the key allows. If you are comparing backends, our guide on Supabase vs Firebase covers how each one handles access rules.
The four policy types: select, insert, update, delete
A policy applies to one operation, and each operation uses one or two conditions. USING filters the rows that already exist. WITH CHECK validates the rows being written. Supabase's documentation recommends one policy per operation.
| Operation | Condition used | What it controls |
|---|---|---|
| SELECT | USING | Which existing rows the user can read |
| INSERT | WITH CHECK | Whether the new row is allowed |
| UPDATE | USING and WITH CHECK | Which rows can be edited, and what they may become. It also needs a SELECT policy |
| DELETE | USING | Which rows can be removed |
Here is a complete example for a todos table where each row has a user_id column. First, turn RLS on:
alter table public.todos enable row level security;Then one policy per operation, each limited with the TO clause to signed-in users:
create policy "Read own todos"
on public.todos for select
to authenticated
using ( (select auth.uid()) = user_id );
create policy "Create own todos"
on public.todos for insert
to authenticated
with check ( (select auth.uid()) = user_id );
create policy "Update own todos"
on public.todos for update
to authenticated
using ( (select auth.uid()) = user_id )
with check ( (select auth.uid()) = user_id );
create policy "Delete own todos"
on public.todos for delete
to authenticated
using ( (select auth.uid()) = user_id );Two details matter. The TO clause names the role: without it, the policy is also evaluated for anonymous requests (the anon role). And auth.uid() returns the ID of the signed-in user, or null if nobody is signed in, in which case the comparison silently fails and no row matches. Wrapping the call as (select auth.uid()) lets Postgres compute it once per statement instead of once per row, which Supabase recommends for speed.
Pattern 1: rows that belong to one user
The per-user pattern is a user_id column filled with the signed-in user's ID, plus the four policies above. It fits notes, tasks, profiles, settings and any private data. A useful default for the column is auth.uid(), so the browser does not have to send it:
user_id uuid not null default auth.uid()
references auth.users (id) on delete cascadeIndex the column your policies filter on, since the policy runs on every query:
create index todos_user_id_idx on public.todos (user_id);Pattern 2: teams and organisations
For shared data, store membership in its own table and let the policy ask whether the user belongs to the row's team. A members table with team_id and user_id is enough:
create policy "Members read team projects"
on public.projects for select
to authenticated
using (
team_id in (
select team_id from public.team_members
where user_id = (select auth.uid())
)
);Write policies are stricter than read policies in most apps: members may read, only some may insert or delete. Put a role column on team_members and check it in the insert, update and delete policies. Index team_members on user_id, because a composite primary key that starts with team_id does not index user_id.
Pattern 3: admin roles
An admin policy should read the role from a place the user cannot edit. Supabase's documentation warns that raw_user_meta_data can be changed by the user, so it must not be used for authorization, while raw_app_meta_data (app_metadata in the JWT) can be trusted. The simpler and more common route is a table of admins that only the database owner writes to:
create policy "Admins read all invoices"
on public.invoices for select
to authenticated
using (
exists (
select 1 from public.admins
where user_id = (select auth.uid())
)
);Policies for the same operation are combined with OR, so this policy adds admin access on top of the per-user one. Keep the admins table locked down with its own RLS and no insert policy, otherwise a user could make themselves an admin.
Storage policies for uploaded files
Files in Supabase Storage are protected by RLS policies on the storage.objects table, and a bucket accepts no upload until you add a policy. A common layout stores each user's files in a folder named after their user ID, and the policy checks the first folder of the path:
create policy "Upload to own folder"
on storage.objects for insert
to authenticated
with check (
bucket_id = 'documents'
and (storage.foldername(name))[1] = (select auth.uid())::text
);
create policy "Read own files"
on storage.objects for select
to authenticated
using (
bucket_id = 'documents'
and (storage.foldername(name))[1] = (select auth.uid())::text
);Overwriting a file (upsert) needs insert, select and update policies together. Objects in a public bucket are readable by anyone with the link, so keep invoices, contracts and ID documents in private buckets. See also the Supabase page on Storage access control.
How to test your policies with two accounts
The only test that proves RLS works is to sign in as one user and try to reach another user's data. The SQL editor does not count: it runs with elevated rights and ignores your policies. Use the app itself, or the API with a real user session.
- Create two accounts, A and B, and add a few rows with each.
- Signed in as A, confirm that the list shows only A's rows.
- Still as A, call the API directly for B's rows (a select with B's user_id, an update on one of B's row IDs, a delete). Each must return nothing or fail.
- Try to insert a row with B's user_id. The insert policy must refuse it.
- Open the app in a private window, signed out, and request the same tables. Private tables must return nothing.
- Repeat the file steps: A must not be able to download B's file by its path.
Common mistakes
| Mistake | Result | Fix |
|---|---|---|
| RLS never enabled on a table | The table is open to everyone with the public key | Enable RLS on every table in the public schema |
| Policy with no TO clause | Evaluated for anonymous requests too | Add to authenticated |
| using (true) | Every row is visible to the role | Keep it only for data meant to be public |
| Update policy without a select policy | Updates silently change nothing | Add a matching select policy |
| Role read from user metadata | Users can grant themselves the role | Use app_metadata or an admins table |
| View over a protected table | Views bypass RLS by default | On Postgres 15 and later, create it with security_invoker = true |
| Secret key in client code | Bypasses every policy | Keep it in server code only |
How Cadrant handles it
Cadrant, our builder, generates React web apps on a Supabase project in your own Supabase account. When the AI creates a table, it writes the migration with RLS policies for per-user tables, storage policies included, and it can inspect the current schema and policies before changing them. Secret keys stay out of client code: integrations such as Stripe run through edge functions, with secrets stored as environment variables. Setup is described in the Supabase connection docs and the guide to AI builders with Supabase.
The AI writes the policies from what you say, so say it: "a client sees only their own invoices, the manager sees all". The prompt examples in our web app prompt examples show how. Generated policies are a good first draft, not a guarantee. Always run the two-account test before you share the app with real users. Cadrant, like any AI builder, can get a rule wrong, and the test is how you find out.