Supabase RLS policy examples, tested on PostgreSQL 17

Four Supabase row-level security policy sets cover most apps: users see only their own rows, anyone reads published rows, team members see their team’s rows through a security definer helper, and admins read everything through app_metadata. Most examples of these are shown, not tested. We ran each one, with Supabase’s own roles and auth.uid() on PostgreSQL 17.11, against eleven operations as two users and as anon. All four kept users out of each other’s rows on every operation. They differ in what they let people see, and in one case by nearly a factor of a hundred in speed: a team policy with a per-row helper function took 744 ms to count projects, the same rule written as a set took 7.7 ms.

The setup

The roles exactly as supabase/postgres creates them (anon, authenticated, service_role with bypassrls, and postgres without superuser but with BYPASSRLS), and auth.uid() and auth.jwt() verbatim from supabase/auth. Every request ran the way PostgREST runs one: in a transaction, with role and request.jwt.claims set by set_config(…, true). User A and user B each own rows; anon sends no user. Tables use generated by default as identity, so an upsert by id is possible; with generated always it fails before any policy, as we measured in Supabase RLS policy not working.

Every example uses (select auth.uid()), wrapped, and names its role with to authenticated. Why the wrapper is worth it is in when (select auth.uid()) helps.

What did each example allow?

The write side came out the same for all four examples, for every user we tested, including the admin in example 4:

A dash means the operation has no own row to act on for anon. In example 3, ‘their own row’ means a project of one of their teams, and handing it to someone else means moving it to a team they are not in. Note the two quiet zeros: updating or deleting another user’s row raises no error, it just affects nothing. Your client has to check the count if it matters.

Example 1: users can only see and change their own rows

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

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

create policy "Users can update their own notes" on public.notes
for update to authenticated
using ( (select auth.uid()) = user_id )
with check ( (select auth.uid()) = user_id );

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

A saw rows 1 and 2, B rows 3 and 4, anon none. All four policies are needed. Without the SELECT policy, .insert().select() fails and .update().eq(…) silently changes nothing, as we measured in Supabase RLS policy not working. The with check on the update policy is what stopped A from handing a note to B; never write with check (true) there.

Example 2: anyone can read published rows, only the author can write

create policy "Published posts are public" on public.posts
for select to anon, authenticated
using ( is_published );

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

Plus insert, update and delete policies on author_id as in example 1. Two SELECT policies on the same table are combined with or. Of four posts, A’s published one, A’s draft, B’s published one and B’s draft, anon saw the two published posts, A saw both published posts and A’s own draft, and B the same the other way round. Reading a published post did not give anyone the right to change it: A’s update of B’s published post gave UPDATE 0.

Example 3: team members can see their team’s rows

The common shape: teams, team_members (team_id, user_id, role), and a projects table with a team_id. The first version most people write puts a subquery on team_members in the policy of team_members itself, to let members see each other:

create policy "Members can view co-members (naive)" on public.team_members
for select to authenticated
using ( team_id in (select team_id from public.team_members
                    where user_id = (select auth.uid())) );

That failed on the first query with infinite recursion detected in policy for relation "team_members", and a projects policy with the same subquery inside it failed with the same error, because it reads team_members, whose own policy recursed. The fix is a security definer function in a schema the API does not expose, owned by postgres, which bypasses RLS:

create schema if not exists private;

create function private.user_team_ids()
returns setof bigint
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.user_team_ids() from public;
grant usage on schema private to authenticated;
grant execute on function private.user_team_ids() to authenticated;

create policy "Members can view co-members" on public.team_members
for select to authenticated
using ( team_id in (select private.user_team_ids()) );

create policy "Members can view team projects" on public.projects
for select to authenticated
using ( team_id in (select private.user_team_ids()) );

With the insert, update and delete policies on projects written the same way, and measured with a team_members policy that shows only your own memberships (the helper bypasses it either way), A, a member of teams 1 and 3, saw projects 1 and 3. B, a member of teams 2 and 3, saw projects 2 and 3. anon saw no projects and no memberships. Moving a project to a team A is not in failed with new row violates. Every member can update and delete the team’s projects; use the role column in the update and delete policies if that is too much.

