Supabase RLS Tutorial: Policies, using vs with check, Testing

Supabase RLS (Row Level Security) is Postgres's built-in row-level permission system: you enable RLS on a table, write a policy for select, insert, update and delete, and the database decides row by row what the signed-in user can read and write. The browser queries your database with a public publishable key, so these policies are the thing actually protecting your data. The examples come from the rules my site's back office runs on, mistakes included.

Technical details follow the official Supabase and PostgreSQL docs, checked on 3 November 2026.

What RLS protects

Supabase turns your tables into an API (the Data API). Every query from supabase-js in the browser ends up running as one of two Postgres roles: anon (not signed in) or authenticated (signed in). Whether those roles can touch a table depends on two gates:

• Grants: does the role have select, insert and so on for this table?
• RLS: given the grant, which rows can it read or write?

A comment in my blog posts migration sums up the split:

-- Grants gate; RLS filters. Anon may only read; authenticated may write.
alter table public.blog_posts enable row level security;
grant select on public.blog_posts to anon;
grant select, insert, update, delete on public.blog_posts to authenticated;

Enabling RLS: one statement, then everything is denied

alter table public.notes enable row level security;

With RLS on and no policy yet, a publishable-key query returns nothing. PostgreSQL calls this "default deny": if no policy applies, no rows are visible or updatable.

Watch how you create tables. Supabase says tables created in the dashboard's Table Editor get RLS on by default; tables created in the SQL Editor or other tools need enable row level security explicitly. I create every table in a migration, so every migration carries that line.

Policies for the four commands

Take a notes table where each user manages their own notes, the most common pattern in the Supabase docs:

create table public.notes (
  id      uuid primary key default gen_random_uuid(),
  user_id uuid not null default auth.uid() references auth.users(id) on delete cascade,
  body    text not null
);
alter table public.notes enable row level security;

create policy "Read own notes" on public.notes for select to authenticated
  using ((select auth.uid()) = user_id);
create policy "Insert own notes" on public.notes for insert to authenticated
  with check ((select auth.uid()) = user_id);
create policy "Update own notes" on public.notes for update to authenticated
  using ((select auth.uid()) = user_id)
  with check ((select auth.uid()) = user_id);
create policy "Delete own notes" on public.notes for delete to authenticated
  using ((select auth.uid()) = user_id);

create index notes_user_id_idx on public.notes (user_id);

Command	Clause	What it checks
select	using	which existing rows can be read
insert	with check	whether the new row is allowed
update	using + with check	using picks the rows you may change, with check validates the result
delete	using	which existing rows can be deleted

• Always write to authenticated. Supabase recommends naming the role on every policy; without it, anonymous requests still pay for evaluating the policy. In the official benchmark, adding to authenticated took anon access from 170 ms to under 0.1 ms.
• When nobody is signed in, auth.uid() returns null, and null = user_id is never true. Nothing leaks, but nothing errors either: you just get no rows.
• Index the columns your policies use. In Supabase's own benchmark, indexing user_id took a query from 171 ms to under 0.1 ms.

You can also cover all four commands with one for all policy, which is what my back office does.

using vs with check

using checks rows already in the table; with check checks rows about to be written. They fail differently:

• A row that fails using is silently filtered out. A select with missing rows or an update that touches 0 rows is usually this.
• A row that fails with check makes the whole insert or update fail with an error.

Two rules from the PostgreSQL docs that people miss:

• An update usually has to see the row too: whenever it has a where clause or returns data (supabase-js .eq() and .select() both count), you need a select or all policy covering the row on top of the update policy. The Supabase docs put it flatly: an update needs a matching select policy.
• If a for all or update policy has only using, that expression is also used as with check.

with check is what stops someone from moving a row to another owner. In the notes table the condition is the same either way, but when the conditions differ, a missing with check can let a user hand their row to someone else or put it into a state others can see.

My rule: one workspace, several members

My site's back office (/app) used to have exactly one user, and most tables used the textbook policy:

create policy "Owner manages own projects" on public.projects
  for all to authenticated
  using (owner_id = auth.uid()) with check (owner_id = auth.uid());

A few older tables were looser still. They had no owner column, and the migration comment said that in a single-user app "authenticated" means the one owner, so the policy was using (true).

In September I needed a second account for a colleague handling SEO, with the same permissions as mine. The design doc, spec/2026-09-29-workspace-members-design.md, opens with the problem:

Layer	Before	With a second account
Data (RLS)	most tables owner_id = auth.uid(); a few older tables using (true)	he can't see Studio or posts, yet can read, write and delete those older tables through the API

The two failures point in opposite directions. owner_id = auth.uid() is too strict: every row is mine, so his auth.uid() matches nothing. using (true) is too loose: any signed-in account can touch those tables. It had been fine only because mine was the only account. Once that changed, both rules were wrong.

