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

Last updated October 25, 2026 · By Ember, SniffPup

Short answer: most apps need only a handful of Row Level Security (RLS) patterns. Pick the one that matches each table below, turn RLS on, grant each role only what it needs, write one policy per operation, then prove it with a signed-out test and a two-user test. The SQL follows Supabase's own RLS docs. 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 the anon or authenticated roles. 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, and 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), and keep the public table to what you'd show a stranger.

3. Team or organization access

For workspaces, shared lists or multi-tenant apps, where everyone in a team can see the team's rows. 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, and never put one in an exposed schema such as public, because anyone could call it through the API with the owner's privileges. Add insert, update and delete policies the same way, and decide who may add members: usually only a team owner, never "anyone signed in".

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. Supabase's docs say users can update their own raw_user_meta_data, but not raw_app_meta_data. Two safe options:

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, so 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 (Supabase's docs recommend a dedicated roles table over column-level privileges for most apps):

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;
-- No grants and no policies for anon/authenticated: only your server
-- (secret key, server-side) or the SQL editor can change roles.

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()) );

Postgres combines permissive policies with OR, so this admin policy sits next to the owner-only policy from pattern 1: users still see their own orders, 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

For a waitlist, contact form or feedback box where signed-out visitors write but must never read other people's entries:

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 is no select grant or policy, so nobody can read the list through the API, including the person who just signed up. In supabase-js, call insert() without chaining .select(), since reading the row back would be refused. The column-level grant stops visitors from filling columns such as created_at or an internal status. The policy is open to anyone on purpose, and 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

Storage access is controlled by policies on storage.objects. With files saved as <user id>/<file name> in a private bucket, the upload policy below 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 a file with upsert also needs select and update policies, per the same docs. Keep invoices, IDs and private uploads out of public buckets: a public bucket's files can be fetched by anyone who has the URL, whatever your policies say.

7. Require MFA for sensitive changes (optional)

A restrictive policy is combined with AND, so it adds a requirement on top of your other policies. Supabase's docs show this one, which blocks profile updates unless the user signed in with a second factor:

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 Supabase dashboard open Advisors, then 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, ask for one row of each private table:

   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. Real rows mean that table needs work. Then run a free SniffPup scan of your live site, which does a read-only version of this test for up to 8 tables and checks your frontend for keys that would bypass RLS.
3. Test as two users. Sign in as user B and try to read, change and delete user A's rows. Every attempt should fail or return nothing. Check writes as carefully as reads.
4. Keep it tested. Supabase's RLS docs show pgTAP test files that assert allow and deny for each operation as anon and authenticated, run with supabase test db (see the database testing docs). Re-run them after every migration, especially ones an AI tool wrote.

Not sure what an existing policy does? Paste your pg_policies rows or schema SQL into the RLS policy explainer, and see the Supabase RLS checker guide for the SQL that lists tables with RLS off and the most common policy mistakes.

FAQ

These examples are a starting point, not a review of your app. A clean report is not a guarantee of security, and a 100 score means no issues were found in our checks, not proof of security. Only test projects you own.

Run a free scan →