Supabase RLS Policies: Real-World Examples for Tenant Isolation

Row Level Security is the feature that lets a Supabase project ship a multi-tenant app without writing a single line of authorization code in the API layer. It is also the feature most teams misconfigure on their first production deploy. A policy that looks correct in the dashboard can quietly return another tenant's invoices because of a missing WITH CHECK clause, an implicit OR between policies, or a using (true) that someone added during a late-night debugging session and forgot to remove.

This article walks through RLS from the database side. We start with the Postgres primitives that Supabase wraps, build a tenant isolation model that survives audit, layer role-based access on top, and end with the five pitfalls that show up in roughly 80% of the policy reviews I have done. The examples target Supabase Auth and the auth.uid() / auth.jwt() helpers, but the underlying SQL is plain Postgres and ports to any project running PostgREST or a similar JWT-aware gateway.

Why RLS belongs in the database, not the API

A common alternative is to enforce tenant isolation in application code. Every query passes a tenant_id parameter, every route checks the JWT, every list endpoint adds where tenant_id = $1. This works until it doesn't. A new developer writes a one-off report query, forgets the filter, and suddenly the daily metrics email contains rows from three customers. A background job uses a service role for convenience and bypasses the check entirely. The application layer is the wrong place to enforce a security boundary because there is no compiler that fails the build when a query is missing a filter.

Postgres RLS flips the model. The filter lives on the table itself. Every SELECT, INSERT, UPDATE, and DELETE runs through the policy regardless of which client issued it, which framework wrapped it, or whether the engineer remembered the filter. The PostgREST layer that Supabase exposes simply forwards the JWT, and Postgres evaluates the policy with the claims available as current_setting('request.jwt.claims', true). You can read the upstream Postgres documentation on the feature at https://www.postgresql.org/docs/current/ddl-rowsecurity.html and the Supabase guide at https://supabase.com/docs/guides/database/postgres/row-level-security.

The tradeoff is debugging. When a query returns zero rows you cannot tell from the client whether the row does not exist or whether the policy filtered it out. That single property forces a different test discipline, which we cover at the end.

Anatomy of a policy

A Supabase RLS policy has four moving parts: the command it covers, the role it applies to, the USING expression that filters reads (and the read side of writes), and the WITH CHECK expression that validates writes. Forgetting one of them is the most common cause of leaks.

-- Enable RLS first. Without this line, the policies below are inert.
alter table public.invoices enable row level security;

-- Force RLS even for the table owner. Skip this and your migration scripts
-- silently bypass every policy.
alter table public.invoices force row level security;

create policy "tenants read own invoices"
on public.invoices
for select
to authenticated
using (tenant_id = (auth.jwt() ->> 'tenant_id')::uuid);

create policy "tenants insert own invoices"
on public.invoices
for insert
to authenticated
with check (tenant_id = (auth.jwt() ->> 'tenant_id')::uuid);

Two policies, one for reads and one for inserts. The USING clause filters which existing rows the user can see, and WITH CHECK validates the row a user is trying to write. They are different concepts even though they often look identical. A policy that defines USING but not WITH CHECK on an UPDATE lets a tenant rewrite the tenant_id column to another tenant's id, then immediately lose visibility of the row they just stole.

Multiple policies on the same command are combined with OR, not AND. If you write one policy that says "owner can read" and a second that says "admin can read everything", a user who matches either branch can read the row. That OR semantic is convenient for role layering but dangerous if you intended to require both conditions.

Real-world example: SaaS with tenants, users, and projects

A typical SaaS schema has three layers of authorization. A tenant owns billing. A user belongs to one or more tenants through a membership table. A project belongs to a tenant, and resources hang off projects. The JWT carries the active tenant_id because users can switch tenants without re-authenticating.

create table tenants (
  id uuid primary key default gen_random_uuid(),
  name text not null,
  plan text not null default 'free'
);

create table memberships (
  tenant_id uuid references tenants(id) on delete cascade,
  user_id uuid references auth.users(id) on delete cascade,
  role text not null check (role in ('owner', 'admin', 'member', 'viewer')),
  primary key (tenant_id, user_id)
);

create table projects (
  id uuid primary key default gen_random_uuid(),
  tenant_id uuid not null references tenants(id) on delete cascade,
  name text not null,
  created_by uuid references auth.users(id)
);

create index projects_tenant_idx on projects(tenant_id);

The membership table is the source of truth for "does this user belong to this tenant", and it carries the role. Instead of stuffing roles into JWT custom claims, we look them up at policy evaluation time. This costs one index lookup per row check but it survives role changes without forcing a token refresh.

alter table projects enable row level security;
alter table projects force row level security;

create policy "members read tenant projects"
on projects
for select
to authenticated
using (
  exists (
    select 1
    from memberships m
    where m.tenant_id = projects.tenant_id
      and m.user_id = auth.uid()
  )
);

