Supabase Row-Level Security for Multi-Tenant Apps: A Guide

Multi-tenant Supabase row-level security comes down to one column and one rule: every tenant-owned table has an organization_id, and every policy requires organization_id = (select current_user_org()), where current_user_org() reads the caller's organization from their profile. Add role conditions inside that tenant check, fill organization_id automatically on insert, index it everywhere, and test isolation as two real users from two organizations. This guide shows the full pattern as it runs in LetRelay.

Why shared tables, not a schema or database per tenant

There are three classic multi-tenant designs:

On a free or small plan, shared tables are the only design that fits: Supabase's Free plan gives you a single 500 MB database per project and two active projects. Shared tables put all the weight on your policies being correct — which is why the rest of this guide is about making them boringly correct. OWASP's multi-tenant security cheat sheet makes the same point: tenant context must be enforced at the data layer, not trusted from the client.

Step 1: the tenant column

Every table holding tenant data gets:

organization_id uuid not null references public.organizations(id) on delete cascade

not null because a row without a tenant is a row every tenant's policy will argue about. on delete cascade because deleting an organization should delete its data, not orphan it. And an index, because every policy filters on it:

create index if not exists idx_requests_org on public.requests (organization_id);

For hot queries, lead composite indexes with the tenant: (organization_id, status, sla_due_at).

Step 2: helper functions

Policies need the caller's organization and role. Reading them from profiles inside a policy on profiles would recurse, so use small security definer functions:

create or replace function public.current_user_org()
returns uuid
language sql stable security definer set search_path = public
as $$ select organization_id from public.profiles where id = auth.uid() $$;

Rules for these helpers:

• They only return the caller's own facts. No arguments that let a caller look up someone else.
• set search_path so a malicious function with the same name in another schema can't be substituted.
• stable so the planner may cache the result within a statement.

Supabase's docs caution that security definer functions bypass RLS and, in an exposed schema, can be called through the API. That's acceptable for a function that returns "my organization id" — the caller already knows it — and not acceptable for anything broader.

Don't read the tenant from the JWT's user metadata. user_metadata is writable by the user. Either read from a table the user can't write (as above), or put the claim in app_metadata via a server-side hook.

Step 3: policies that combine tenant and role

Here is LetRelay's real select policy on requests:

create policy requests_select on public.requests for select to authenticated
using (
  organization_id = (select public.current_user_org())
  and (
    requester_id = (select auth.uid())
    or (select public.current_user_role()) = 'admin'
    or ((select public.current_user_role()) = 'agent'
        and (team_id = (select public.current_user_team()) or team_id is null))
  )
);

Read it as two layers: the outer organization_id = … is the tenant wall, and the inner or block is the role logic within the tenant. Every policy on every tenant table starts with that same wall. The (select …) wrappers let Postgres evaluate each helper once per statement rather than once per row.

Roles and their trade-offs are covered in role-based access for internal tools.

Step 4: inserts and updates need with check

using filters rows you can see; with check validates rows you write. Without with check, a user could insert a row into another tenant:

create policy requests_insert on public.requests for insert to authenticated
with check (
  organization_id = (select public.current_user_org())
  and requester_id = (select auth.uid())
);

For updates, use both: using decides which rows you may update, with check ensures the updated row still belongs to your tenant — so nobody can "move" a row into another organization by changing its organization_id.

Step 5: fill the tenant automatically

Clients shouldn't have to send organization_id at all. A before insert trigger fills it from the caller:

create or replace function public.set_org_id()
returns trigger language plpgsql security definer set search_path = public
as $$
begin
  if new.organization_id is null then
    new.organization_id := public.current_user_org();
  end if;
  return new;
end $$;

A client that sends a foreign organization id is still rejected by the with check clause. The trigger is convenience; the policy is the guarantee.

Step 6: lock down what users can change about themselves

The most dangerous row in a multi-tenant app is the user's own profile, because it holds their organization and role. If a user can update profiles.role or profiles.organization_id, every policy above is decoration. Options:

• A before update trigger that rejects changes to role and organization_id unless made by an admin of the same organization (or the service role).
• Column-level privileges: revoke update (role, organization_id) on profiles from authenticated.

We use a trigger, because it can express "admins of the same organization may change roles, except the owner's".

Step 7: cross-tenant features without breaking the wall

Some features legitimately look across tenants: a site owner's console, global search results, platform analytics. Keep them out of normal policies:

• Implement them as security definer functions that check a specific privilege first (for example, an allowlist of site-owner emails) and return only the fields they need.
• Or run them server-side with the service role, behind an authenticated admin route.

Never widen a normal policy with or is_site_owner() "just for now". That clause will be copied.

Step 8: prove it

Write tests that act as real users, not as the service role:

1. Create organizations A and B, each with a requester, an agent and an admin.
2. As each user in A, try to read, insert, update and delete rows belonging to B in every table.
3. Assert nothing is returned and nothing changes.
4. Try inserting a row with organization_id set to B's id. It must fail.
5. Try updating your own profile's organization_id and role. They must fail.

Run this whenever a migration adds a table. LetRelay runs it as part of every verification pass; a new table without policies shows up as a failure immediately.

Performance checklist

• organization_id indexed on every table; composite indexes lead with it.
• Helpers wrapped in (select …) in every policy.
• Helpers are stable, tiny, and read an indexed primary key (profiles.id).
• Policies scoped with to authenticated (and to anon only where public reads are intended).
• Avoid policies that join large tables per row; precompute membership in a small table if needed.

For the broader argument about why this belongs in the database, see row-level security vs app-layer authorization, and for the full app built on it, building an internal help desk with Next.js and Supabase.

FAQ

Should the tenant id come from the JWT or a table?

Either can work. Reading from a profiles table the user can't modify is simplest and always current. If you use JWT claims, put them in app_metadata (server-controlled), never user_metadata, and remember claims can be stale until the token refreshes.

Can one user belong to several organizations?

Yes, with a membership table (user_id, organization_id, role) instead of a single column on profiles, and a notion of the "active" organization. Policies then check membership, which needs an index on (user_id, organization_id).

Does RLS protect Supabase Realtime?

Postgres change subscriptions respect RLS, so each subscriber only receives rows its policies allow. For membership-gated data, broadcasting from trusted server code can be simpler.

What if I need to bypass RLS for a background job?

Use the service-role key on the server only, and keep those code paths few and reviewed. Everything a user triggers should run with the user's own session.

Sources

• Supabase — Row Level Security
• Supabase — Database functions
• PostgreSQL — CREATE POLICY
• PostgreSQL — CREATE FUNCTION
• OWASP — Multi-Tenant Security Cheat Sheet
• Supabase — Pricing