Supabase RLS for Multi-Tenant Apps: Policies That Hold Up

For Supabase RLS in a multi-tenant app, store each user's tenant and role in a membership table, write security definer helpers that answer "which tenants, at what role?", and make every policy check rows against them. Wrap auth.uid() in a select, index tenant columns, and test isolation in SQL. Then the database refuses cross-tenant reads, whatever the app does.

TL;DR: Enable RLS on every exposed table, put membership lookups in security definer functions with an empty search_path, and write separate read, write, and member-level policies. Supabase's docs say the service role bypasses RLS and that RLS isn't applied to Realtime DELETE events, so handle both explicitly. Test policies with pgTAP on every migration.

GymFast, our gym management platform, enforces a six-level role hierarchy, from owner to member, in the database with row-level security rather than in app code. The SQL below is an example pattern for that kind of app, with simplified table names. It isn't GymFast's actual schema, but it's the shape we'd start any multi-tenant Supabase project with.

Why put permissions in the database at all?

Because with Supabase, the database is often the only layer between a user and your data. Clients call Postgres through an auto-generated API, frequently straight from a browser or phone. If a policy is missing, anyone with a valid session can query the table directly, whatever your screens show.

Row-level security moves "who can see this row" into Postgres, so every query from every client is filtered. Supabase's docs say to enable RLS on every table in an exposed schema, because without it any role with a grant on the table can read and write it (Supabase docs).

For a product with several apps on one backend, this matters even more. GymFast's web dashboard and its staff, member, and trainer apps all hit the same tables. Writing permission checks four times, in four clients, is how leaks happen. Writing them once, in Postgres, is how they stop.

What does the multi-tenant data model look like?

Three kinds of table carry the pattern: one row per tenant, a membership table linking users to tenants with a role, and tenant-owned tables that each carry a tenant ID. Roles are ordered, so a policy can ask "is this user at least a manager here?" instead of listing every role.

Table	Holds	Notes
gyms	One row per tenant	Everything else points here
gym_members	user_id, gym_id, role	Who belongs where, as what
Tenant tables	Plans, invoices, classes, with a gym_id column	Every row knows its tenant

A Postgres enum compares by declaration order, which gives you ordered roles for free. The six names here are illustrative:

create type public.gym_role as enum
  ('member', 'trainer', 'front_desk', 'manager', 'admin', 'owner');

Helper functions that avoid recursion

The helpers avoid recursion by reading the membership table as its owner, not as the caller. A policy on gym_members that queries gym_members recurses forever. A security definer function in a private schema reads the table without triggering its own policies, and policies call that function instead.

create schema if not exists private;

-- Gyms where the caller holds at least min_role.
create function private.my_gym_ids(min_role public.gym_role)
returns setof uuid
language sql
security definer
set search_path = ''
stable
as $$
  select gym_id from public.gym_members
  where user_id = (select auth.uid())
    and role >= min_role
$$;

-- Does the caller hold at least min_role in this gym?
create function private.has_gym_role(target_gym uuid, min_role public.gym_role)
returns boolean
language sql
security definer
set search_path = ''
stable
as $$
  select exists (
    select 1 from public.gym_members
    where user_id = (select auth.uid())
      and gym_id = target_gym
      and role >= min_role
  )
$$;

revoke execute on function private.my_gym_ids(public.gym_role) from public;
revoke execute on function private.has_gym_role(uuid, public.gym_role) from public;
grant usage on schema private to authenticated;
grant execute on function private.my_gym_ids(public.gym_role) to authenticated;
grant execute on function private.has_gym_role(uuid, public.gym_role) to authenticated;

Supabase's guidance is to set search_path = '' on every security definer function and schema-qualify every name inside it, so a caller can't redirect a name to an object they control (Supabase docs). That's why the enum is written public.gym_role even inside the function.

Which policies does a multi-tenant table need?

At least three: a read policy for staff, a write policy with a role check, and a member policy that limits members to their own rows. Postgres combines permissive policies with OR, so each one widens access for one kind of user and nothing more.

alter table public.invoices enable row level security;

-- Staff from front desk upward read every invoice in their gyms.
create policy "staff read invoices"
on public.invoices for select to authenticated
using ( gym_id in (select private.my_gym_ids('front_desk')) );

-- Members read only their own invoices.
create policy "members read own invoices"
on public.invoices for select to authenticated
using ( member_user_id = (select auth.uid()) );

-- Managers and above create invoices, only inside their own gyms.
create policy "managers insert invoices"
on public.invoices for insert to authenticated
with check ( private.has_gym_role(gym_id, 'manager') );

create policy "managers update invoices"
on public.invoices for update to authenticated
using ( private.has_gym_role(gym_id, 'manager') )
with check ( private.has_gym_role(gym_id, 'manager') );

Notice the staff read policy starts at front_desk. Members are rows in gym_members too, so a helper that returned every gym the user belongs to would let any member read every invoice in the gym. That's the most common bug we look for in role-based RLS: the tenant check passes, and nobody checked the role.

The with check clauses matter as much as using. Without one, a manager in gym A could insert a row stamped with gym B's ID, and the read policy would never notice.

