Multi-Tenant SaaS With Supabase: RLS Patterns That Scale

The default way to build a multi-tenant Supabase app is shared tables: every tenant-owned row carries an org_id, and Row Level Security policies return only rows whose org_id belongs to an organisation the signed-in user is a member of. Schema per tenant and project per tenant are worth their cost only when customers need different tables, or a contract demands a physically separate database.

Checked October 2026 against Supabase's Row Level Security guide, RLS performance guide, RLS performance test results, custom schemas, compute pricing and advanced pgTAP testing pages, plus the PostgreSQL 18 docs on row security. The SQL adapts Supabase's documented patterns to an organisations schema; run the test file before you trust it with your own tables.

This is the design guide: which tenancy model to pick, the tables and helper functions for the shared-table model, what the policies do and do not protect, and the indexes that keep them fast. It assumes you know what a policy is. If using versus with check is still fuzzy, read Supabase RLS explained first, then come back. Both sit under our hub on how to build an AI SaaS.

Which multi-tenant Supabase model should you pick?

There are three real options, and they differ in where the wall between customers sits: in a column, in a schema, or in a whole database.

The cost line is the one founders underestimate. Each Supabase project is a dedicated Postgres instance, and compute is billed per project by the hour: Micro is $0.01344 an hour, about $10 a month, and the Pro plan's $10 compute credit covers one project (checked October 2026). Following the billing examples in Supabase's docs, ten tenant projects on Micro come to roughly $25 for Pro plus $100 of compute minus the $10 credit, about $115 a month before any usage, where the shared-table model is still on one project.

The quiz: five questions that decide it

Answer in order and stop at the first row that gives you a model. Most products stop at question 3.

Two notes on reading the result. First, the models mix: nothing stops you running shared tables for everyone and moving a single enterprise customer to its own project when a contract pays for it. Second, "our customers are worried about security" is not a yes to question 1. Start by offering a tested RLS boundary and a clear answer about backups; a requirement for a separate database is usually written into a contract, and you will know when you have one.

One reason schema per tenant disappoints on Supabase in particular: every signed-in user shares one Postgres role, authenticated. Grants are per role, so a schema you expose to the Data API is reachable by every signed-in user unless policies inside it say otherwise. With the standard roles, separate schemas give you separate namespaces, not separate permissions. You still write RLS, and now you write it once per tenant.

How do you build a multi-tenant SaaS on Supabase with shared tables?

Three tables carry the whole model: who the tenants are, who belongs to which, and an example of tenant-owned data. Everything else you add later is a copy of the third.

create table public.organizations (
  id uuid primary key default gen_random_uuid(),
  name text not null,
  created_at timestamptz not null default now()
);

create table public.org_members (
  org_id uuid references public.organizations (id) on delete cascade,
  user_id uuid references auth.users (id) on delete cascade,
  role text not null check (role in ('owner', 'admin', 'member')),
  primary key (org_id, user_id)
);

-- The primary key covers org_id. Policies look up by user_id, which needs its own index.
create index org_members_user_id_idx on public.org_members using btree (user_id);

-- Every tenant-owned table carries org_id and an index that starts with it.
create table public.projects (
  id uuid primary key default gen_random_uuid(),
  org_id uuid not null references public.organizations (id) on delete cascade,
  name text not null,
  created_at timestamptz not null default now()
);

create index projects_org_id_idx on public.projects using btree (org_id);

Three decisions in that schema are deliberate:

• Membership is its own table, not a column on the user. It lets one person belong to several organisations and gives each membership a role.
• Every tenant table has its own org_id, even where you could reach it through a parent. A policy that reads a column on the row is cheaper and harder to get wrong than one that joins to find the tenant.
• The membership table gets a second index. Supabase's RLS guide makes this exact point: a composite primary key on (team_id, user_id) indexes its first column and no others, and policies look memberships up by user.

Next, enable RLS and set grants. Note what clients are not granted, and that service_role is granted explicitly: on projects with Supabase's new default, announced in April 2026, new tables no longer receive automatic grants for any API role.

