The Supabase RLS mistake that lets any user join any organization

Nearly every B2B app converges on the same three tables: organizations, a membership join table, and whatever the customer actually came for. Access is decided by membership, row-level security enforces it in Postgres, and the whole thing is genuinely more robust than checking permissions in application code — a missed where clause in a route handler can't leak data past a policy.

That is exactly why the bug below is worth knowing about. It doesn't live in the policies people scrutinise. It lives in the one nobody reads twice.

The schema

Standard shape. Organizations are the tenant boundary, membership decides who sees what, and projects stands in for whatever your product's real resource is.

create table organizations (
  id        uuid primary key default gen_random_uuid(),
  name      text not null,
  owner_id  uuid not null
);

create table organization_members (
  organization_id uuid not null references organizations(id) on delete cascade,
  user_id         uuid not null,
  role            text not null default 'member',
  primary key (organization_id, user_id)
);

create table projects (
  id              uuid primary key default gen_random_uuid(),
  organization_id uuid not null references organizations(id) on delete cascade,
  name            text not null
);

Reads are scoped through a helper. It needs security definer because a policy on organization_members that queries organization_members re-enters itself — Postgres raises "infinite recursion detected in policy for relation". Running the lookup as the function's owner takes it out of the policy's reach and breaks the loop.

create or replace function public.user_org_ids()
returns setof uuid
language sql security definer stable
set search_path = public
as $$
  select organization_id from organization_members where user_id = auth.uid()
$$;

create policy "read projects in your orgs"
on projects for select
using (organization_id in (select public.user_org_ids()));

So far this is fine. Every read of projects is filtered to organizations the caller belongs to. Test it with two accounts and it behaves.

The policy that looks right

Somewhere you have to let a user become a member — at minimum, the person who just created an organization needs a membership row in it. So you write the obvious policy:

-- checks who, never which org
create policy "users can insert their own membership"
on organization_members for insert
with check (user_id = auth.uid());

Read it as a sentence: "you may add a membership row as long as the row is about you." That sounds like a security check. It constrains who is being added, and says nothing at all about which organization they are being added to.

Organization ids are not secret. They travel in URLs, in API responses, in invite links, in error messages, in support tickets. Assume any user has seen at least one that isn't theirs.

await supabase.from('organization_members').insert({
  organization_id: someOtherOrgId,
  user_id: myUserId,
  role: 'owner',
})

The policy passes: user_id really is auth.uid(). They are now an owner of a tenant they have no relationship to.

Why the read policies don't save you

This is the part that makes it dangerous rather than merely wrong. Every SELECT policy in the schema keys off membership — that was the whole design. So the moment the attacker owns a membership row, those policies start working for them:

The read policies were never broken. They were asked the wrong question, and they answered it correctly.

Projects, documents, members, billing records — anything scoped by user_org_ids() is now in scope, because the attacker genuinely is a member as far as Postgres is concerned. One INSERT converts into read access across the entire tenant, and often write access too. Because role was unconstrained, they took 'owner' on the way in.

Nothing in your logs looks unusual. There is no failed request, no permission error, no anomalous query — just a membership row that shouldn't exist, and a user quietly reading someone else's data through policies working exactly as written.

The fix

Constrain the tenant, not just the actor. The only legitimate reason a client inserts a membership row is an owner bootstrapping the organization they just created. Everything else — invite acceptance, an admin adding a colleague — belongs server-side, in a security definer function or behind the service role, where RLS is not the thing doing the checking.

create policy "owner bootstraps their own membership"
on organization_members for insert
with check (
  user_id = auth.uid()
  and role = 'owner'
  and organization_id in (
    select id from organizations where owner_id = auth.uid()
  )
);

Three clauses, three distinct jobs, and dropping any one reopens a hole:

• user_id = auth.uid() — the row is about you, so you can't add anyone else.
• role = 'owner' — you aren't inventing a privilege level for yourself on the way in.
• organization_id in (...) — the organization is one you actually own. This is the clause that was missing.

How do you know it's fixed?

This is the part that usually gets skipped, and it matters more than the policy, because a policy you haven't tested is a guess. There are two traps that make RLS tests pass while the policy leaks.

Superusers bypass RLS unconditionally

If you connect with a psql connection string, or as the table owner, or as postgres, every policy passes no matter how broken it is. Row-level security simply does not apply to superusers, and alter table ... enable row level security doesn't change that.

Tests have to run as a non-superuser, through the same auth.uid() path your application uses. If your RLS test suite has never been run as authenticated, it is not testing anything at all.

Denials are usually silent

This one bites people writing their first negative test. Which clause rejects you determines what you observe:

A blocked SELECT returns an empty result, not an exception. A blocked DELETE removes nothing and tells you it succeeded. So:

Never assert that something was blocked by checking a call didn't throw. Read the data back and assert on what is actually there.

Then break it on purpose

Put the vulnerable policy back and run your tests again. If nothing goes red, your suite would not have caught the real thing either. On the schema above, reintroducing the broken insert policy should fail the "cannot join an org you don't own" assertion and a second assertion, because the attacker can now see a third tenant's projects. That cascade is the blast radius, reproduced in a few seconds.

A test suite you have never watched fail is a decoration.

Two traps in the same neighbourhood

INSERT ... RETURNING is also a read

supabase-js compiles .insert().select() into INSERT ... RETURNING, and Postgres applies the SELECT policy to the returned row. A user creating an organization is not yet a member of it, so a membership-only read policy rejects the read-back — and the error says "new row violates row-level security policy", which reads like the insert was refused rather than the row being returned. Give the organizations read policy an owner_id = auth.uid() branch and it resolves.

UPDATE needs both clauses spelled out

USING chooses which rows you may touch; WITH CHECK validates the row you leave behind. Postgres reuses USING as the check when you omit it, so the default is safe — but write both anyway. The day someone widens USING, having the second clause in front of them is what prompts the question of what a row is now allowed to become. Without it, an update can move a row out of your tenant and into somebody else's.

The pattern behind the bug

It is worth naming, because once you can hear it you will catch it in policies nobody has written yet:

A check that names the actor but not the boundary.

The insert policy above checks who but not which tenant. A role check written as exists (select 1 from organization_members where user_id = auth.uid() and role = 'admin') checks what rank but not where it is held — making anyone who is an admin somewhere an admin everywhere. A sharing policy phrased as "you may share what you can see" checks visibility but not authority, so every recipient becomes a distributor.

Same shape every time. Read each policy aloud as a sentence and listen for the missing half.