RLS policy patterns for multi-tenant rows

Row-level security turns "did I remember the where clause?" into a property of the table rather than a property of every query. That is worth a lot, and it is also a new place to make mistakes. These are the patterns that cover almost every table in a normal SaaS app, and the two failure modes that recur.

Everything below assumes RLS is on and forced:

alter table public.documents enable row level security;
alter table public.documents force row level security;

Pattern 1: owned by a user

The simplest and most common shape.

create policy "documents: read own"
  on public.documents for select
  to authenticated
  using ((select auth.uid()) = user_id);

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

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

Three things are doing work here.

to authenticated. Without a role, the policy also applies to anon, and auth.uid() is null for an anonymous request. Most of the time that denies, but it is an accident that it denies, not a decision.

with check on insert and update. using selects which existing rows you may touch; with check validates the row you are writing. An update policy with only using lets a user take one of their rows and set user_id to somebody else's: moving data out of their tenancy and into another.

(select auth.uid()). The subselect makes Postgres evaluate the function once for the query instead of once per row. On a large table that is the difference between a filter and a per-row function call.

Pattern 2: owned by an organisation

Membership is a table, so the predicate is a lookup. Wrap it in a function so the policies stay readable and the logic lives in one place:

create or replace function public.is_org_member(target_org uuid)
returns boolean
language sql
stable
security definer
set search_path = public, pg_temp
as $$
  select exists (
    select 1
    from public.org_members m
    where m.org_id = target_org
      and m.user_id = (select auth.uid())
  );
$$;

create policy "documents: read own org"
  on public.documents for select
  to authenticated
  using (public.is_org_member(org_id));

security definer lets the function read org_members even if that table's own policies would not allow the caller to. set search_path is not optional: without it, a caller who can create a schema earlier in the search path can redefine what the function body resolves to.

stable tells the planner the result does not change within a statement, so it can be evaluated once per distinct org_id rather than per row.

For a role within the org, take the role as an argument rather than writing a second function per role:

create or replace function public.has_org_role(target_org uuid, minimum text)
returns boolean
language sql stable security definer
set search_path = public, pg_temp
as $$
  select exists (
    select 1 from public.org_members m
    where m.org_id = target_org
      and m.user_id = (select auth.uid())
      and case m.role when 'owner' then 3 when 'admin' then 2 else 1 end
          >= case minimum   when 'owner' then 3 when 'admin' then 2 else 1 end
  );
$$;

Pattern 3: staff override

Support needs to read everything. Add a separate policy rather than complicating the first one: policies for the same command are OR-ed together, so each one stays a single readable clause:

create policy "documents: admins read all"
  on public.documents for select
  to authenticated
  using (public.auth_is_admin());

auth_is_admin() reads app_metadata.role from the JWT, which only the service-role key can write. A role in user_metadata would be user-writable and this policy would be a self-service admin button.

Pattern 4: public read, owner write

create policy "posts: public read published"
  on public.posts for select
  to anon, authenticated
  using (published_at is not null);

create policy "posts: author writes"
  on public.posts for update
  to authenticated
  using ((select auth.uid()) = author_id)
  with check ((select auth.uid()) = author_id);

Note that the public policy filters on published_at. using (true) on a table that contains drafts publishes the drafts.

The performance failure

Every policy is a predicate on every query against the table. That means the columns your policies filter on need indexes, and the join tables they read need them too:

create index on public.documents (user_id);
create index on public.documents (org_id);
create index on public.org_members (user_id, org_id);
create index on public.org_members (org_id);

Symptoms of missing ones: a query that is instant with ten rows and takes seconds with fifty thousand, and an explain analyze full of Seq Scan under a filter you did not write.

Check the plan with the policy applied, not as the owner:

set local role authenticated;
set local request.jwt.claims = '{"sub":"<uuid>","role":"authenticated"}';
explain analyze select * from public.documents limit 20;

The correctness failure

An empty list in the UI when the rows exist. Almost always one of:

• RLS enabled with no policy for that command: denies everything.
• The policy is for all and the update path needs a with check that is not there.
• The query runs as anon because the request had no session, and the policy is to authenticated.
• The client is the admin one, which bypasses policies, so the other half of your app looks fine while this half is a hole.

unwrap() in src/lib/auth/rls.ts exists for the first three: it turns a silent empty result into a thrown error with the table and the message, instead of a list that renders as "no results".

Checking your work

• Every table in public has RLS enabled and at least one policy.
• Every update policy has both using and with check.
• Two accounts in two browsers see only their own rows.
• explain analyze under set local role authenticated uses an index.
• bun run verify passes, which fails on any table with RLS off.