alter table public.organizations enable row level security;
alter table public.org_members enable row level security;
alter table public.projects enable row level security;

revoke all on table public.organizations, public.org_members, public.projects
  from anon, authenticated;

-- Clients read organisations and membership. Changes to them go through your server.
grant select on table public.organizations to authenticated;
grant select on table public.org_members to authenticated;
grant select, insert, update, delete on table public.projects to authenticated;

-- Server code using a secret key runs as service_role and needs grants too.
grant select, insert, update, delete
  on table public.organizations, public.org_members, public.projects
  to service_role;

Creating an organisation, inviting a member and changing a role are low-volume, high-consequence operations. Running them in a server route with the secret key, after your code has checked who is asking, keeps the policy surface small: no client can write to org_members at all, so no policy mistake can let a member promote themselves. It also sidesteps a classic trap: a user creates an organisation from the browser and asks for the new row back. Postgres requires a returned row to pass the select policy, the creator is not a member yet, and the insert errors.

Supabase multi-tenant RLS: the helper-function pattern

The obvious policy is a subquery: allow the row if a matching membership exists. It works until the membership table gets a policy of its own. A policy on projects reads org_members, and the policy that decides who can see the member list reads memberships again. Supabase's guide documents where that ends: Postgres raises 42P17, infinite recursion detected in policy.

The documented fix is a security definer function. It reads the membership table as its owner, so the membership table's policy is not evaluated inside it and the cycle never starts. (Supabase notes this relies on the owner being able to bypass RLS, which postgres can; it would not hold on a table set to force row level security.) Two small functions cover most products:

create schema if not exists private;

-- Organisations the caller belongs to.
create function private.user_org_ids()
returns setof uuid
language sql
security definer
set search_path = ''
stable
as $$
  select org_id from public.org_members
  where user_id = (select auth.uid())
$$;

-- Organisations where the caller is an owner or admin.
create function private.user_admin_org_ids()
returns setof uuid
language sql
security definer
set search_path = ''
stable
as $$
  select org_id from public.org_members
  where user_id = (select auth.uid())
    and role in ('owner', 'admin')
$$;

revoke execute on function private.user_org_ids() from public;
revoke execute on function private.user_admin_org_ids() from public;
grant usage on schema private to authenticated;
grant execute on function private.user_org_ids() to authenticated;
grant execute on function private.user_admin_org_ids() to authenticated;

Every line of that boilerplate is doing a job:

• The private schema. A security definer function in an exposed schema is callable over the Data API with its creator's privileges. Keeping helpers in a schema that is not exposed means they can be used by policies but not called from a browser.
• set search_path = '' with every name schema-qualified. Without it, a caller could point an unqualified name at their own object and have it run with the function owner's privileges.
• No parameters. The functions take nothing from the caller and filter on auth.uid(), so they can only ever return the caller's own organisations.
• revoke ... from public, then grant to authenticated. Postgres lets every role execute a new function by default; this narrows it to signed-in users.

Now the policies. Read each one as a sentence about a role, an operation and a set of organisations:

create policy "Members read their organisations"
on public.organizations for select
to authenticated
using ( id in (select private.user_org_ids()) );

create policy "Members read the member list of their organisations"
on public.org_members for select
to authenticated
using ( org_id in (select private.user_org_ids()) );

-- Repeat these four for every table that has an org_id.
create policy "Members read their organisation's projects"
on public.projects for select
to authenticated
using ( org_id in (select private.user_org_ids()) );

create policy "Members create projects in their organisation"
on public.projects for insert
to authenticated
with check ( org_id in (select private.user_org_ids()) );

create policy "Members update their organisation's projects"
on public.projects for update
to authenticated
using ( org_id in (select private.user_org_ids()) )
with check ( org_id in (select private.user_org_ids()) );

create policy "Admins delete their organisation's projects"
on public.projects for delete
to authenticated
using ( org_id in (select private.user_admin_org_ids()) );