Do not let users insert their own membership row. team_members had no insert policy here, and an insert by A failed. The obvious policy, with check (user_id = (select auth.uid())), would let anyone join any team, and through user_team_ids() read all of its projects. Restrict inserts to team owners, or add members server-side. Supabase’s guide is explicit about where such a function lives: “A security definer function in an exposed schema is callable over the Data API with the creator’s privileges. Never create one in a schema listed under ‘Exposed schemas’”. And about the search path: “Set search_path = '' on every security definer function”.

A set, not a per-row check

The other common way to write the same rule is a function that answers yes or no per row, private.is_team_member(team_id). It allowed and denied exactly the same as user_team_ids() on all eleven operations. It was nearly a hundred times slower. With 100,003 projects over 1,003 teams and a user in 3 of them, select count(*) from projects counted 300 projects:

The plans show why. team_id in (select private.user_team_ids()) ran the function once and turned its three team ids into a hashed set that every row was checked against. private.is_team_member(team_id) stayed a function call in the filter, once per row: the plan kept the function call, because PostgreSQL does not inline a security definer function, nor one with a set clause. Wrapping it in (select …) did not help, because the call depends on the row’s team_id; the plan turned it into a subplan that still ran 100,003 times. Supabase’s guide warns about exactly this: “You can only use this technique if the results of the query or function do not change based on the row data.” So write a membership rule as “is the row’s team in my set of teams”, not “is this team mine”.

All four plans with a policy read the whole projects table with a sequential scan, even though projects (team_id) had an index; the planner did not use it for this count. We did not test other queries.

Example 4: admins can read everything

create policy "Users view own invoices, admins view all" on public.invoices
for select to authenticated
using (
  (select auth.uid()) = user_id
  or ((select auth.jwt()) -> 'app_metadata' ->> 'role') = 'admin'
);

With the write policies from example 1, an admin, a user whose JWT carried app_metadata.role = 'admin', saw all four invoices but could still change only their own: UPDATE 0 and DELETE 0 on other users’ rows. The admin check sits only in the SELECT policy, so that is where it applies. If admins need to edit, the or has to go into the update policy’s using and its with check, and into the delete policy’s using; with it in with check, an admin can also hand a row to anyone. We did not measure that variant. And the role is only as fresh as the token. Supabase’s guide: “Keep in mind that a JWT is not always up-to-date. In the team policy example, even if you remove a user from a team and update the app_metadata field, that will not be reflected using auth.jwt() until the user’s JWT is refreshed.”

The check has to read app_metadata, not user_metadata. Supabase’s guide: “creating an RLS policy that relies on the user_metadata claim can create security issues in your application as this information can be modified by authenticated end users.” We wrote the same policy on user_metadata instead. User B, with user_metadata.role = 'admin' in the claims, then read all four invoices, and the real admin, whose role is in app_metadata, read only their own.

Reproduce it

setup.sql creates the Supabase roles, the two functions, the tables and the helpers. run.sql runs every operation as every user, the recursion test and the timing, each in a transaction that it rolls back. run.sh does both in a throwaway container started as supabase_admin. We set the JWT claims by hand; we did not test whether a user can actually change their user_metadata through the client.

What we did not measure

Supabase’s hosted platform and Realtime policies (Storage policies are in Supabase Storage RLS: why uploads fail); inserting and removing team members, for which team_members has no insert policy here (an insert by A failed with new row violates); a user in thousands of teams, where the set from user_team_ids() gets large; and other queries than a count for the timing.

Sources: Supabase, Row Level Security, read on 19 October 2026; Supabase’s roles from supabase/postgres and auth.uid() and auth.jwt() from supabase/auth, read on 19 October 2026. All results are our own, on PostgreSQL 17.11 in a throwaway container, on 19 October 2026.