A NULL in a PL/pgSQL IF NOT skipped the check on 3683 tasks

This was in the audit firm time tracker, in the function behind “clone task”.

What did the check look like?

if not ( has_perm('tasks', 'create')
         or v_task.created_by = auth.uid()
         or v_task.assignee_id = auth.uid() ) then
  raise exception 'forbidden';
end if;

It reads as “refuse unless you may create tasks, made this one, or own it”. The 3683 tasks had been seeded automatically, so created_by and assignee_id were NULL on every one of them. For a role with every permission switched off, the condition was NOT (false OR NULL OR NULL), which is NULL, and the raise never ran.

Why is the same condition safe in RLS?

Row level security treats anything that is not true as a refusal, so NULL means no. A PL/pgSQL IF treats anything that is not true as “skip the body”, and here the body was the refusal. The PostgreSQL documentation states both halves, for row security and for IF; together they turn the same expression into a deny in one place and an allow in the other.

A SECURITY DEFINER function also runs past RLS, so its own check is the only one that applies. One NULL arm is enough: the other arms can all be false.

What fixed it?

Wrap the whole condition so NULL becomes a refusal, and treat clone as creating a task, not as editing the source:

if not coalesce((select has_perm('tasks', 'create')), false) then
  raise exception 'forbidden' using errcode = '42501';
end if;

Did it happen again?

Yes, about three months later, in three more functions: archiving a task, changing its type and checking whether time may be logged on it. Reading the SQL did not catch them. The role-by-role RLS proof did, because it switches each permission off and checks that the action is refused.

How do you sweep for it?

grep 'if not (' finds the syntax, not the bug. What worked:

1. Dump every function body with pg_get_functiondef in one query.
2. In each if not (...) then and if a <> b then, strip coalesce(...), exists(...) and (select ...). A bare or chain or comparison over a nullable column is a candidate.
3. Also match if not <variable> then and trace where the variable is set. _ok := has_perm(...) or created_by = me; hides the same bug in two lines.
4. Call each helper used as if not helper(...) three ways inside a rolled-back transaction: an unknown id, a NULL argument, and a real user whose expected row is missing. A helper that returns NULL in any of them needs a coalesce at the call site or inside.

In that app the sweep checked 14 helpers, all already safe. The bug was confined to the clone function and the three found later. A related multi-tenant gap: what RLS misses for a user in two orgs. The app was an audit firm’s time tracker; its other silent errors are in time tracking for audit firms. The Supabase security check lists SECURITY DEFINER functions among its 45 rules, but it cannot tell whether an IF inside one fails open, so this sweep stays manual.