The in (select private.user_org_ids()) shape matters twice. Wrapping the call in a select lets Postgres run the function once per statement instead of once per row. And comparing the row's org_id to a set, rather than joining from the membership table back to the row, is the form Supabase's performance guide recommends.

Child tables follow the same four policies. One extra guard is worth adding where a row points at a parent: Postgres foreign-key checks bypass row security, so a policy on tasks that only checks org_id would not stop a task in one organisation pointing at a project in another. A composite foreign key makes the database refuse it:

alter table public.projects
  add constraint projects_org_id_id_key unique (org_id, id);

create table public.tasks (
  id uuid primary key default gen_random_uuid(),
  org_id uuid not null,
  project_id uuid not null,
  title text not null,
  foreign key (org_id, project_id)
    references public.projects (org_id, id) on delete cascade
);

-- Leads with org_id for the policy, and covers the foreign key.
create index tasks_org_id_project_id_idx
  on public.tasks using btree (org_id, project_id);

What do these policies protect, and what do they not?

A tenant boundary is only useful if you know where it stops. This table is the one to review with whoever signs off on security.

The last row is the one to worry about. Stripe webhooks, scheduled jobs and admin scripts run with a secret key, which resolves to service_role and skips every policy. In those code paths, tenant isolation is whatever your where clause says. Take the org_id from something you trust, such as your own mapping from a payment customer to an organisation, and never from a field in the request body.

For server code acting on behalf of a signed-in user, build the Supabase client from that user's session rather than the secret key, so the same policies apply on the server as in the browser. Supabase's docs add a detail worth knowing: a secret key bypasses RLS only when the request carries no user access token.

Tenant ID in the JWT or in a membership table?

The alternative to a lookup is putting the tenant in the token: in app_metadata, or as a claim added by a Custom Access Token Hook, read in policies with auth.jwt(). It is faster per query and it is how Supabase's role-based access control guide carries a user's role. It has a cost that matters for tenancy.

Supabase's RLS guide states the staleness problem plainly: remove a user from a team and update app_metadata, and that is not reflected in auth.jwt() until the user's token is refreshed. For a tenancy boundary, "removed" should mean removed. One rule holds in both designs: never read tenant or role data from user_metadata, because users can edit it themselves.

Which indexes keep multi-tenant RLS fast?

Postgres evaluates a policy against each candidate row, so a policy on an unindexed column turns every read into a scan of every tenant's data. Three indexing rules cover the model above:

1. Index org_id on every tenant table. It is the column every policy filters on.
2. Index user_id on the membership table. The primary key (org_id, user_id) does not help a lookup by user.
3. Start composite indexes with org_id. Supabase's guide notes a column only counts as indexed when it leads a btree index, so (org_id, created_at) serves both the policy and a "latest in this organisation" query, while (created_at, org_id) serves neither well.

How much does the shape of the policy matter? Supabase published timings from its own RLS test suite, for a team-membership policy on a 100,000-row table:

The same page reports a larger case, a one-million-row table with a 1,000-row team table and a user in ten teams: the query timed out when the helper was called per row, took 170 ms once wrapped, and 2 ms with the wrap plus an index on the tenant column. Wrapping and indexing are not alternatives; you need both.

Two limits to know. Supabase's performance guide says that if the set a policy compares against grows past about 1,000 items, the approach may need rethinking; that is a user in a thousand organisations, which few products will meet. And the RLS guide notes that the wrapping trick only applies when the function's result does not depend on the row, which is why the helpers above take no arguments.

How do you test tenant isolation?

You test it with two tenants and a user who belongs to only one. Supabase's guide is specific about shared tables: assert that a member who is not the owner can do what the policies allow, and that a non-member cannot. This file does the second half for projects, in the same pgTAP style as the documented examples:

-- File: supabase/tests/projects_rls.test.sql
-- Run:  supabase test db
begin;
select plan(4);

