# RLS policy patterns for multi-tenant rows

> Owner-scoped, org-scoped and role-scoped policies, the with-check clause people forget, and the indexes that stop a policy from turning every read into a scan.

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:

```sql
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.

```sql
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:

```sql
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:

```sql
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:

```sql
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

```sql
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:

```sql
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:

```sql
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.

---

Agentic Boilerplate: A Next.js repo your agent already knows. Free during launch, then $99 once.

- Site map for agents: https://agenticboilerplate.com/llms.txt
- Public API: https://agenticboilerplate.com/openapi.json
- Contact: agenticstudio@gmail.com