Keeping RLS policies fast

RLS runs on every row a query touches, so a slow policy makes every screen slow. Supabase's performance advisor flags policies that call auth.uid() once per row instead of once per query, and the fix is to wrap the call as (select auth.uid()) so Postgres evaluates it once and caches it (Supabase advisors).

Two more rules cover most of the rest. Add an index on every column a policy filters on, including gym_id on tenant tables and gym_members (user_id, gym_id). And filter in your queries too: a query that says where gym_id = $1 lets the planner use the index directly, and the policy becomes a safety net rather than the main filter.

One caution: the wrap-in-select trick only helps when the expression doesn't depend on the row. has_gym_role(gym_id, ...) uses the row's gym_id, so it runs per row. Keep functions like that small and indexed.

Where does RLS not protect you?

In three places: server code that uses the service role, Realtime delete events, and views. Each one is documented, and each one is easy to miss on a busy week. Here's what to do about each.

The service role. Supabase's docs say the service role bypasses RLS and must stay server-side. Scheduled jobs often use it; GymFast's reminders and expiry jobs run as scheduled edge functions. In any job that acts for one gym, filter by gym_id explicitly. When a function acts for a user, call Supabase with that user's token so RLS still applies.

Realtime deletes. Postgres Changes authorizes each event against the subscriber, but Supabase's docs state that RLS policies aren't applied to DELETE events, because Postgres can't verify access to a row that no longer exists (Supabase Realtime docs). Treat a delete event as "refetch this list", not as trusted data, and don't subscribe clients to deletes on tables where the deleted row's existence is itself sensitive.

Views. By default, Postgres checks a view's underlying tables with the view owner's permissions and RLS policies. Creating the view with security_invoker = true applies the calling user's policies instead (PostgreSQL docs). A view without it can expose rows your policies hide.

Storage follows the same model: access is controlled by RLS policies on storage.objects, and helpers like storage.foldername(name) let you match on a path (Supabase Storage docs). Put the tenant ID first in every object path.

How do you test Supabase RLS policies?

In SQL, as the user, on every migration. Supabase supports pgTAP tests run with supabase test db (Supabase testing docs). Each test switches to the authenticated role, sets a user's ID in the JWT claims, and tries something that user shouldn't be able to do.

begin;
select plan(3);

-- Fixtures (two gyms, a manager in gym A, a member in gym A) inserted above.
set local role authenticated;
set local request.jwt.claims = '{"sub": "00000000-0000-0000-0000-00000000000a"}';

select is_empty(
  $$ select id from public.invoices
     where gym_id = '00000000-0000-0000-0000-0000000000b0' $$,
  'manager in gym A cannot read gym B invoices'
);

select throws_ok(
  $$ insert into public.invoices (gym_id, member_user_id, amount)
     values ('00000000-0000-0000-0000-0000000000b0',
             '00000000-0000-0000-0000-00000000000a', 100) $$,
  '42501'
);

set local request.jwt.claims = '{"sub": "00000000-0000-0000-0000-00000000000c"}';
select results_eq(
  $$ select count(*)::int from public.invoices
     where member_user_id <> '00000000-0000-0000-0000-00000000000c' $$,
  $$ values (0) $$,
  'a member sees only their own invoices'
);

select * from finish();
rollback;

Run that suite in CI against a local Supabase instance, and check the dashboard's security advisor after each migration: it flags public tables with RLS disabled as an error (Supabase advisors). The wider isolation checklist, beyond the database, is in the multi-tenant SaaS checklist.

Where this pattern stops

RLS is a strong default, not the only control. Rate limiting, audit logs, and admin tooling live elsewhere. Vector search needs its own isolation rules, covered in keeping users' data apart in vector search, and the same idea on Django is in Django REST Framework multi-tenant. If you ever leave Supabase, the policies are plain Postgres and move with the database. Platforms are compared on the Supabase vs Firebase page.

FAQ

Should the user's role live in the JWT or in a table?

A table is easier to change and audit, and a role change takes effect on the next query. Claims in the token are cheaper to read but stay stale until the token refreshes, so a demoted manager keeps manager rights for a while. Use a table for roles that change; use claims only for stable facts.

Can one user belong to several tenants?

Yes, and the membership table handles it naturally: one row per user per tenant, each with its own role. The helpers return every gym where the user holds the role, so a trainer who works at two gyms sees both. The app should still let the user pick an active tenant for the screen.

Does RLS replace authorization checks in Edge Functions?

Only when the function calls Supabase with the user's own token. Functions that use the service role bypass every policy, so they need explicit checks: filter by tenant in every query and confirm the caller's role before writing. Keep service-role functions few, small, and reviewed.

How do you audit an existing Supabase project's policies?

Start with the security advisor for tables without RLS, then read each policy for missing with check clauses, role checks that stop at tenant membership, recursion, and per-row function calls. Finally, look for the service role key anywhere outside server code. Those five checks find most real problems.

Building on Supabase, or worried your current policies leak? See our Supabase development work, or hire a Supabase developer to review your row-level security.