NULL im PL/pgSQL-IF NOT: Prüfung bei 3683 Tasks übersprungen

Das war im Zeiterfassungs-Tool für eine Wirtschaftsprüfungsgesellschaft, in der Funktion hinter “Aufgabe klonen”.

Wie sah die Prüfung aus?

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;

Sie liest sich als “ablehnen, außer man darf Aufgaben anlegen, hat diese erstellt oder ist dafür zuständig”. Die 3683 Aufgaben waren automatisch angelegt worden, daher waren created_by und assignee_id bei jeder einzelnen NULL. Für eine Rolle mit allen ausgeschalteten Rechten lautete die Bedingung NOT (false OR NULL OR NULL), also NULL, und das raise lief nie.

Warum ist dieselbe Bedingung in RLS sicher?

Row Level Security behandelt alles, was nicht true ist, als Ablehnung, NULL heißt also nein. Ein PL/pgSQL-IF behandelt alles, was nicht true ist, als “Rumpf überspringen”, und hier war der Rumpf die Ablehnung. Die PostgreSQL-Dokumentation nennt beide Hälften, für Row Security und für IF; zusammen machen sie aus demselben Ausdruck an einer Stelle ein Verbot und an der anderen eine Erlaubnis.

Eine SECURITY DEFINER-Funktion läuft außerdem an RLS vorbei, ihre eigene Prüfung ist also die einzige, die gilt. Ein einziger NULL-Zweig genügt: Die anderen Zweige können alle false sein.

Was hat es behoben?

Schließen Sie die ganze Bedingung ein, sodass NULL zur Ablehnung wird, und behandeln Sie das Klonen als Anlegen einer Aufgabe, nicht als Bearbeiten der Quelle:

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

Ist es wieder passiert?

Ja, etwa drei Monate später, in drei weiteren Funktionen: beim Archivieren einer Aufgabe, beim Ändern ihres Typs und bei der Prüfung, ob darauf Zeit gebucht werden darf. Das Lesen des SQL fing sie nicht ab. Der Nachweis der RLS Rolle für Rolle tat es, denn er schaltet jedes Recht aus und prüft, dass die Aktion abgelehnt wird.

Wie suchen Sie danach?

grep 'if not (' findet die Syntax, nicht den Fehler. Was funktioniert hat:

1. Alle Funktionsrümpfe mit pg_get_functiondef in einer Abfrage ausgeben.
2. In jedem if not (...) then und if a <> b then coalesce(...), exists(...) und (select ...) entfernen. Eine nackte or-Kette oder ein Vergleich über eine Spalte, die NULL sein kann, ist ein Kandidat.
3. Auch if not <variable> then suchen und nachverfolgen, wo die Variable gesetzt wird. _ok := has_perm(...) or created_by = me; versteckt denselben Fehler in zwei Zeilen.
4. Jeden Helfer, der als if not helper(...) verwendet wird, in einer zurückgerollten Transaktion auf drei Arten aufrufen: mit einer unbekannten ID, mit einem NULL-Argument und mit einem echten Nutzer, dessen erwartete Zeile fehlt. Ein Helfer, der dabei irgendwann NULL liefert, braucht ein coalesce an der Aufrufstelle oder in sich.

In dieser App prüfte die Suche 14 Helfer, alle bereits sicher. Der Fehler beschränkte sich auf die Klon-Funktion und die drei später gefundenen. Eine verwandte Lücke bei mandantenfähigen Apps: was RLS bei einem Nutzer in zwei Organisationen übersieht. Die App war der Zeiterfassungs-Tracker einer Wirtschaftsprüfungskanzlei; ihre übrigen stillen Fehler stehen in Zeiterfassung für Wirtschaftsprüfer. Der Supabase Sicherheitscheck listet SECURITY DEFINER-Funktionen unter seinen 46 Regeln, kann aber nicht erkennen, ob ein IF darin offen fehlschlägt, daher bleibt dieser Durchgang manuell.