---
title: "A NULL in a PL/pgSQL IF NOT skipped the check on 3683 tasks"
description: "In a SECURITY DEFINER function, false OR NULL is NULL and IF NOT skips the RAISE. Why the same check is safe in RLS, the coalesce fix, and how to sweep for it."
url: "https://madsalt.tech/notes/plpgsql-if-not-null-fails-open/"
author: "Denys Zaichenko"
language: "en"
published: 2026-10-18
---

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

TL;DR

A permission check inside a `SECURITY DEFINER` function read `if not (has_perm or created_by = me or assignee_id = me) then raise`. In June 2026, all 3683 tasks in the app had no creator and no assignee. `false OR NULL OR NULL` is `NULL`, `NOT NULL` is still `NULL`, and `IF` skips its body when the condition is `NULL`. So the error was never raised, and any signed-in user could clone tasks. The fix is `coalesce(..., false)` around the whole condition.

This was in the [audit firm time tracker](https://madsalt.tech/work/audit-firm-time-tracking/), in the function behind "clone task".

## What did the check look like?

```sql
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](https://www.postgresql.org/docs/current/ddl-rowsecurity.html) and for [IF](https://www.postgresql.org/docs/current/plpgsql-control-structures.html); together they turn the same expression into a deny in one place and an allow in the other.

![The same NULL, two opposite answers. has_perm is false, created_by and assignee_id are NULL. In an RLS policy: false OR NULL → NULL → Row refused; In IF NOT (...) THEN RAISE: false OR NULL → NOT NULL is NULL → RAISE skipped → Clone allowed.](https://madsalt.tech/charts/en/plpgsql-if-not-null-fails-open.svg)

*Three-valued logic as the PostgreSQL 18 documentation describes it, sections 5.9 (row security) and 41.6.4 (IF).*

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:

```sql
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](https://madsalt.tech/blog/how-to-test-supabase-rls/) 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](https://madsalt.tech/notes/supabase-rls-user-in-two-organizations/). The app was an audit firm's time tracker; its other silent errors are in [time tracking for audit firms](https://madsalt.tech/blog/time-tracking-for-audit-firms/). The [Supabase security check](https://madsalt.tech/tools/supabase-security-check/) lists SECURITY DEFINER functions among its 46 rules, but it cannot tell whether an IF inside one fails open, so this sweep stays manual.