Supabase Row Level Security: a practical guide with SQL
Row Level Security (RLS) is a Postgres feature that decides, row by row, what each database role can read or change. In Supabase it is the main access control, because the browser calls the database API directly with a public key. Enable RLS on every table in an exposed schema, write one policy per operation that compares the row's owner to auth.uid(), and test each policy as an anonymous user.

What Row Level Security does
Postgres normally controls access per table: a role can select from orders or it cannot. Row Level Security adds a filter per row. A policy is a SQL expression attached to a table. Postgres evaluates it for every row a query touches and hides or rejects rows where it is not true.
In a traditional app, your server checks permissions before it queries the database. In Supabase, the browser or mobile app calls the auto-generated API directly using a publishable key (the anon key in older projects). That key is public. RLS is the layer that stands between that key and your data, which is why missing policies are the first item in our list of vibe coding security risks.
Supabase maps each request to a Postgres role:
| Role | When it is used | RLS applies |
|---|---|---|
anon |
Request with the publishable key and no user token | Yes |
authenticated |
Request with a logged-in user’s token | Yes |
service_role |
Request with the secret key | No, it bypasses RLS |
Enable RLS on every table
create table public.documents (
id uuid primary key default gen_random_uuid(),
user_id uuid not null default auth.uid() references auth.users (id),
title text not null,
created_at timestamptz not null default now()
);
alter table public.documents enable row level security;
Once RLS is enabled and no policy exists, the API returns nothing for this table. That is the safe starting state. Tables created through a SQL migration do not get RLS unless the migration says so, so do not assume it is on. This query lists the tables that are missing it:
select tablename
from pg_tables
where schemaname = 'public' and rowsecurity = false;
Write policies with auth.uid()
auth.uid() returns the ID of the user making the request, taken from their token. A policy compares it to an owner column.
create policy "Users read own documents"
on public.documents for select
to authenticated
using ( (select auth.uid()) = user_id );
create policy "Users insert own documents"
on public.documents for insert
to authenticated
with check ( (select auth.uid()) = user_id );
create policy "Users update own documents"
on public.documents for update
to authenticated
using ( (select auth.uid()) = user_id )
with check ( (select auth.uid()) = user_id );
create policy "Users delete own documents"
on public.documents for delete
to authenticated
using ( (select auth.uid()) = user_id );
Four details in that SQL matter.
usingandwith checkdo different jobs.usingfilters existing rows for select, update and delete.with checkvalidates the new row for insert and update. An update policy withoutwith checkreuses theusingexpression for both.to authenticatednames the role. Without it, the policy also runs foranon. Naming the role is clearer and lets Postgres skip the policy for other roles.(select auth.uid())is a performance pattern. Wrapping the call in a select lets the planner evaluate it once per statement instead of once per row. Supabase recommends this for functions whose result does not depend on the row.- Update needs a select policy. Postgres has to read a row to update it, so an update policy without a matching select policy will not behave as expected.
Add an index on the column the policy filters on:
create index documents_user_id_idx on public.documents (user_id);
Common mistakes
Forgetting that auth.uid() can be null
For a request with no user token, auth.uid() returns null. In SQL, null = user_id is never true, so the row is filtered out and the caller gets an empty result with no error.
That behavior is safe, but it confuses people. The app shows no data, the builder or an AI agent “fixes” it by loosening the policy to using (true), and the table is now open to everyone. The right fix is to find out why the request has no session. To make the intent explicit, Supabase suggests writing the null check into the policy:
using ( auth.uid() is not null and auth.uid() = user_id )
The related mistake is a policy with to anon using (true). That grants every visitor read access to every row. Use it only for data that is public on purpose.
Views without security_invoker
A view runs with the privileges of the role that created it, usually postgres, which bypasses RLS. A view over a protected table can return every row even though the table’s policies are correct. On Postgres 15 and later:
create view public.document_titles
with (security_invoker = true)
as select id, title from public.documents;
For an existing view, alter view public.document_titles set (security_invoker = true); does the same. On older Postgres versions, revoke access to the view from anon and authenticated or move it to a schema the API does not expose.
Service role misuse
The secret key bypasses RLS. Three ways this goes wrong:
- The key is used in frontend code. Anyone can extract it and read the whole database.
- A server function creates a service-role client for every request, including ones made on behalf of a user. The function now has to re-implement every access rule by hand, and one missed check is a leak.
- The key sits in a public environment variable or in git history.
In server functions, create the Supabase client with the caller’s token so RLS still applies, and use the secret key only for specific administrative steps.
SECURITY DEFINER functions open to everyone
A security definer function runs as its owner and bypasses RLS. Postgres grants execute on new functions to all roles by default, and functions in the public schema are callable through the API. Lock them down:
revoke execute on function public.admin_report from public;
revoke execute on function public.admin_report from anon, authenticated;
grant execute on function public.admin_report to service_role;
Always add set search_path = '' to definer functions and schema-qualify every table name inside them.
Authorization data in user metadata
A logged-in user can edit their own user_metadata. A policy that reads a role or plan from it lets users promote themselves. Store authorization data in app_metadata, which users cannot change, or in a table.
Multi-tenant isolation
In a multi-tenant app, rows belong to an organization, and users belong to one or more organizations. Put a tenant_id on every tenant-owned table and check membership in the policy.
create table public.memberships (
tenant_id uuid not null,
user_id uuid not null references auth.users (id),
role text not null default 'member',
primary key (tenant_id, user_id)
);
alter table public.memberships enable row level security;
create schema if not exists private;
create function private.user_tenant_ids()
returns setof uuid
language sql
stable
security definer
set search_path = ''
as $$
select tenant_id
from public.memberships
where user_id = (select auth.uid());
$$;
grant usage on schema private to authenticated;
grant execute on function private.user_tenant_ids to authenticated;
create policy "Members read tenant projects"
on public.projects for select
to authenticated
using ( tenant_id in (select private.user_tenant_ids()) );
The helper function lives in a private schema that the API does not expose, so nobody can call it as an endpoint. It is security definer so that it can read memberships without triggering that table’s own policies, which avoids recursive policy errors.
You can also read tenant IDs from the token with auth.jwt() -> 'app_metadata'. It is faster, but a token is not always current: removing a user from a tenant does not take effect until their token refreshes. For anything that handles money, a table lookup is the safer choice. Our post on multi-tenant architecture compares this shared-schema model with schema-per-tenant and database-per-tenant.
Test policies as an anonymous user
A policy you have not tested is a guess. Test from two places.
In SQL. Switch role inside a transaction and run the query the attacker would run:
begin;
set local role anon;
select count(*) from public.documents; -- expect 0
rollback;
begin;
set local role authenticated;
set local request.jwt.claim.sub = '11111111-1111-1111-1111-111111111111';
select count(*) from public.documents; -- expect only this user's rows
rollback;
Over HTTP. Call the API with only the publishable key, the way a stranger would:
curl "https://YOUR-PROJECT-REF.supabase.co/rest/v1/documents?select=*" \
-H "apikey: YOUR_PUBLISHABLE_KEY"
An empty array is a pass. Rows are a finding. Then log in as user A and request user B’s rows by ID.
Turn these into automated tests. Supabase supports pgTAP tests run with supabase test db. In the marketplace platform Shai runs, with about 145 tables under RLS and 155 policies, an automated job probes sensitive functions as an anonymous user every day and closes any permission that should not be open. Manual testing catches today’s mistakes. A scheduled probe catches next month’s.
What to do next
Run the missing-RLS query and the anonymous curl test on your project today. If either returns something unexpected, fix that table before anything else ships. If your app holds payment or personal data and you want every table, policy, function and view reviewed, with fixes delivered as pull requests, see our security audit. For new products, our SaaS development work writes policies in the same migration that creates each table.