The fix is a members table plus helper functions for "is this user a member" and "who owns the workspace". What follows is a simplified version with generic names and schema; the structure matches my real migration:

create table public.members (
  user_id    uuid primary key references auth.users(id) on delete cascade,
  role       text not null check (role in ('owner', 'member')),
  created_at timestamptz not null default now()
);

create schema if not exists private;
grant usage on schema private to authenticated;

create or replace function private.is_member() returns boolean
language sql stable security definer set search_path = '' as $$
  select exists (select 1 from public.members where user_id = auth.uid())
$$;

create or replace function private.workspace_owner_id() returns uuid
language sql stable security definer set search_path = '' as $$
  select user_id from public.members where role = 'owner'
$$;

Both are security definer functions, so they bypass RLS. Supabase recommends a schema the API doesn't expose; if you keep them in public, tighten execute rights and return values along the lines described further on.

The members table itself needs a third helper, private.is_owner() (same shape, plus a role = 'owner' condition), for its own policies: every member can see the list, but only the owner can add, change or remove people, and only member rows. A member can't promote himself, and nobody can touch the owner row:

create policy "Owner adds members" on public.members
  for insert to authenticated
  with check ((select private.is_owner()) and role = 'member');

Every table with an owner column gets the same policy (again simplified):

create policy "Members manage studio_contents" on public.studio_contents
  for all to authenticated
  using ((select private.is_member()) and owner_id = (select private.workspace_owner_id()))
  with check ((select private.is_member()) and owner_id = (select private.workspace_owner_id()));

alter table public.studio_contents alter column owner_id set default private.workspace_owner_id();

• is_member(): only members see anything. A non-member reads 0 rows from every table.
• owner_id = workspace_owner_id(): the row must belong to the workspace. For me this means exactly what owner_id = auth.uid() used to.
• The same condition in with check: a member can't insert rows under his own id or update existing rows out of the workspace.
• The default becomes workspace_owner_id(): rows my colleague creates belong to the workspace, so the background worker and the other "owner must match" checks didn't need to change.

Tables without an owner column check membership only: using ((select private.is_member())) with check ((select private.is_member())).

The rule now lives in the project's CLAUDE.md: every new table follows it, and owner_id = auth.uid() is explicitly ruled out.

The first version of the migration checked membership only, with no owner condition. Review caught it: with membership alone, a member could insert a row with his own owner_id, or update an existing row to his name, and that row would leave the workspace. Adding the owner condition to with check closed the hole.

Why wrap calls in (select …)

Every function call above, auth.uid() included, sits inside (select …). The difference is how often it runs. Written as auth.uid() = user_id, Postgres may call the function once per row; written as (select auth.uid()), the optimizer turns it into an initPlan that runs once per statement and reuses the result.

Supabase's RLS performance guide has the numbers:

Rewrite	Before	After
auth.uid() = user_id → (select auth.uid()) = user_id	179 ms	9 ms
is_admin() (queries another table) → (select is_admin())	11,000 ms	7 ms
has_role() = role → (select has_role()) = role	178,000 ms	12 ms

The catch: this only works when the result doesn't depend on the row. is_member() and workspace_owner_id() depend only on who is signed in, so they qualify. The Performance Advisor's auth_rls_initplan lint ("Slow security policy detected") flags the unwrapped form.

security definer helpers: useful, with a cost

is_member() reads members, which has RLS of its own. A plain function would loop: the policy asks the members table, whose policy asks about membership again. The design doc's answer is security definer: the function runs with its creator's privileges, skips the RLS on members, and doesn't recurse. The PostgreSQL docs say the same thing from the other side: policy expressions run with the querying user's privileges, and security definer functions can reach data that user can't.

That convenience is also the risk. The function bypasses RLS, so whatever it returns, the caller gets. Following the official guidance, I:

• set search_path = '' and schema-qualify everything (public.members), so nobody can hijack a name;
• return as little as possible, never whole rows;
• revoke the default execute grant and give it to authenticated only:

revoke all on function private.is_member(), private.is_owner(), private.workspace_owner_id() from public, anon;
grant execute on function private.is_member(), private.is_owner(), private.workspace_owner_id() to authenticated;

Supabase's Data API docs warn that functions are callable by every role by default and that RLS doesn't apply to them, so this step is on you.

Supabase also recommends keeping security definer functions out of schemas exposed to the API, such as public: anything there can be called over the Data API by any role with execute, with the creator's privileges. If your app genuinely needs to call a check function through the API, at least revoke anon and make sure it returns only something about the caller (an 'am I a member' boolean), harmless for any signed-in user to see. Otherwise put it in a schema the API doesn't expose. The Security Advisor's authenticated_security_definer_function_executable lint lists every such function; go through them one by one.

Testing policies

The usual symptom of a wrong policy is no error and 0 rows. Reading the SQL won't show it; you have to run it as different users.

