Supabase RLS policy examples: copy-paste policies for 7 common app patterns

Disclosure: we build SniffPup, a free passive scanner for live sites. This guide was first published on our site. The SQL follows Supabase's own RLS docs and works without our tool.

Short answer: most apps need only a handful of Row Level Security (RLS) patterns:

1. Pick the one that matches each table below.
2. Turn RLS on.
3. Grant each role only what it needs.
4. Write one policy per operation.
5. Prove it with a signed-out test and a two-user test.

Rename the tables and columns to match yours, and only test projects you own.

Before any policy: turn RLS on and set the grants

Postgres checks two things. Grants decide whether a role can run an operation on a table at all. Policies decide which rows it applies to. Supabase's docs warn that on projects that still grant anon and authenticated every privilege on new tables, adding policies doesn't take those grants back. So for every table in an exposed schema:

alter table public.todos enable row level security;
-- Start from nothing, then grant back only what the app uses.
revoke all on table public.todos from anon, authenticated;
grant select, insert, update, delete on table public.todos to authenticated;

With RLS on and no policies yet, the API returns no rows to anon or authenticated. Then add policies.

Three habits from Supabase's docs apply to every example below:

• Name the role with to authenticated (or to anon).
• Wrap helpers as (select auth.uid()) so Postgres runs them once per query instead of once per row.
• Add an index on every column a policy filters on, such as user_id.

1. Private rows: each user sees only their own

For todos, notes, orders, chats: anything that belongs to one user. Write one policy per operation:

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

create policy "Users create their own todos"
on public.todos for insert to authenticated
with check ( (select auth.uid()) = user_id );

create policy "Users update their own todos"
on public.todos for update to authenticated
using ( (select auth.uid()) = user_id )        -- which rows they can change
with check ( (select auth.uid()) = user_id );  -- what the row can become

create policy "Users delete their own todos"
on public.todos for delete to authenticated
using ( (select auth.uid()) = user_id );

create index todos_user_id_idx on public.todos using btree (user_id);

The with check on update matters: without it, a user could set user_id to someone else's ID. A column default such as user_id uuid not null default auth.uid() saves your frontend from sending it. Supabase also notes that an update needs a matching select policy, which the first policy provides.

2. Public read, owner-only writes

For published posts, public profiles or a product catalog that anyone, signed in or not, may read:

grant select on table public.posts to anon, authenticated;
grant insert, update, delete on table public.posts to authenticated;

create policy "Anyone can read published posts"
on public.posts for select to anon, authenticated
using ( published = true or (select auth.uid()) = author_id );

create policy "Authors create their own posts"
on public.posts for insert to authenticated
with check ( (select auth.uid()) = author_id );

create policy "Authors update their own posts"
on public.posts for update to authenticated
using ( (select auth.uid()) = author_id )
with check ( (select auth.uid()) = author_id );

create policy "Authors delete their own posts"
on public.posts for delete to authenticated
using ( (select auth.uid()) = author_id );

RLS works on whole rows, not columns. If a public table also holds private details (an email, a phone number, a Stripe customer ID), anyone who can read the row can read those too. Move private columns to a separate table with owner-only policies (pattern 1).

3. Team or organization access

A policy on projects that reads team_members, while team_members has its own policy reading back, can loop forever (Postgres error 42P17, infinite recursion). Supabase's docs break the loop with a security definer function in a schema the API doesn't expose:

create schema if not exists private;

create function private.my_team_ids()
returns setof uuid
language sql
security definer
set search_path = ''
stable
as $$
  select team_id from public.team_members
  where user_id = (select auth.uid())
$$;

revoke execute on function private.my_team_ids() from public;
grant usage on schema private to authenticated;
grant execute on function private.my_team_ids() to authenticated;

create policy "Members read their team's projects"
on public.projects for select to authenticated
using ( team_id in (select private.my_team_ids()) );

create policy "Members read their team's member list"
on public.team_members for select to authenticated
using ( team_id in (select private.my_team_ids()) );

create index team_members_user_id_idx on public.team_members using btree (user_id);

The function breaks the loop because it reads team_members as its owner, which on Supabase is postgres and bypasses RLS. Two rules from the docs:

• Always set search_path = '' on a security definer function and schema-qualify every name inside it.
• Never put one in an exposed schema such as public.

Add insert, update and delete policies the same way, and decide who may add members.

4. Admin-only access (without letting users promote themselves)

