DEV Community: Ashton Latham

The Supabase RLS mistake that lets any user join any organization

Ashton Latham — Tue, 01 Sep 2026 16:24:38 +0000

It is four lines long, it reads correctly out loud, and it appears in a lot of multi-tenant Supabase schemas. It checks who is writing the row and never checks which tenant the row belongs to.

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, and 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.

Any authenticated user, from the browser, against any organization id they have ever seen:

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 or 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

Which clause rejects you determines what you observe:

Rejected by	What you get
`USING`	Silence. The row was never visible, so the statement affects zero rows and reports success.
`WITH CHECK`	An error: "new row violates row-level security policy".

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. 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.

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.

I write more of these at Scaffold, including guides on role hierarchies, invite acceptance, and security definer boundaries. Disclosure: I also sell a multi-tenant starter kit built on these patterns, but the guides stand alone and there's no signup wall.

I broke my own database with an infinite loop, and here's what I learned about Row Level Security

Ashton Latham — Sun, 30 Aug 2026 14:57:04 +0000

A couple of weeks ago I set out to build something I actually wanted to exist: a starter kit for B2B SaaS apps that handles the boring-but-critical stuff: auth, multi-tenancy, and team invites so I could stop rebuilding the same plumbing every time I had a new idea.

It went fine, right up until Postgres told me I'd created an infinite loop in my own security policy.

The setup

Nothing fancy — Next.js, Supabase, Vercel. The whole point of the kit is that users belong to organisations, not just individual accounts, because that's how basically every real B2B product works (think Slack, Notion, Linear). Multiple people share access to the same workspace, and one org's data should never, ever leak into another org's view.

That last part is where Row Level Security (RLS) comes in. RLS is Postgres's way of enforcing access rules at the database level instead of hoping your application code remembers to filter correctly every single time. You write a policy once, and Postgres enforces it on every query, no exceptions, no "oops I forgot the WHERE clause."

I set up two tables: organisations and organization_members (a join table linking users to orgs). Then I wrote what seemed like a completely reasonable policy — members should only be able to see membership rows for organisations they're already part of:

sql
create policy "Users can view memberships in their orgs"
on organization_members for select
using (
organization_id in (
select organization_id from organization_members
where user_id = auth.uid()
)
);

Read that again slowly. I didn't, and Postgres made me regret it.

infinite recursion detected in policy for relation "organization_members"

The policy for organization_members checks whether a row is visible by... querying organization_members. Which triggers the policy again. Which queries the table again. Forever.

It's such an obvious mistake in hindsight, but it's also an incredibly easy one to make, because the logic feels correct when you're writing it. "Only show rows for orgs the user belongs to" is a completely reasonable rule — I just implemented the check by querying the very table the policy was protecting, which meant every read of the table triggered another read of the table to authorise the first read.

The fix: let a function do the dirty work

The way out is a security definer function — a function that runs with elevated privileges, bypassing RLS internally so it can safely check membership without re-triggering the policy that's asking the question:

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

Then the policy calls the function instead of querying the table directly:

sql
create policy "Users can view memberships in their orgs"
on organization_members for select
using (
organization_id in (select * from get_user_org_ids())
);

The function still ultimately reads from organization_members, but because it runs as security definer, it steps outside the RLS check instead of triggering it again. The recursion stops, and the actual security logic is unchanged — users still only see orgs they belong to.

A second, sneakier version of the same problem

I thought I was done, but a few steps later I hit new row violates row-level security policy for table "organizations" — a different table, and at first this looked like a totally separate bug.

It wasn't. When you do an insert-then-select in one call (which Supabase's client does automatically when you chain .insert().select()), Postgres inserts the row and then tries to hand it back to you by selecting it. My organisation's SELECT policy only allowed a user to see orgs they were already a member of — but at the exact moment of creation, the membership row didn't exist yet. So the insert succeeded, the select-back failed, and Postgres reported the whole thing as an RLS violation on the insert, which sent me looking in completely the wrong place for a while.

The fix was just widening the SELECT policy slightly, so an org's owner can see it immediately, even before their membership row exists:

sql
create policy "Users can view their organisations"
on organisations for select
using (
owner_id = auth.uid()
or id in (select * from get_user_org_ids())
);

What I'd tell past-me

RLS is genuinely one of the best tools for making sure you don't accidentally leak one customer's data to another — enforcing it at the database layer instead of trusting every code path to remember to filter correctly is worth the setup pain. But two things are easy to get wrong, and both bit me on the same afternoon:

Don't write a policy on table X that queries table X to authorise itself. If your policy needs to check the same table it's protecting, wrap that check in a security definer function instead of querying it directly inside the policy.
Remember that insert-then-select is two operations, not one, and your SELECT policy needs to actually allow the person who just inserted a row to read it back — which isn't automatic just because they were the one who created it.

Neither of these is documented anywhere obvious until you hit them, which is exactly why I'm writing this down now, mostly for the next person (possibly future me) who gets the same cryptic recursion error and has no idea why a policy that reads perfectly correct in English is actually an infinite loop in practice.

If you're setting up multi-tenant RLS for the first time, save yourself the afternoon I lost — write your membership-check policies through a security definer helper function from the start, not as an afterthought once Postgres yells at you.

I packaged this whole setup—auth, orgs, RLS, invites—into a starter kit so I (and hopefully others) don't have to solve it from scratch again. Happy to answer questions if you're working through something similar.