Supabase RLS Policy Examples, Plus 5 Mistakes That Leak Data

These Supabase RLS policy examples cover the patterns most apps need: rows each user owns, public read with owner write, and team membership, plus a clear explanation of using versus with check. Each one follows the shape Supabase's own docs recommend, so you can paste it and change the table names. After them come five mistakes that look like protection but let any user read every row, each with the bad SQL and the fix.

How Supabase RLS policies work

A Supabase RLS policy is a rule Postgres adds to every query on a table. using filters which existing rows a request can see, update or delete. with check decides which new or changed rows it may write. Policies apply per role, anon for signed-out visitors and authenticated for signed-in users, and permissive policies combine with OR.

Three things sit underneath every example below:

• RLS has to be enabled on the table, or none of its policies apply. If the Security Advisor flags a table, start with Supabase RLS disabled in public.
• With RLS on and no policy for an operation, that operation is denied. You only write policies for what the app should be able to do.
• The secret key (sb_secret_..., or the legacy service_role key) bypasses every policy. Policies protect you from the publishable key in the browser, not from a leaked secret key. More on that in the Supabase service role key.

Supabase's Row Level Security guide asks for one policy per operation rather than for all, because a for all policy hides which operation each rule was meant to cover. The examples follow that.

Supabase RLS policy examples for common apps

Three patterns cover most apps. Change the table and column names, keep the shape.

Example 1: owner-only rows

The most common pattern: a todos, notes or orders table where each row belongs to one user and nobody else may see it.

create table public.todos (
  id bigint generated always as identity primary key,
  user_id uuid not null default auth.uid() references auth.users (id) on delete cascade,
  title text not null,
  done boolean not null default false
);

alter table public.todos enable row level security;

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

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

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

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

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

Two details come straight from Supabase's performance advice. Writing (select auth.uid()) instead of auth.uid() lets Postgres run the function once per query and reuse the result, rather than calling it for every row. And the index on user_id matters because every query now filters on that column. On a small table you won't notice either; on a large one, both make a real difference.

to authenticated means signed-out requests skip these policies entirely. That is also why auth.uid() returning null for a signed-out visitor is not a hole here.

Example 2: public read, owner write

A blog, a recipe site, public profiles: anyone may read published rows, only the author may write.

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 can read their own drafts"
on public.posts for select
to authenticated
using ( (select auth.uid()) = author_id );

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

create policy "Authors can 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 can delete their own posts"
on public.posts for delete
to authenticated
using ( (select auth.uid()) = author_id );

The two select policies are combined with OR: a visitor sees published posts, an author also sees their own drafts. If every row really is public, a reference table of countries for example, using ( true ) on the select policy is fine. The danger is only in using it for data that isn't meant to be public, which is mistake 1 below.

Example 3: team or organization membership

When rows belong to a team, the policy has to ask "is this user a member of the row's team?" Asking that with a subquery on team_members inside the policy works until team_members gets its own policy that reads the other table, at which point Postgres reports infinite recursion. Supabase's recommended fix is a small helper function that reads the membership table as its owner:

create schema if not exists private;

create table public.team_members (
  team_id uuid not null references public.teams (id) on delete cascade,
  user_id uuid not null references auth.users (id) on delete cascade,
  primary key (team_id, user_id)
);

-- The primary key indexes team_id first. Policies filter on user_id, so it needs its own index.
create index team_members_user_id_idx on public.team_members using btree (user_id);

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

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

create policy "Members can see their teams' members"
on public.team_members for select
to authenticated
using ( team_id in (select private.user_team_ids()) );

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

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

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

Why each piece is there:

• security definer makes the function run as its owner (postgres on Supabase), so it can read team_members without that table's policies running again.
• set search_path = '' pins name lookup, which is why every table inside is written as public.team_members. Mistake 5 below explains what goes wrong without it.
• The function lives in a private schema. Supabase warns that a security definer function in an exposed schema can be called directly over the Data API with its creator's privileges. Keep private out of the exposed schemas in your API settings.
• Postgres grants execute on new functions to everyone by default, so the revoke and grant lines limit it to signed-in users.

Who may add or remove members is up to your app (often only a team owner), so write those insert and delete policies on team_members to match. Delete on projects is left out above for the same reason.

Supabase RLS: using vs with check

using looks at the row as it is now. with check looks at the row as it would be after the write.

An insert has no existing row, so an insert policy takes only with check; Postgres rejects using on it. An update needs both questions answered: may this user touch this row, and is the result still allowed?

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

If you leave with check out, Postgres reuses the using expression for it, which is safe here. The version that leaks is the one that spells out with check ( true ): a user can then change user_id on their own row to someone else's id, moving data into another account. Supabase's docs add one more catch. An update also needs a matching select policy, or it silently changes nothing.

Why are my Supabase RLS policies not working?

Most "not working" reports are one of these:

• A permission error with code 42501. The role has no grant on the table, and Postgres stops before any policy runs. Check grants before you rewrite the policy.
• An empty result, no error. RLS is on and no policy matched. Either the policy is missing for that operation, or the condition is false for this user.
• Works signed in, fails signed out (or the reverse). Signed-out requests run as anon and auth.uid() is null; check the to clause.
• Updates do nothing. There is no select policy covering the row.

Remember that the SQL Editor runs as postgres, which bypasses RLS, so a query that works there proves nothing about your app.

Five RLS mistakes that leak every user's rows

1. Always-true policies: using (true) and with check (true)

-- Leaks: every row, to everyone with the public key
create policy "Enable read access for all users"
on public.orders for select
to anon, authenticated
using ( true );

