The role check that makes an admin of one tenant an admin of all

This is the sequel to the insert-policy bug that lets any user join any organization, and it is the same mistake wearing a different hat. That one checked who without checking which tenant. This one checks what rank without checking where it is held.

The failure mode

You want admins to be able to delete projects and ordinary members not to. So:

create policy "admins delete projects"
on projects for delete
using (
  exists (
    select 1 from organization_members
    where user_id = auth.uid() and role = 'admin'
  )
);

There is no correlation anywhere in that policy between the row being deleted and the membership row being consulted. It asks "is this user an admin" and never "an admin of what". Anyone who is an admin somewhere is an admin everywhere.

The same bug by a second route

Putting role on the users table produces the identical outcome, earlier and more permanently. A column there cannot express "admin of Acme, member of Globex" — so the moment a user joins a second tenant the data model is wrong and no policy can rescue it.

Rank belongs on the membership, not on the user.

The fix: make the tenant a required argument

Rather than trusting everyone to remember the correlation, put it in the function signature so the code will not compile without it.

-- declaration order IS the ranking: member < admin < owner
create type org_role as enum ('member', 'admin', 'owner');

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

create or replace function public.has_org_role(org_id uuid, minimum org_role)
returns boolean
language sql security definer stable
set search_path = public
as $$
  select coalesce(public.org_role(org_id) >= minimum, false)
$$;

Every policy then reads the same way, with the row's own tenant supplied:

create policy "members read projects"
on projects for select
using (has_org_role(organization_id, 'member'));

create policy "admins delete projects"
on projects for delete
using (has_org_role(organization_id, 'admin'));

organization_id there is the column of the row being touched, so the question is always "what is this caller's rank in this row's tenant". Isolation and authorisation collapse into one call, and there is no version of the policy that forgets the tenant.

Two details doing quiet work

The enum ranks itself. Postgres orders enum values by declaration order, so member < admin < owner and >= means "at least". No lookup table, no integer constants to keep in sync. The trap: adding a rank later with a bare alter type ... add value appends it above owner. Use before or after.

The coalesce is not decoration. A non-member has no row, so org_role() returns null and null >= 'admin' is null, not false. Null in a policy denies, so the behaviour is right — but the expression is now a three-valued thing that reads as a bug to the next person, and it combines badly with or. Pin it to a boolean.

Escalation is the thing to test

A hierarchy is only real if the rungs can't be climbed. The membership table is where that happens, and three separate conditions have to hold — it is tempting to collapse them:

create policy "admins manage members below owner"
on organization_members for update
using (
  has_org_role(organization_id, 'admin')
  and user_id <> auth.uid()      -- no editing yourself
  and role < 'owner'            -- no touching an owner
)
with check (
  has_org_role(organization_id, 'admin')
  and role < 'owner'            -- no minting an owner
);

user_id <> auth.uid() is what stops self-promotion. role < 'owner' appears twice on purpose: in USING it reads the existing row, so an admin can't demote the owner; in WITH CHECK it reads the row being left behind, so an admin can't promote anyone — including, later, themselves — to owner.

Denials are not uniform, and it matters

Two of those are blocked loudly and the rest silently, which surprises everyone debugging their own policies for the first time:

• USING rejects → silence. The row was never visible, so the statement affects zero rows and returns success. supabase-js gives you no error.
• WITH CHECK rejects → an error: "new row violates row-level security policy".

So "admin promotes a member to owner" raises — it passes USING, since she is a below-owner member of his organization, and dies on the row he tried to leave behind. But "admin promotes himself" is silent, because he never passes USING at all.

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

Write the self-promotion test expecting an exception and it fails against a perfectly correct policy. Write the other one expecting silence and it fails too. Reading the row back afterwards is the only form that works for both.

Adapting it

• More ranks: add them to the enum in the right position and every >= comparison keeps working untouched.
• Per-capability grants (can_invite, can_bill) don't fit an ordered enum. Use a table keyed by (organization_id, user_id, capability) and a has_capability(org_id, cap) helper. The discipline to carry over is the org_id argument, not the enum.
• Ownership transfer is deliberately impossible through these policies — every path to minting an owner is blocked. It belongs in a security definer function that demotes and promotes in one transaction.

Which is a whole subject of its own: what to do when the operation is one RLS structurally cannot express, and what you give up the moment you move it out.