The classic mistake is a policy that trusts user_metadata or an is_admin column the user can edit. Users can update their own raw_user_meta_data, but not raw_app_meta_data.

Option A, a role in app_metadata (set only from your server with the admin API, or with SQL):

create policy "Admins read all orders"
on public.orders for select to authenticated
using ( (select auth.jwt() -> 'app_metadata' ->> 'role') = 'admin' );

A JWT isn't refreshed instantly. When you remove someone's admin role, it keeps working until their token refreshes. If that matters, use option B.

Option B, a roles table only your server can write:

create schema if not exists private;

create table public.user_roles (
  user_id uuid primary key references auth.users on delete cascade,
  role text not null check (role in ('admin', 'member'))
);
alter table public.user_roles enable row level security;
revoke all on table public.user_roles from anon, authenticated;

create function private.is_admin()
returns boolean
language sql
security definer
set search_path = ''
stable
as $$
  select exists (
    select 1 from public.user_roles
    where user_id = (select auth.uid()) and role = 'admin'
  )
$$;

revoke execute on function private.is_admin() from public;
grant usage on schema private to authenticated;
grant execute on function private.is_admin() to authenticated;

create policy "Admins read all orders"
on public.orders for select to authenticated
using ( (select private.is_admin()) );

Permissive policies combine with OR, so users still see their own orders (pattern 1) and admins see everyone's. Hiding the admin page in your frontend protects nothing on its own. The policy is what counts.

5. Insert-only forms: anyone can submit, nobody can read

alter table public.waitlist enable row level security;
revoke all on table public.waitlist from anon, authenticated;
grant insert (email, source) on table public.waitlist to anon, authenticated;

create policy "Anyone can join the waitlist"
on public.waitlist for insert to anon, authenticated
with check ( char_length(email) between 3 and 320 );

There's no select grant or policy, so nobody can read the list through the API. In supabase-js, call insert() without chaining .select().

Anyone can call the endpoint directly, not just through your form, so a CAPTCHA on the form alone won't stop scripted sign-ups. If spam matters, send the form to a server route or Edge Function that checks the CAPTCHA and does the insert, and drop the anon grant.

6. Per-user storage folders

Files are saved as <user id>/<file name> in a private bucket. The upload policy is the one in Supabase's storage docs, and the read policy applies the same folder check:

create policy "Users upload to their own folder"
on storage.objects for insert to authenticated
with check (
  bucket_id = 'user-files'
  and (storage.foldername(name))[1] = (select auth.jwt() ->> 'sub')
);

create policy "Users read their own files"
on storage.objects for select to authenticated
using (
  bucket_id = 'user-files'
  and (storage.foldername(name))[1] = (select auth.jwt() ->> 'sub')
);

Overwriting with upsert also needs select and update policies. Anyone who has the URL can fetch a public bucket's files, whatever your policies say.

7. Require MFA for sensitive changes (optional)

A restrictive policy is combined with AND:

create policy "Updates need MFA"
on public.profiles as restrictive for update to authenticated
using ( (select auth.jwt() ->> 'aal') = 'aal2' );

Prove each policy works

1. Run Security Advisor. In the dashboard, open Advisors → Security Advisor (or run supabase db advisors). It flags tables in public with RLS off, always-true policies, policies that trust user_metadata and security definer views. See Supabase's advisor docs.
2. Test signed out. With only your anon key:

   curl "https://YOUR_REF.supabase.co/rest/v1/todos?select=*&limit=1" \
     -H "apikey: YOUR_ANON_KEY"

An empty [] or a permission error is what you want.

1. Test as two users. Sign in as user B and try to read, change and delete user A's rows. Every attempt should fail.
2. Keep it tested. Supabase's RLS docs show pgTAP tests run with supabase test db (see the database testing docs). Re-run them after every migration, especially ones an AI tool wrote.

Where SniffPup fits (we build it)

A free SniffPup scan of your live URL does a read-only version of the signed-out test from the outside:

• With the public anon key your site already ships, it sends one HEAD request per table, up to 8, to see whether an anonymous visitor can read at least one row. It never downloads rows or writes anything.
• It also flags a service_role or sb_secret_ key in your frontend.

It can't see your policies, writes or what signed-in users can read. To review the policies themselves, paste them into the free RLS policy explainer, which runs in your browser. A 100 score means no issues were found in our checks. It's never proof of security.

These examples are a starting point, not a review of your app. Got a pattern we missed? Tell us: https://sniffpup.com/suggest