Why invite acceptance can't be an RLS policy

Row-level security answers one question well: may this caller write this row? Invite acceptance asks something else entirely — is this caller the person a message sent to a third-party mail server was addressed to? Postgres has no way to know that, so any policy you write is approximating it.

The tempting version

create policy "join if you were invited"
on organization_members for insert
with check (
  user_id = auth.uid()
  and exists (
    select 1 from invites
    where organization_id = organization_members.organization_id
      and email = auth.jwt() ->> 'email'
  )
);

It scopes the tenant, so it looks like a fix. Four holes, and they compound:

• The invitee picks their own rank. Nothing constrains role, so someone invited as a member inserts themselves as owner. The tenant-isolation bug, re-entering through the door marked invited.
• The invite is never consumed. A policy authorises a row; it can't mark the invite used in the same breath. One invite becomes unlimited joins, and a revoked-then-resent invite is still live.
• Unverified email is treated as identity. If the provider hasn't confirmed the address, auth.jwt() ->> 'email' is just a string the user typed at signup. Type a colleague's address, inherit their invites.
• expires_at is unenforced unless every policy remembers it, and one of them eventually won't.

The fix: a function is the trust boundary

Acceptance is not a row the client writes. It is a function that runs as its owner, does what the caller may not, and takes responsibility for every check RLS would otherwise have made.

create or replace function public.accept_invite(invite_token text)
returns uuid
language plpgsql security definer
set search_path = public
as $$
declare
  claimed invites;
  caller_email text;
begin
  if auth.uid() is null then
    raise exception 'not authenticated';
  end if;

  -- an unverified address is a claim, not a fact
  if coalesce((auth.jwt() ->> 'email_verified')::boolean, false) is not true then
    raise exception 'email not verified';
  end if;
  caller_email := lower(auth.jwt() ->> 'email');

  -- claim and validate in ONE statement
  update invites
     set accepted_at = now(), accepted_by = auth.uid()
   where token        = invite_token
     and accepted_at  is null
     and expires_at   > now()
     and lower(email) = caller_email
  returning * into claimed;

  if not found then
    raise exception 'invite is invalid, expired, already used, or not for this account';
  end if;

  insert into organization_members (organization_id, user_id, role)
  values (claimed.organization_id, auth.uid(), claimed.role)
  on conflict (organization_id, user_id) do nothing;

  return claimed.organization_id;
end;
$$;

revoke execute on function public.accept_invite(text) from public, anon;
grant  execute on function public.accept_invite(text) to authenticated;

Note what is absent from the schema around it: no SELECT policy letting an invitee read their own invite, and no INSERT policy on organization_members for joining. The invitee holds a token from their email; the table stays shut.

Four things inside doing the real work

Claim and validate in one statement

A select, then checks, then an update is a check-then-act race: two concurrent calls both observe accepted_at is null and both proceed. Folding every condition into the where clause of the write makes the database's row lock the thing enforcing single use.

Validation belongs in the statement that claims, not before it.

The coalesce is the whole guard

A token with no email_verified claim yields null, and if null then does not raise — it falls through, silently granting exactly what the check exists to prevent. This is the line people omit, and its absence is invisible in every happy-path test.

The rank comes from the invite

role is read off the claimed row, never from an argument. Add check (role < 'owner') to the invites table and no invite capable of minting an owner can exist at all — a constraint, not a policy, so it holds even against the service role. Ownership transfer can't be laundered through the invite flow.

One error message for every failure

Distinguishing expired from not for you from no such token turns the function into an oracle: an attacker learns which addresses were invited to which organizations by reading the error text. One message costs nothing and closes it.

The token

Don't use the invite's id. Ids leak into logs, URLs, error messages, and support tickets, and are often enumerable. Generate a separate secret:

token text not null unique default
  replace(gen_random_uuid()::text, '-', '')
  || replace(gen_random_uuid()::text, '-', '')

Two v4 uuids is roughly 244 bits and needs no extension. With pgcrypto enabled, encode(gen_random_bytes(32),'hex') is the more idiomatic equivalent.

The shortcut that only half works

A common alternative is a trigger on auth.users that auto-joins any pending invites matching the new user's email at signup. It is less code and it does work — for people who don't have an account yet.

The function above works for both cases, because it keys off the current session rather than the moment of account creation.

Operational notes

• Resending should mint a new token and expire the old one, not extend the existing row. A token that has sat in an inbox for a month has also sat in a backup, a log, and possibly a support ticket.
• Rate-limit the RPC. Nothing here stops an attacker calling accept_invite in a loop. The tokens are far too large to guess, but attempts should still be capped at the edge.
• Who can read invites: admins of the organization, and nobody else. The table is a roster of who has been asked to join what — treat it as data, not plumbing.