Per-row sharing in RLS without making it transitive

Sooner or later one specific row needs to reach one specific person who isn't in the tenant — a document shared with a client, a report sent to a contractor. Three ways that goes wrong.

1. Sharing becomes transitive

-- "you may share what you can see"
create policy "share a document"
on document_shares for insert
with check (can_access_document(document_id, 'view'));

Read it back: anyone who can see the row may grant it to anyone else. Every recipient becomes a distributor, the grant graph escapes the tenant, and revoking the original share does nothing about the copies made downstream.

Granting is a tenant right, not something a grant confers.

The check should ask whether you are a member of the document's organization, deliberately not whether you can see the document:

create policy "only members grant access"
on document_shares for insert
with check (
  granted_by = auth.uid()
  and user_id <> auth.uid()
  and exists (
    select 1 from documents d
    where d.id = document_shares.document_id
      and has_org_role(d.organization_id, 'member')
  )
);

2. One expression for every verb

The tempting simplification is to write the access check once and reuse it in all four policies. Do that and a share meant for reading silently permits writing, because SELECT and UPDATE were asked the same question.

The levels have to differ per verb:

create type share_level as enum ('view', 'edit');   -- ordered

create policy "read if a member or shared with"
on documents for select
using (can_access_document(id, 'view'));

create policy "write if a member or shared to edit"
on documents for update
using      (can_access_document(id, 'edit'))
with check (can_access_document(id, 'edit'));

Deletion isn't on that list at all. A share grants access to a row; it is never a licence to destroy it. Creation and deletion stay with the tenant.

3. The share row is itself data

document_shares needs its own policies, and it's easy to leave it open "because it's just a join table". It records who has access to what — a roster of your customers' collaborators. Recipients may see their own grant, members may see grants on their organization's documents, nobody else sees anything.

Its SELECT policy also has to consult document_shares, which recurses — the same trap as the membership lookup in tenant isolation, defused the same way, with a security definer helper.

The shape that holds

One function answers "how much access does this caller have to this row, by any route", and every policy calls it with the level that verb requires:

create or replace function public.can_access_document(doc_id uuid, minimum share_level)
returns boolean
language sql security definer stable
set search_path = public
as $$
  select
    coalesce((select has_org_role(d.organization_id, 'member')
              from documents d where d.id = doc_id), false)
    or coalesce(public.share_level(doc_id) >= minimum, false)
$$;

Both routes live in one place, so they can't drift apart across four policies — which is what happens when the membership half gets updated and the share half doesn't.

What to test

Then break it on purpose. Change the insert policy to can_access_document(document_id, 'view') and re-run: the re-share assertion fails, and two more fail behind it as the share graph spreads. Three failures from one plausible-looking simplification is the point.

Adapting it

• Sharing by link rather than by user: replace user_id with a token column and treat it exactly like the token in invite acceptance — long, secret, expiring, revocable.
• Sharing to a whole organization: add a nullable shared_with_org_id and extend the helper. Keep it as one more branch in the same function, never as a new expression in each policy.
• Expiring shares: add expires_at and put it in the helper, not the policies — one place to forget instead of four.
• Audit: keep granted_by and created_at. Shares are the first thing customers ask about after an incident.