create policy "admins write projects"
on projects
for insert
to authenticated
with check (
  exists (
    select 1
    from memberships m
    where m.tenant_id = projects.tenant_id
      and m.user_id = auth.uid()
      and m.role in ('owner', 'admin')
  )
);

The read policy lets any member of the tenant list projects. The write policy is stricter: only owner or admin can insert. To make UPDATE and DELETE consistent, repeat the admin pattern for those commands with matching USING and WITH CHECK clauses. PostgREST will run them in a single transaction with the JWT bound to request.jwt.claims, so auth.uid() resolves to the calling user without any application code.

Role-based access without a custom claims dance

The temptation is to bake roles into the JWT. Supabase Auth supports custom claims via the raw_app_meta_data column on auth.users, and a hook can copy roles into the JWT at sign-in. This is fast to read inside policies because no join is needed. The problem appears when you change a user's role. The JWT still carries the old role until it expires, which on Supabase defaults to one hour. A user demoted from admin to member keeps admin powers for up to 60 minutes.

Database-backed roles via the memberships table cost an extra row lookup per policy but reflect changes immediately. For audit-sensitive systems – healthcare, finance, anything with a compliance officer – choose the database lookup. For high-read public catalogs where roles change rarely, JWT claims are fine. The two strategies can coexist: use JWT claims for the tenant id (rarely changes per session) and a membership lookup for the role.

A small optimization helps when policies join the same table repeatedly: wrap the lookup in a STABLE SECURITY DEFINER function. Postgres can then evaluate it once per row instead of recompiling the subquery.

create or replace function public.has_tenant_role(
  t_id uuid,
  required_roles text[]
)
returns boolean
language sql
stable
security definer
set search_path = public
as $$
  select exists (
    select 1
    from memberships m
    where m.tenant_id = t_id
      and m.user_id = auth.uid()
      and m.role = any(required_roles)
  );
$$;

revoke all on function public.has_tenant_role(uuid, text[]) from public;
grant execute on function public.has_tenant_role(uuid, text[]) to authenticated;

Now the admin policy becomes one line: using (public.has_tenant_role(tenant_id, array['owner', 'admin'])). Readable, indexable, and the planner can short-circuit on the exists clause.

Five pitfalls that leak data

The first pitfall is forgetting WITH CHECK on writes. A policy with USING only protects reads. Without WITH CHECK, a malicious or buggy client can update a row to a value the policy would never have allowed on insert. Always pair the two on every write command.

The second pitfall is missing FORCE ROW LEVEL SECURITY. By default, the table owner bypasses RLS. Migration scripts that run as the superuser see every row, which is intended for the dashboard but unsafe for any function that runs as a logical owner. The force variant closes that gap.

The third pitfall is the implicit OR between policies. Two policies, one strict and one loose, combine permissively. If you have a "user reads own rows" policy and an "admin reads all rows" policy, the union is what runs. Audit every new policy in the context of the existing ones, not in isolation.

The fourth pitfall is using service_role for convenience inside Edge Functions or backend workers. The service role bypasses RLS entirely. Treat it like a database superuser: useful for explicit admin scripts, dangerous as a default for anything that processes user input. Where you need elevated access, use a SECURITY DEFINER function with a narrow surface and an assert on the calling user's claims.

The fifth pitfall is testing only the happy path. The leak rarely shows up when user A queries their own rows. It shows up when user A queries with a manually crafted tenant_id filter for user B's tenant. Your test suite should include a fixture where two tenants exist with overlapping resource ids, and every list endpoint asserts that cross-tenant queries return zero rows. The supabase-js client and the upstream PostgREST docs at https://postgrest.org/en/stable/references/auth.html describe exactly how the JWT travels and what claims the policy sees.

Testing policies without losing your mind

Write a SQL test fixture that creates two tenants, three users with different memberships, and a handful of projects. Then use set local role authenticated and set local request.jwt.claims = '...' inside a transaction to impersonate each user. Run the same select from each session and assert the row counts. A 12-line pgTAP test catches the cross-tenant leak that an integration test against the HTTP API would miss because the HTTP layer never sees the rows the policy filtered.

For production confidence, add a CI job that diffs the policy DDL across migrations and requires sign-off when a USING or WITH CHECK clause changes. RLS is the kind of code where a one-character typo (auth.uid() versus auth.uid returning a function reference) silently disables the filter. Treat policy changes the same way you would treat schema changes to a users table.

The mental model that stuck with me is this: every RLS policy is a contract between the database and a class of users. The contract is in SQL, lives next to the data, and runs on every query whether or not the application remembers to ask for it. When the contract is good, the application code shrinks. When the contract is wrong, the leak is silent. Either way, the policy is the single source of truth, which is exactly where authorization belongs.

References: