How to write a Supabase RLS policy: 4 patterns for AI-generated schemas

Turning on Row Level Security solves half the problem. The real work is writing a policy that matches the table's actual relationship: does one owner see a row, does a team, does an admin need an override. In AI-generated schemas the policy is usually either missing or copy-pasted into a shape that doesn't fit, and both fail in production in different ways.

RLS is on, a policy exists, and it's still wrong: three repeat mistakes

Seeing "RLS enabled" on a table isn't enough. Three mistakes show up again and again, all while a policy is technically present:

• A write policy with no WITH CHECK. USING restricts what a query can read. Without a separate WITH CHECK on INSERT and UPDATE, a user can still write a row that belongs to someone else.
• Forgetting what auth.uid() does when logged out. For an unauthenticated request, auth.uid() returns null. null = user_id is always false, so the row disappears with no error thrown. Silently empty results make this hard to debug.
• A correct policy that gets ripped out under load. When a query slows down in production, the policy gets disabled "temporarily" and stays that way. This is rarely a syntax bug. It's usually the performance issue further down this page.

AI-generated schemas tend to show all three at once: a policy exists, but it was written once and copy-pasted, never matched to the table's real relationship. Here's how to pick the right one.

Pattern 1: ownership, only the owner sees the row

The most common shape: one row, one owner, only that owner reads or writes it. Profiles, personal orders, private notes all fall here.

alter table public.profiles enable row level security;

create policy "owner_reads_own_row"
on public.profiles for select
to authenticated
using ( (select auth.uid()) = user_id );

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

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

Four separate policies, not one for all policy. select only needs using. insert only needs with check, since there's no existing row yet to filter. update needs both: using decides which rows the query can touch, with check stops the update from moving the row to a new, unauthorized owner.

Wrapping auth.uid() in (select ...) isn't decoration. It's the difference between the function running once per statement and once per row, and the performance section below shows exactly why that matters.

Pattern 2: team and multi-tenant access

A row belongs to a team, not a person, and membership lives in a join table. This is the shape almost every AI-generated multi-tenant schema needs and almost never gets right on the first pass.

create policy "team_member_reads_row"
on public.documents for select
to authenticated
using (
  team_id in (
    select team_id from public.team_users
    where user_id = (select auth.uid())
  )
);

Notice the direction of the comparison: the policy filters team_id against a subquery keyed on the current user, not the other way around. Writing it as auth.uid() in (select user_id from team_users where team_id = documents.team_id) reads the same in plain language, but it forces Postgres to run a correlated subquery per row instead of resolving the user's team set once. Same logic, very different query plan.

Pattern 3: admin override, without a service role key

Reaching for the service role key for an admin panel is the fastest way to lose row-level control entirely: it carries the bypassrls attribute and skips every policy on every table, so there's no per-row visibility left to reason about. A is_admin() check inside the policy keeps admin access filtered by role, auditable, and revocable.

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

create policy "owner_or_admin_reads_row"
on public.orders for select
to authenticated
using (
  (select public.is_admin()) or (select auth.uid()) = user_id
);

The function runs as security definer, so it can read the admins table on the caller's behalf without needing a separate RLS policy that exposes that table to every user. Wrap the function call in (select ...) too, for the same per-statement caching reason as the ownership pattern.

Pattern 4: public read, authenticated write

A blog, a catalog, a public listing: anyone reads, only a signed-in owner or role writes. Split it into two policies with two different to targets instead of one policy trying to do both.

create policy "anyone_reads_published"
on public.articles for select
to anon, authenticated
using ( status = 'published' );

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

Storage buckets follow the same four shapes, but a bucket policy filters storage.objects instead of a table, and the folder structure carries the ownership signal instead of a column. That's its own topic with its own gotchas; our breakdown of what Lovable and Bolt apps leave exposed covers the bucket-specific policy in full.