insert into auth.users (id, email) values
  ('11111111-1111-1111-1111-111111111111', 'a@example.com'),
  ('22222222-2222-2222-2222-222222222222', 'b@example.com');

insert into public.organizations (id, name) values
  ('aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa', 'Org A'),
  ('bbbbbbbb-bbbb-bbbb-bbbb-bbbbbbbbbbbb', 'Org B');

insert into public.org_members (org_id, user_id, role) values
  ('aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa', '11111111-1111-1111-1111-111111111111', 'member'),
  ('bbbbbbbb-bbbb-bbbb-bbbb-bbbbbbbbbbbb', '22222222-2222-2222-2222-222222222222', 'owner');

insert into public.projects (org_id, name) values
  ('aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa', 'A project'),
  ('bbbbbbbb-bbbb-bbbb-bbbb-bbbbbbbbbbbb', 'B project');

-- Act as the member of Org A.
set local role authenticated;
set local request.jwt.claim.sub = '11111111-1111-1111-1111-111111111111';

select results_eq(
  $$select name from public.projects$$,
  array['A project'],
  'a member of A reads only A'
);
select throws_ok(
  $$insert into public.projects (org_id, name)
    values ('bbbbbbbb-bbbb-bbbb-bbbb-bbbbbbbbbbbb', 'planted')$$,
  '42501',
  null,
  'a member of A cannot insert into B'
);
select is_empty(
  $$update public.projects set name = 'renamed'
    where org_id = 'bbbbbbbb-bbbb-bbbb-bbbb-bbbbbbbbbbbb'
    returning name$$,
  'a member of A updates nothing in B'
);

-- Matching no rows is not proof on its own: confirm B's row is intact.
set local request.jwt.claim.sub = '22222222-2222-2222-2222-222222222222';
select results_eq(
  $$select name from public.projects$$,
  array['B project'],
  'the denied update left B intact'
);

select * from finish();
rollback;

Notice the last assertion. A denied update does not raise an error; it matches zero rows and reports success. So the test switches to the other tenant's user and checks the row is unchanged. Copy the file for each tenant table, add cases for the admin-only delete, and run supabase test db in CI so a new table without policies fails the build rather than reaching production.

That list is a week of careful work the first time, and it is the week most tutorials skip. Our AI SaaS Builder program builds the schema, grants and policies as part of one product, then carries it through auth, billing and launch.

What about files and the other surfaces?

Table policies do not cover Supabase Storage. Files have their own policies on storage.objects, and the usual tenant convention is a top-level folder per organisation. This adapts Supabase's documented per-user folder policy to check membership instead:

create policy "Members upload to their organisation's folder"
on storage.objects
for insert
to authenticated
with check (
  bucket_id = 'org-files' and
  (storage.foldername(name))[1] in (select private.user_org_ids()::text)
);

That covers uploads. Reading, replacing and deleting files each need a matching policy, and the client has to upload to a path that starts with the organisation ID. Views need the same care as tables: created normally, a view runs with its owner's privileges and bypasses RLS, so create tenant-facing views with security_invoker = true.

When is schema per tenant or project per tenant the right call?

Project per tenant is right when the isolation requirement is physical and someone is paying for it: a customer that needs its own region, its own backup and restore, or the ability to be deleted by dropping a database. You take on a provisioning pipeline, one migration run per project, and a control-plane table that maps each tenant to its project URL and keys. Budget the per-project compute and say so in your pricing.

Schema per tenant is right when tenants need different tables, which is rare outside platforms that let customers define their own data model. On Supabase each schema must be added to the exposed schemas in API settings and granted to the API roles, and clients choose it per query with supabase.schema(). It can be run at scale: a Supabase blog post on pg_graphql 1.5.7 mentions a project running one schema per tenant with more than 2,200 tenants. Treat that as proof it is possible, not as a recommendation for a first version.

If you are still choosing where the database lives, Postgres hosting compared sets Supabase beside Neon, Railway and RDS, and the Supabase full-stack tutorial covers the project setup this guide assumes.

Multi-tenant Supabase: FAQ