Often added to make an empty table "work" during development. Because permissive policies combine with OR, one always-true policy also cancels every careful policy on the same table and operation: the Postgres docs state that all permissive policy expressions are combined using OR. with check ( true ) on an insert or update policy is the write-side version: anyone may write any row.

The fix is to drop it and use an owner or team condition from the examples above:

drop policy "Enable read access for all users" on public.orders;

To find these in your project, list the always-true policies:

select tablename, policyname, cmd, roles, qual, with_check
from pg_policies
where schemaname = 'public'
  and (qual = 'true' or with_check = 'true');

Keep the ones on tables that really are public and read-only. Drop the rest.

2. to authenticated using (true)

-- Leaks: every row, to anyone who signs up
create policy "Logged-in users can read profiles"
on public.profiles for select
to authenticated
using ( true );

This one feels safe because it says "authenticated". But being authenticated only means having an account, and Supabase Auth lets anyone create one while Allow new users to sign up is on (see Auth general configuration). If anonymous sign-ins are enabled, an anonymous user also gets the authenticated role. A stranger signs up with a throwaway email, takes the access token your app gives them, and reads the whole table.

Fix it with a condition about the row, not about the session: (select auth.uid()) = user_id, or the team helper above. If every member really should see every row, a member directory say, make sure the table holds only the columns you'd show a stranger.

3. Policies that trust user_metadata

-- Leaks: any user can make themselves an admin
create policy "Admins can read all orders"
on public.orders for select
to authenticated
using ( ((select auth.jwt()) -> 'user_metadata' ->> 'is_admin')::boolean );

User metadata is stored in raw_user_meta_data, and Supabase's RLS guide says it "can be updated by the authenticated user" and "is not a good place to store authorization data." One call from the browser console does it:

await supabase.auth.updateUser({ data: { is_admin: true } })

Use app_metadata instead. It lives in raw_app_meta_data, which, in the guide's words, "cannot be updated by the user":

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

Set it from your server with the secret key, never from the client:

await supabaseAdmin.auth.admin.updateUserById(userId, {
  app_metadata: { role: 'admin' },
})

A role read from the JWT is only as fresh as the token, so a removed admin keeps access until their token refreshes. If that matters, keep roles in a table and check it with a security definer helper like the team example. The same warning applies to policies that read raw_user_meta_data from auth.users directly.

4. RLS never enabled

-- Leaks: the policy below is never applied
create table public.invoices (
  id bigint generated always as identity primary key,
  user_id uuid not null references auth.users (id),
  amount_cents integer not null
);

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

The policy is correct and does nothing. Postgres applies policies only when row level security is enabled on the table, so this table is fully open. The Security Advisor reports the pair as "Security policy not enforced". The fix is one line, in the same migration as the table:

alter table public.invoices enable row level security;

The full walkthrough, including tables created outside migrations, is in Supabase RLS disabled in public.

5. security definer functions without a fixed search_path

-- Risky: no search_path, unqualified names, and in an exposed schema
create function public.is_team_member(t uuid)
returns boolean
language sql
security definer
as $$
  select exists (
    select 1 from team_members
    where team_id = t and user_id = auth.uid()
  );
$$;

A security definer function runs with its owner's privileges, and on Supabase the owner is usually postgres, which bypasses RLS. Without a fixed search_path, the names inside it are looked up through the caller's search path. The Postgres docs warn that a caller who can create objects in a schema on that path, the temporary schema included, can mask the objects the function meant to use and have them run with the owner's privileges. And because this one sits in public, it can be called directly over the Data API.

The fix is the shape from example 3: a schema that isn't exposed, set search_path = '', schema-qualified names, and execute granted only to the roles that need it.

create function private.is_team_member(t uuid)
returns boolean
language sql
security definer
set search_path = ''
stable
as $$
  select exists (
    select 1 from public.team_members
    where team_id = t and user_id = (select auth.uid())
  );
$$;

revoke execute on function private.is_team_member(uuid) from public;
grant execute on function private.is_team_member(uuid) to authenticated;

The Security Advisor reports the missing setting as "Unsecured function search path".

Policy checklist before you ship

1. Every table in public has enable row level security, in the migration that creates it.
2. Each table has a separate policy for each operation the app uses, and none for the rest.
3. Every policy has a to clause, and no policy is using (true) unless the table is public and read-only.
4. No to authenticated using (true) on a table that holds anything personal.
5. No policy reads user_metadata or raw_user_meta_data. Roles come from app_metadata or a table.
6. Every security definer function sets search_path = '', lives outside the exposed schemas, and has execute revoked from public.
7. Columns your policies filter on (user_id, team_id) are indexed.
8. You've tested as a signed-out visitor, as the owner, and as a second signed-up user who should see nothing.

For a first pass over your own repository, this finds the risky shapes in migration files:

grep -rniE "using[[:space:]]*\([[:space:]]*true[[:space:]]*\)|with check[[:space:]]*\([[:space:]]*true[[:space:]]*\)|user_meta|security definer" supabase/migrations

Each hit needs a human look. Some are fine on purpose.

These five shapes are what Tuvio's Supabase checks flag in the .sql migrations in your repository: always-true policies (including to authenticated using (true)), policies open to any logged-in user, tables with RLS disabled, policies that trust user_metadata, and security definer functions without a search_path. A table that is public on purpose gets flagged too, so read each finding against what you meant. It reads migration files, not your live database, so a policy created in the dashboard without a migration won't be seen. A free scan needs no account, and the coverage page lists what else it checks. If your app came out of Lovable, is Lovable safe covers the rest of what to look at.