Pattern	Use it when	Common AI-generated mistake
Ownership	One user, one row	No WITH CHECK on update
Team	Shared rows via a join table	Subquery direction that forces a row-by-row scan
Admin	A role needs an override	Service role key used instead of a scoped check
Public read	Anyone reads, owner writes	One policy trying to cover both directions

Why a policy can slow a query down 1000x

Supabase's own troubleshooting docs publish the numbers, measured on a 100K-row table. Two changes account for nearly all of it.

The index rule is direct: any column referenced inside a policy's using or with check needs a btree index, the same as any other frequent filter.

create index on public.documents using btree (team_id);

The (select ...) wrap works because Postgres can turn it into an initPlan, caching the result for the statement instead of calling the function per row. It only holds when the wrapped value doesn't change row to row, which is true for auth.uid(), auth.jwt(), and a security definer function like is_admin(). Adding to authenticated on every policy helps too: it lets Postgres skip the policy entirely for the anon role instead of evaluating it and getting a false.

Testing a policy for real: pgTAP

A policy that "looks right" and a policy that's actually enforced are different claims. Supabase ships pgTAP support for exactly this, run through the CLI:

supabase test db

A test sets the role and identity the policy will see, then asserts on the outcome:

set local role authenticated;
set local request.jwt.claim.sub = '11111111-1111-1111-1111-111111111111';

select results_eq(
  $$insert into profiles (id, user_id, avatar_url)
    values (gen_random_uuid(), '11111111-1111-1111-1111-111111111111', 'owner.png')
    returning avatar_url$$,
  array['owner.png'],
  'the owner can insert their own profile'
);

Three assertion shapes cover most policies: throws_ok for a write that should be rejected outright with a 42501 permission error, is_empty for a read that should silently return nothing rather than error, and results_eq with a returning clause to prove a write actually landed. Test the denial case as deliberately as the success case. A policy that lets the right user in but never rejects the wrong one usually has no with check at all.

Where these patterns stop being enough

The four patterns above cover the row-level shape of access control. They don't cover a business rule that spans several tables with heavy joins (a security definer function helps, but every join it does still needs its own review), audit and versioning requirements, or a schema whose relationships don't actually match what the product needs yet. If the schema itself needs to change shape before a policy can express the rule, that's a design pass, not a policy fix.

When a codebase came out of an AI builder and nobody has read through what it actually generated, our AI project completion process starts with that reading: which tables carry which relationship, which policies match, which don't.

RLS checklist before you ship

This one is scoped to RLS specifically. For the wider surface (keys, uploads, webhooks, logging), our pre-launch security checklist for AI-written apps covers the rest.

• Every table in public has RLS enabled, no exceptions carved out for convenience.
• Every write policy has a with check, not just using.
• Every policy specifies to authenticated (or anon) instead of leaving the role open.
• Every column referenced inside a policy has a btree index.
• Every auth.uid() and auth.jwt() call inside a policy is wrapped in (select ...).
• At least one pgTAP test per table asserts the denial case, not just the success case.

FAQ

When should I actually use the service role key instead of an is_admin() policy?
Only from trusted server-side code that needs to bypass row filtering entirely, like a migration script or a scheduled job. Never from anything reachable by a client, and never as a shortcut for an admin panel a real user opens.

Do I need to wrap auth.uid() in select everywhere?
Anywhere it appears inside a policy's using or with check, yes, as long as the value doesn't depend on the row being checked. That covers auth.uid(), auth.jwt(), and most security definer helper functions.

Does pgTAP require a local Supabase setup?
It runs through the Supabase CLI against your local dev database, so you need the CLI and a running local stack, not a separate testing service.

Is RLS always going to cost me query performance?
An indexed, correctly wrapped policy adds overhead close to a normal WHERE clause. The 1000x numbers above come from missing an index or re-evaluating a function per row, not from RLS itself.

My table only has one pattern right now. Do I still need four?
No, pick the one that matches the table's real relationship. The four exist so you stop reaching for whichever policy you copied last time.