Security
Is each company's data separated? Multi-tenant RLS in Supabase for teams, organisations and invitations
In a team or B2B app, every row belongs to an organisation, and a user may see it only if they are a member of that organisation. The usual leaks are a policy that checks "is this user a member of some organisation" instead of "of this row's organisation", and a membership table that users can write to themselves. Below: SQL checks you can run on your own project, and a complete setup with a membership table, helper functions, policies and an invitation flow.
What you are seeing
Data leaking between companies is quiet: each customer sees their own data plus someone else's, and nobody notices until a customer does. The moments that should make you check:
- Your first business customer asks whether their data is separated from other companies, and you are not sure how to answer.
- A user in one workspace sees projects, documents, customers or members of another workspace, or a dropdown shows organisations they never joined.
- Queries fail with
infinite recursion detected in policy for relation "organisation_members"(code42P17). Supabase's docs describe this error for policies on two tables that read each other (Avoid recursive policies). Watch what happens next: the fastest change that makes the error go away is a broad policy on the membership table, which shows every company's member list to everyone. - Your app adds people to a team by inserting a row into a members table straight from the browser, and the invite link only contains the organisation id.
- Some of your tables have no column saying which organisation a row belongs to, and the app filters by organisation only in the front-end query.
How to check it yourself
Run these against your own project only. Steps 1 to 4 only read. Step 5 tries changes inside a transaction and rolls them back. Everything runs in the Supabase dashboard under SQL Editor. The examples use the names organisations, organisation_members and projects; your app may say teams, workspaces, team_members or company_id instead - the checks look for all of them.
1. Map which tables hold company data
select c.relname as table_name,
c.relrowsecurity as rls_enabled,
(select string_agg(a.attname, ', ')
from pg_attribute a
where a.attrelid = c.oid
and a.attnum > 0
and not a.attisdropped
and a.attname ~* '(org|organi[sz]ation|team|workspace|company|tenant|account)_?id') as tenant_columns,
(select count(*)
from pg_policies p
where p.schemaname = 'public'
and p.tablename = c.relname) as policies
from pg_class c
join pg_namespace n on n.oid = c.relnamespace
where n.nspname = 'public'
and c.relkind in ('r', 'p')
order by tenant_columns nulls first, table_name;rls_enabled = falseon a table with company data: anyone with your public key may be able to read it. Our open-tables guide covers that case.rls_enabled = trueandpolicies = 0: nobody can read it through the API. Postgres uses a default-deny policy when RLS is on and no policy exists (PostgreSQL: Row Security Policies). Safe, but probably a broken screen.- Company data in a table without an organisation column (for example
tasksthat only point to aproject_id): the policy has to look the organisation up through another table. That works, but every such policy is one more place for a wrong join.
2. Read every policy and look for the wrong join
select tablename, policyname, permissive, cmd, roles, qual, with_check
from pg_policies
where schemaname = 'public'
order by tablename, cmd, policyname;qual is the using condition (which existing rows a user can see or change). with_check is the condition new or edited rows must meet.A correct policy on a company table ties the row's organisation to the caller's memberships. The patterns below look almost right and are not:
EXISTS (SELECT 1 FROM organisation_members m WHERE (m.user_id = auth.uid())): checks that the user belongs to some organisation, not to this row's organisation. Every member of any company sees every company's rows.(organisation_members.organisation_id = organisation_members.organisation_id)inside the condition: the policy was written with an unqualifiedorganisation_idinside the subquery, and Postgres read it as the membership table's own column, compared with itself. This is always true, so the policy is the same as the one above.pg_policiesshows the column with its table name, which is how you can spot it.organisation_id IN (SELECT organisation_id FROM organisation_members)with nouser_idfilter: whether this leaks depends entirely on the policy onorganisation_members. If members can see every membership row, they can see every company's data.- Conditions that read the organisation from
auth.jwt() -> 'user_metadata': users can change their ownuser_metadata, so Supabase's docs warn against using it for authorization (Row Level Security). - Conditions that read the organisation from
app_metadatain the token: users cannot change it, but a token is not updated until it is refreshed, so a removed member keeps access until then (Row Level Security). using (true)on the membership or invitation table: every signed-in user sees who works where, and on an invitation table possibly the invite tokens too.
This query finds the self-comparison from the second item in every policy:
select tablename, policyname, cmd
from pg_policies
where schemaname = 'public'
and (qual ~ '\m(\w+)\.(\w+) = \1\.\2\M'
or with_check ~ '\m(\w+)\.(\w+) = \1\.\2\M');3. Check who can write to the membership and invitation tables
Whoever can insert a row into the membership table can join any organisation. Start with the privileges, then the policies:
select c.relname as table_name,
has_table_privilege('authenticated', c.oid, 'INSERT') as signed_in_can_insert,
has_table_privilege('authenticated', c.oid, 'UPDATE') as signed_in_can_update,
has_table_privilege('authenticated', c.oid, 'DELETE') as signed_in_can_delete
from pg_class c
join pg_namespace n on n.oid = c.relnamespace
where n.nspname = 'public'
and c.relkind in ('r', 'p')
and c.relname ~* '(member|invit|team|organi[sz]ation|workspace|tenant)'
order by table_name;
select tablename, policyname, cmd, roles, qual, with_check
from pg_policies
where schemaname = 'public'
and tablename ~* '(member|invit|team|organi[sz]ation|workspace|tenant)'
and cmd in ('INSERT', 'UPDATE', 'ALL')
order by tablename, cmd;has_table_privilege reports table-wide privileges only; a grant on single columns (such as update (name)) shows as false here.- An insert policy on the membership table such as
with check (user_id = auth.uid()): it checks that you add yourself, not that anyone invited you. Any signed-in user can add themselves to any organisation, with any role the column allows. - An update policy on the membership table that lets members edit their own row: they can change their own
roletoowneroradmin. See our guide to role columns for why row policies do not protect columns. - An insert or update policy on the invitation table that does not require the caller to be an admin of that organisation.
4. List the database functions the app can call
select p.proname as function_name,
n.nspname as schema,
p.prosecdef as security_definer,
has_function_privilege('anon', p.oid, 'EXECUTE') as signed_out_can_call,
has_function_privilege('authenticated', p.oid, 'EXECUTE') as signed_in_can_call
from pg_proc p
join pg_namespace n on n.oid = p.pronamespace
where n.nspname = 'public'
order by security_definer desc, function_name;security_definer = true (Database > Functions in the dashboard) and read what it does.A security definer function runs with the rights of the role that created it, and one in an exposed schema such as public is callable over the Data API with those rights (Use security definer functions). The Security Advisor flags these as lints 0028 and 0029 (Database Advisors). A function like join_team(team_id) or accept_invite(org_id) that adds the caller to an organisation without checking a real invitation is the same hole as an open membership table.
5. Test as a real user: read across companies and join uninvited
You need a test user who belongs to exactly one organisation, and a second organisation they do not belong to. Supabase's own policy tests switch role and identity with set local role and set local request.jwt.claim.sub (Row Level Security). First find the ids:
select m.user_id, u.email, m.organisation_id, m.role
from public.organisation_members m
join auth.users u on u.id = m.user_id
order by u.email;Read test. Replace projects with a table holding company data, and paste the test user's id and their organisation's id:
begin;
set local role authenticated;
set local request.jwt.claim.sub = 'TEST-USER-UUID';
select count(*) as rows_visible,
count(*) filter (where organisation_id is distinct from 'TEST-ORG-UUID'::uuid) as rows_of_other_organisations
from public.projects;
rollback;rows_of_other_organisations must be 0. Repeat for each table with company data, and for organisation_members itself. SQL Editor may show only the result of the last statement: if you see "Success. No rows returned", select the lines from begin; through the select, run only that selection, then run rollback;.Join test. Paste the id of the other organisation:
begin;
set local role authenticated;
set local request.jwt.claim.sub = 'TEST-USER-UUID';
insert into public.organisation_members (organisation_id, user_id, role)
values ('OTHER-ORG-UUID', 'TEST-USER-UUID', 'owner');
reset role;
select organisation_id, user_id, role
from public.organisation_members
where organisation_id = 'OTHER-ORG-UUID'
and user_id = 'TEST-USER-UUID';
rollback;permission denied for table organisation_members or new row violates row-level security policy) is what you want. If the insert succeeds and the final select shows the row, any signed-in user can join any organisation as owner. SQL Editor may show only the result of the last statement (rollback): if you see "Success. No rows returned" and no error, the insert went through, which is the failing case. To see the row, select the lines from begin; through the final select, run only that selection, then run rollback;. If your role column uses other values, use the one that exists in your app. rollback undoes the insert either way.Why it happens
A single-user app needs one comparison per policy: auth.uid() = user_id. A multi-tenant app needs two hops: from the row to its organisation, and from the organisation to the caller's membership. Each hop is a place to get it wrong without any error message.
- Policies are ordinary SQL conditions, so a join that is missing one comparison, or compares a column with itself, still runs. It just lets too many rows through.
- Permissive policies add up. Postgres combines them with OR (PostgreSQL: CREATE POLICY), so one broad policy added to fix a broken screen overrides the correct membership policy next to it.
- Policy expressions run with the privileges of the user running the query (PostgreSQL: Row Security Policies). A policy on
projectsthat readsorganisation_memberstherefore depends on the policy onorganisation_members. When that policy in turn reads itself or another table that reads it back, Postgres stops with42P17(Avoid recursive policies) - and loosening the membership policy to get rid of the error opens every company's data at once. - The membership table is the key to every company, and joining is a write. If the app adds members by inserting rows from the browser, the database has no way to tell an accepted invitation from someone typing another organisation's id.
- Organisation ids travel in URLs and invite links. They are identifiers, not secrets, so a check that "the user knows the organisation id" is no check.
- Data reaches users around policies: views bypass RLS unless created with
security_invoker(Postgres 15 and later), and server code using a secret key bypasses RLS entirely (Row Level Security).
How to fix it
organisations, organisation_members and projects to the names your app uses.1. One membership table, one organisation column on every company table
Skip the create table statements for tables you already have, and check that yours have the same columns and keys. Every table with company data needs a not null organisation column: a row without one belongs to nobody, and a policy has nothing to compare.
create table public.organisations (
id uuid primary key default gen_random_uuid(),
name text not null,
created_at timestamptz not null default now()
);
create table public.organisation_members (
organisation_id uuid not null references public.organisations (id) on delete cascade,
user_id uuid not null references auth.users (id) on delete cascade,
role text not null default 'member' check (role in ('owner', 'admin', 'member')),
created_at timestamptz not null default now(),
primary key (organisation_id, user_id)
);
create index if not exists organisation_members_user_id_idx
on public.organisation_members using btree (user_id);
alter table public.organisations enable row level security;
alter table public.organisation_members enable row level security;user_id serves the lookup every policy makes: Supabase recommends indexing the columns your policies filter on, and a column only counts when it comes first in a btree index (Add indexes).For an existing company table without the column, add it, fill it, then make it required (here projects, filled from the owner's only organisation as an example - work out the right mapping for your data):
alter table public.projects
add column organisation_id uuid references public.organisations (id) on delete cascade;
-- Fill organisation_id for every existing row before the next statement.
alter table public.projects alter column organisation_id set not null;
create index if not exists projects_organisation_id_idx
on public.projects using btree (organisation_id);2. Two helper functions: "my organisations" and "organisations I administer"
This follows the fix in Supabase's docs for recursive policies: a security definer function in a private schema that reads the membership table with its owner's rights, so the policies never call each other (Avoid recursive policies). Supabase notes this works because the function's owner on Supabase is postgres, which bypasses RLS; a function owned by a role without that right would hit the recursion again. search_path is empty, so every name inside must include its schema (Use security definer functions).
create schema if not exists private;
create or replace function private.user_organisation_ids()
returns setof uuid
language sql
stable
security definer
set search_path = ''
as $$
select organisation_id
from public.organisation_members
where user_id = (select auth.uid());
$$;
create or replace function private.user_admin_organisation_ids()
returns setof uuid
language sql
stable
security definer
set search_path = ''
as $$
select organisation_id
from public.organisation_members
where user_id = (select auth.uid())
and role in ('owner', 'admin');
$$;
revoke execute on function private.user_organisation_ids() from public, anon;
revoke execute on function private.user_admin_organisation_ids() from public, anon;
grant usage on schema private to authenticated;
grant execute on function private.user_organisation_ids() to authenticated;
grant execute on function private.user_admin_organisation_ids() to authenticated;private to Exposed schemas in the API settings. The functions only ever return the caller's own organisations, because they filter by auth.uid() themselves.3. Policies on the organisation and membership tables
Members can see their organisations and who else is in them. Admins can rename the organisation and remove members other than the owner; anyone can leave. There is deliberately no insert or update policy on organisation_members: users never write membership rows directly. Joining happens only through the functions in step 6.
create policy "Members can read their organisations"
on public.organisations for select
to authenticated
using ( id in (select private.user_organisation_ids()) );
create policy "Admins can rename their organisations"
on public.organisations for update
to authenticated
using ( id in (select private.user_admin_organisation_ids()) )
with check ( id in (select private.user_admin_organisation_ids()) );
create policy "Members can see who else is in their organisations"
on public.organisation_members for select
to authenticated
using ( organisation_id in (select private.user_organisation_ids()) );
create policy "Admins can remove members, members can leave"
on public.organisation_members for delete
to authenticated
using (
(organisation_id in (select private.user_admin_organisation_ids()) and role <> 'owner')
or user_id = (select auth.uid())
);drop policy "exact name" on public.organisation_members; for each one check 2 listed). Postgres combines permissive policies with OR, so an old broad policy left in place overrides these (PostgreSQL: CREATE POLICY).4. The same four policies on every company table
Each one compares the row's organisation with the caller's memberships. The in (select private...) form is the one Supabase's docs use; wrapping a function in select lets Postgres run it once per statement instead of once per row (Row Level Security).
alter table public.projects enable row level security;
create policy "Members can read their organisation's projects"
on public.projects for select
to authenticated
using ( organisation_id in (select private.user_organisation_ids()) );
create policy "Members can add projects to their organisations"
on public.projects for insert
to authenticated
with check ( organisation_id in (select private.user_organisation_ids()) );
create policy "Members can edit their organisation's projects"
on public.projects for update
to authenticated
using ( organisation_id in (select private.user_organisation_ids()) )
with check ( organisation_id in (select private.user_organisation_ids()) );
create policy "Admins can delete their organisation's projects"
on public.projects for delete
to authenticated
using ( organisation_id in (select private.user_admin_organisation_ids()) );with check on update stops a member from moving a row into an organisation they do not belong to: for an update, using decides which rows the command may touch and with check decides which new versions may be stored (PostgreSQL: CREATE POLICY).Optional safety net for company tables: a restrictive policy. Postgres combines restrictive policies with AND, so it must pass on top of any permissive policy (PostgreSQL: CREATE POLICY). If an AI tool later adds using (true) to fix a screen, rows from other organisations still stay hidden:
create policy "Never outside the caller's organisations"
on public.projects
as restrictive
for all
to authenticated
using ( organisation_id in (select private.user_organisation_ids()) )
with check ( organisation_id in (select private.user_organisation_ids()) );5. Invitations: a table only admins can write
An invitation names the organisation, the email address it was sent to, the role, and a random token for the link. gen_random_uuid() produces a version 4 (random) UUID (PostgreSQL: UUID functions). Only admins of the organisation can create, see or cancel its invitations; the person invited never reads this table.
create table public.invitations (
id uuid primary key default gen_random_uuid(),
organisation_id uuid not null references public.organisations (id) on delete cascade,
email text not null,
role text not null default 'member' check (role in ('admin', 'member')),
token uuid not null unique default gen_random_uuid(),
invited_by uuid not null default auth.uid() references auth.users (id) on delete cascade,
expires_at timestamptz not null default now() + interval '7 days',
accepted_at timestamptz,
created_at timestamptz not null default now()
);
alter table public.invitations enable row level security;
create index if not exists invitations_organisation_id_idx
on public.invitations using btree (organisation_id);
create policy "Admins can see their organisation's invitations"
on public.invitations for select
to authenticated
using ( organisation_id in (select private.user_admin_organisation_ids()) );
create policy "Admins can invite to their organisations"
on public.invitations for insert
to authenticated
with check (
organisation_id in (select private.user_admin_organisation_ids())
and invited_by = (select auth.uid())
);
create policy "Admins can cancel invitations"
on public.invitations for delete
to authenticated
using ( organisation_id in (select private.user_admin_organisation_ids()) );check on role means nobody can be invited as owner. Send the invite link (for example https://yourapp.com/invite?token=...) from your own server code or an Edge Function. If you use Supabase's auth.admin methods to send invitation emails, those require a secret key, which must never be in the browser (Admin API).6. Joining only through two checked functions
Creating an organisation makes the creator its owner, and accepting an invitation adds the caller with the invited role. Both write to organisation_members, which users cannot write themselves, so they are security definer functions in private. Accepting checks that the token exists, is unused and unexpired, and that the caller's confirmed email matches the invited address - a forwarded link alone is not enough. Supabase stores the confirmation time in email_confirmed_at, which is null for unconfirmed addresses (Users). This only proves the caller owns the address if Confirm email is on (Authentication > Sign In / Providers > Email). With it off, Supabase does not verify the address at sign-up and marks it as confirmed, so someone holding a forwarded link could register the invited address, if it has no account yet, and accept the invitation.
create or replace function private.create_organisation(organisation_name text)
returns uuid
language plpgsql
security definer
set search_path = ''
as $$
declare
new_organisation_id uuid;
begin
if (select auth.uid()) is null then
raise exception 'Not signed in';
end if;
insert into public.organisations (name)
values (organisation_name)
returning id into new_organisation_id;
insert into public.organisation_members (organisation_id, user_id, role)
values (new_organisation_id, (select auth.uid()), 'owner');
return new_organisation_id;
end;
$$;
create or replace function private.accept_invitation(invite_token uuid)
returns uuid
language plpgsql
security definer
set search_path = ''
as $$
declare
invite public.invitations%rowtype;
caller_email text;
begin
select email into caller_email
from auth.users
where id = (select auth.uid())
and email_confirmed_at is not null;
if caller_email is null then
raise exception 'Sign in with a confirmed email address first';
end if;
select * into invite
from public.invitations
where token = invite_token
and accepted_at is null
and expires_at > now()
for update;
if not found then
raise exception 'Invitation not found, already used or expired';
end if;
if lower(invite.email) <> lower(caller_email) then
raise exception 'This invitation was sent to a different email address';
end if;
insert into public.organisation_members (organisation_id, user_id, role)
values (invite.organisation_id, (select auth.uid()), invite.role)
on conflict (organisation_id, user_id) do nothing;
update public.invitations
set accepted_at = now()
where id = invite.id;
return invite.organisation_id;
end;
$$;
revoke execute on function private.create_organisation(text) from public, anon;
revoke execute on function private.accept_invitation(uuid) from public, anon;
grant execute on function private.create_organisation(text) to authenticated;
grant execute on function private.accept_invitation(uuid) to authenticated;for update locks the invitation row, so two simultaneous clicks on the same link cannot both use it.Supabase's docs say never to create a security definer function in an exposed schema (Use security definer functions). The app still needs something it can call, so add two thin wrappers in public that run with the caller's own rights (security invoker) and only pass the call on:
create or replace function public.create_organisation(organisation_name text)
returns uuid
language sql
security invoker
set search_path = ''
as $$
select private.create_organisation(organisation_name);
$$;
create or replace function public.accept_invitation(invite_token uuid)
returns uuid
language sql
security invoker
set search_path = ''
as $$
select private.accept_invitation(invite_token);
$$;
revoke execute on function public.create_organisation(text) from public, anon;
revoke execute on function public.accept_invitation(uuid) from public, anon;
grant execute on function public.create_organisation(text) to authenticated;
grant execute on function public.accept_invitation(uuid) to authenticated;In the app, the invite page reads the token from the link and calls the function once the user is signed in with the invited address (supabase-js: rpc):
const token = new URLSearchParams(window.location.search).get("token");
const { data: organisationId, error } = await supabase.rpc("accept_invitation", {
invite_token: token,
});
if (error) {
// Show error.message: expired, already used, or sent to another address.
}7. Grants: exactly what the policies allow
Grants decide which operations a role may run at all; policies decide on which rows. Depending on when your project was created and its Data API settings, new tables may start with no access for the app's roles (see our guide to error 42501) or with full access for anon and authenticated; tables that already existed keep the grants they had. Either way, set the grants explicitly:
revoke all on table public.organisations, public.organisation_members, public.invitations, public.projects
from anon, authenticated;
grant select, update (name) on table public.organisations to authenticated;
grant select, delete on table public.organisation_members to authenticated;
grant select, insert, delete on table public.invitations to authenticated;
grant select, insert, update, delete on table public.projects to authenticated;organisation_members and no insert on organisations for users: those happen only inside the functions from step 6. If server code with a secret key needs these tables, grant service_role what it needs too.8. Check what else reaches company data
- Views over company tables: recreate them
with (security_invoker = true)so they apply the caller's policies, or keep them out of the exposed schema (Expose a view safely). - Edge Functions or API routes that use a secret key: RLS does not apply to them (Row Level Security), so they must look up the caller's membership themselves. Take the user from their access token, never an organisation id or role from the request body alone.
- The front end: keep filtering queries by the current organisation (
.eq("organisation_id", currentOrganisationId)) so a user in two organisations sees the right one. That is for the screen, not for security - the policies do the separating.
Verify
Run check 5 again with two test users in two different organisations: both read tests must show rows_of_other_organisations = 0, and both join tests must fail. Then try the real flow in the app: create an organisation, invite a second test account, accept the link signed in as that account, and accept it again - the second attempt must fail. Finally, try the same link from a third account with a different email address. It must be refused.
When to get help
The setup above covers one level: organisations with owners, admins and members. Get a second pair of eyes when:
- Your data has more levels - organisations with projects, projects with their own member lists, guests who see one project only. Every extra level is another join in every policy.
- Existing rows have no organisation, or users already belong to several, and you have to decide who owns what before switching the policies on.
- Files in Supabase Storage belong to organisations too. Storage has its own policies, and they need the same membership check.
- Billing, seats or plan limits depend on membership, so a wrong role or an extra member costs money.
- Check 5 already showed rows from another organisation on a live app, and you need to know whether anyone saw them.
- You need rules this page does not cover, such as transferring ownership, stopping the last owner from leaving, or audit logs of who joined when.
Sources
Primary documentation this guide was checked against. Platforms change - if something here no longer matches, the source wins.
Frequently asked questions
- Is filtering by organisation_id in my front-end queries enough?
- No. Your Supabase URL and public key are in the browser, so any signed-in user can send their own query without your filter. The filter decides what the screen shows; the RLS policies must decide what the database returns.
- Why do I get "infinite recursion detected in policy" on my members table?
- A policy on the members table reads the members table (directly or through another table whose policy reads it back). Supabase's fix is a
security definerfunction in a non-exposedprivateschema that looks up the user's memberships, used in the policies asorganisation_id in (select private.user_organisation_ids()). Do not fix it withusing (true): that shows every company's members to everyone. - Can I store the user's organisation in the JWT instead?
- Not in
user_metadata, which users can change.app_metadatais safe from users, but the token is only updated when it is refreshed, so a removed member keeps access until then. A membership table read by a helper function takes effect on the next request. - Is the invite link itself a security risk?
- Only if the link alone is enough to join. In the setup above the token is random, works once, expires after seven days, and only works for a signed-in user whose confirmed email matches the invitation, provided Confirm email is enabled in your Auth settings. Never build invite links from the organisation id alone.
- Do I need a separate database or schema per customer?
- Not for this. One set of tables with an organisation column on every row and membership-based policies separates companies inside one Supabase project. Separate projects per customer are a different trade-off that this page does not cover.
- Will these policies slow down the app?
- Follow Supabase's recommendations and they should not: call helper functions inside
selectso they run once per statement, and indexorganisation_idon every company table anduser_idon the membership table.
Related guides
Fixed-price audit
Want someone to check the whole app, not just this?
A review of data security, production readiness and App Store readiness for apps built with AI tools. 900 USD, a ranked list of fixes within 5 business days.