Supabase RLS policy patterns for owner, team and public data

You need a Supabase project you own and access to the SQL editor. The examples use a projects table with a user_id column, a teams and team_members setup, a posts table and an admin check. Rename them to fit your schema.

At the end you will have policies for each sharing model, indexes that keep them fast and a test script that proves the policies allow what they should and refuse everything else.

A using clause decides which existing rows a query can see, update or delete. A with check clause validates the row being written by insert or update. An update policy normally needs both: without with check, a user could update their own row and set its owner to someone else.

Name the role each policy applies to. Writing to authenticated means signed-in users only, and anonymous visitors match none of it. Wrap auth.uid() as (select auth.uid()) so Postgres can evaluate it once per statement instead of once per row, which Supabase documents as a performance technique.

The most common pattern: each row belongs to one user and only that user can touch it. Enable RLS first, which denies all API access until a policy exists, then add one policy per operation. The index on user_id keeps the check cheap as the table grows.

alter table public.projects enable row level security;

create policy "Owner can read"
on public.projects for select
to authenticated
using ( (select auth.uid()) = user_id );

create policy "Owner can insert"
on public.projects for insert
to authenticated
with check ( (select auth.uid()) = user_id );

create policy "Owner can update"
on public.projects for update
to authenticated
using ( (select auth.uid()) = user_id )
with check ( (select auth.uid()) = user_id );

create policy "Owner can delete"
on public.projects for delete
to authenticated
using ( (select auth.uid()) = user_id );

create index projects_user_id_idx on public.projects using btree (user_id);

For shared data, keep membership in its own table and ask whether the caller is in the team that owns the row. Put the membership lookup in a subquery that selects team ids for the current user. Index both columns involved in the join so the lookup stays fast.

Be careful that the membership table itself has RLS, or anyone could read who belongs to which team. A simple rule lets users see only their own membership rows.

alter table public.team_members enable row level security;
alter table public.team_docs enable row level security;

create policy "Members see own memberships"
on public.team_members for select
to authenticated
using ( (select auth.uid()) = user_id );

create policy "Team members can read docs"
on public.team_docs for select
to authenticated
using (
  team_id in (
    select team_id from public.team_members
    where user_id = (select auth.uid())
  )
);

create policy "Team members can insert docs"
on public.team_docs for insert
to authenticated
with check (
  team_id in (
    select team_id from public.team_members
    where user_id = (select auth.uid())
  )
);

create index team_members_user_team_idx on public.team_members (user_id, team_id);
create index team_docs_team_id_idx on public.team_docs (team_id);

For content anyone may view, such as published posts, allow select for everyone and restrict writes to the owner. Target both anon and authenticated for the read policy so it works logged out. Add a published flag if drafts must stay private.

alter table public.posts enable row level security;

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

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

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

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

There are two safe ways to mark admins. One is a claim in app_metadata, which users cannot edit. The other is a small roles table only the server can write. Never use user_metadata for authorization: Supabase notes that users can update it themselves, while app_metadata cannot be changed by the user.

Policies for the same operation combine with OR, so an added admin policy widens access without touching the owner policy.

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

create table public.admins (
  user_id uuid primary key references auth.users (id) on delete cascade
);
alter table public.admins enable row level security;

create policy "Admins read all projects via table"
on public.projects for select
to authenticated
using (
  exists (
    select 1 from public.admins
    where admins.user_id = (select auth.uid())
  )
);

The SQL editor runs as a privileged role that bypasses RLS, so a query there proves nothing. To test as a signed-in user, switch to the authenticated role and set the JWT claims for one transaction, then roll back. Replace the uuid with a real test user's id. Rows that a using clause filters out return nothing and raise no error, while a with check violation raises an error, so assert on both.

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

select count(*) from public.projects;

insert into public.projects (user_id, name)
values ('00000000-0000-0000-0000-00000000000b', 'should fail');
rollback;

The pass condition: as user A the count shows only A's rows, an insert claiming user B's id errors, and as an anonymous role only published posts are visible. Then repeat with the public API and the anon key from outside your app to confirm the same result.

Keep the test script in your repository and run it after every migration. Enable RLS in the same migration that creates a table, and review any policy an AI tool generates for a missing to clause or with check.