Supabase documents two approaches: tests written against a client in your app's language, or SQL tests with pgTAP in supabase/tests/database/, run with supabase test db. pgTAP's policies_are() checks that a table carries exactly the policies you expect, no more and no less.

I use the first. tests/e2e/workspace-members.mjs runs supabase-js against the production database:

1. Create a throwaway account with the admin API.
2. As a non-member: every table reads 0 rows; writes to every kind of table fail. Deletion is tried on a single sentinel row, so a missing rule can never touch real data.
3. As anon: only published posts are visible.
4. After I add him as a member: he sees exactly the same row counts as I do, and rows he inserts get my owner_id.
5. A member can't write rows under his own id, move rows out of the workspace, or promote himself to owner.
6. Remove him: he immediately sees nothing.
7. Delete the throwaway account.

Before applying the migration, I ran every migration locally in PGlite with four identities: owner, member, non-member and anon. After applying it, the e2e suite passed 9 of 9.

The migration also checks itself. A fixed list of tables can miss a policy or table created by hand, so at the end it queries pg_policies and pg_class and rolls everything back if any policy for authenticated skips the member functions, or any table in public is readable without RLS:

select string_agg(schemaname || '.' || tablename || ' / ' || policyname, '; ') into bad
  from pg_policies
 where schemaname in ('public', 'storage')
   and roles && array['authenticated', 'public']::name[]
   and coalesce(qual, '') || coalesce(with_check, '') !~ '(is_member|is_owner)\(\)';
if bad is not null then raise exception 'workspace_members: policies outside the member rule: %', bad; end if;

Then run the Security and Performance Advisors in the dashboard, or supabase db advisors from the CLI.

Common mistakes

• Forgetting RLS on a new table. Supabase's docs say that on existing projects, a new table in public starts with privileges granted to anon, authenticated and service_role; a table in an exposed schema without RLS is readable and writable by any role with a grant. This happened to me: my Studio migration never granted anything to anon, yet the tables came up with anon privileges. RLS was already on with authenticated-only policies, so nothing leaked, but I wrote a follow-up migration to revoke anon anyway. You can turn off "Automatically expose new tables" in the Data API settings. The Security Advisor rates rls_disabled_in_public as ERROR.
• Assuming the service role goes through RLS. Secret keys (which replace the legacy service_role key) run as the service_role role, which has bypassrls and ignores every policy. Keep them on the server, never in a browser. The docs also note that a secret key does not bypass RLS when the request carries a user's access token.
• Views bypassing RLS. A view runs with its creator's privileges by default (usually postgres), so the underlying tables' RLS doesn't apply. On Postgres 15 and later, create it with (security_invoker = on) and it applies the caller's policies. My back office's summary views are built that way, so when the underlying policies changed, the views followed automatically. The Security Advisor's security_definer_view lint catches the rest.
• using (true) for authenticated. Every signed-in user can read and write. A few of my older tables sat like that for months, until adding a second account exposed it.
• Policies that read user_metadata. Users can edit their own user metadata, so a policy based on it lets them grant themselves access. The advisor rates rls_references_user_metadata as ERROR.
• Several permissive policies for the same command. PostgreSQL ORs them, so one permissive policy is enough to let a row through; leave an old loose policy behind and your new strict one does nothing. That's why my migration drops every authenticated policy on the affected tables before creating the new ones.
• Using RLS as your filter. Supabase recommends still adding .eq('user_id', …) style filters to queries; in their benchmark the same policy went from 171 ms to 9 ms with the filter added. RLS blocks, query filters narrow; write both.

FAQ

Do I have to enable RLS on Supabase?

On any table the browser reaches through the Data API, yes. Supabase says to enable RLS on every table and view exposed through the Data API. On my site, even tables that only the server touches with a secret key have RLS on with no policy for anon or authenticated, so the default is to deny.

Does RLS slow queries down?

A badly written policy can, by hundreds of times. Wrap functions in (select …), index the columns policies use, and name to authenticated, and most of the cost goes away.

Does it change what I pay?

RLS itself costs nothing extra, but slow policies eat into your database's compute. For how Supabase billing works, see Supabase pricing.

I write most of my migrations and policies together with AI. When it runs SQL through the Supabase MCP server, statements like dropping a table or wiping data are stopped by a hook so I can confirm them first; the setup is in my Claude Code tutorial. I still read every permission rule myself: a wrong policy doesn't throw an error, it just quietly gives out too much or too little.

References (checked 2026-11-03)

• Supabase: Tables and Data
• Supabase: Row Level Security
• Supabase: RLS Performance and Best Practices
• Supabase: Securing your API
• Supabase: API keys
• Supabase: Testing Your Database, pgTAP
• Supabase: Database Advisors
• PostgreSQL: CREATE POLICY, Row Security Policies