---
title: "PL/pgSQL IF NOT ja NULL ohitti tarkistuksen 3683 tehtävällä"
description: "SECURITY DEFINER -funktiossa false OR NULL on NULL ja IF NOT ohittaa RAISEn. Miksi sama tarkistus on turvallinen RLS:ssä, coalesce-korjaus ja läpikäynti."
url: "https://madsalt.tech/fi/notes/plpgsql-if-not-null-fails-open/"
author: "Denys Zaichenko"
language: "fi"
published: 2026-10-18
---

# PL/pgSQL IF NOT ja NULL ohitti tarkistuksen 3683 tehtävällä

TL;DR

`SECURITY DEFINER` -funktion sisällä oleva oikeustarkistus oli muotoa `if not (has_perm or created_by = me or assignee_id = me) then raise`. Kesäkuussa 2026 sovelluksen kaikilta 3683 tehtävältä puuttui sekä tekijä että vastuuhenkilö. `false OR NULL OR NULL` on `NULL`, `NOT NULL` on edelleen `NULL`, ja `IF` ohittaa runkonsa, kun ehto on `NULL`. Virhettä ei siis koskaan nostettu, ja kuka tahansa kirjautunut käyttäjä pystyi kloonaamaan tehtäviä. Korjaus on `coalesce(..., false)` koko ehdon ympärillä.

Tämä tapahtui [tilintarkastusyhtiön ajanseurantasovelluksessa](https://madsalt.tech/fi/work/audit-firm-time-tracking/), "kloonaa tehtävä" -toiminnon takana olevassa funktiossa.

## Miltä tarkistus näytti?

```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;
```

Se luetaan "torju, ellei sinulla ole oikeutta luoda tehtäviä, et ole tehnyt tätä etkä omista sitä". Nuo 3683 tehtävää oli luotu automaattisesti, joten `created_by` ja `assignee_id` olivat `NULL` jokaisella. Roolille, jonka kaikki oikeudet olivat pois päältä, ehdosta tuli `NOT (false OR NULL OR NULL)`, joka on `NULL`, eikä `raise` koskaan suoritettu.

## Miksi sama ehto on turvallinen RLS:ssä?

Row level security käsittelee kaiken, mikä ei ole `true`, torjuntana, joten `NULL` tarkoittaa ei. PL/pgSQL:n `IF` käsittelee kaiken, mikä ei ole `true`, tulkintana "ohita runko", ja tässä runko oli itse torjunta. PostgreSQL-dokumentaatio kertoo molemmat puolet, [row securityn](https://www.postgresql.org/docs/current/ddl-rowsecurity.html) ja [IF-lauseen](https://www.postgresql.org/docs/current/plpgsql-control-structures.html) osalta; yhdessä ne tekevät samasta lausekkeesta torjunnan yhdessä paikassa ja sallimisen toisessa.

![Sama NULL, kaksi vastakkaista vastausta. has_perm on false, created_by ja assignee_id ovat NULL. RLS-politiikassa: false OR NULL → NULL → Rivi torjutaan; IF NOT (...) THEN RAISE -rakenteessa: false OR NULL → NOT NULL on NULL → RAISE ohitetaan → Kloonaus sallittu.](https://madsalt.tech/charts/fi/plpgsql-if-not-null-fails-open.svg)

*Kolmiarvoinen logiikka PostgreSQL 18 -dokumentaation mukaan, luvut 5.9 (row security) ja 41.6.4 (IF).*

`SECURITY DEFINER` -funktio myös ajetaan RLS:n ohi, joten sen oma tarkistus on ainoa, joka pätee. Yksi `NULL`-haara riittää: muut haarat voivat kaikki olla `false`.

## Mikä korjasi sen?

Kääri koko ehto niin, että `NULL` muuttuu torjunnaksi, ja kohtele kloonausta tehtävän luomisena, älä lähteen muokkaamisena:

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

## Tapahtuiko se uudelleen?

Kyllä, noin kolme kuukautta myöhemmin, vielä kolmessa funktiossa: tehtävän arkistoinnissa, sen tyypin vaihtamisessa ja sen tarkistamisessa, saako siihen kirjata aikaa. SQL:n lukeminen ei löytänyt niitä. [Roolikohtainen RLS-todistus](https://madsalt.tech/fi/blog/how-to-test-supabase-rls/) löysi, koska se kytkee jokaisen oikeuden pois päältä ja tarkistaa, että toiminto torjutaan.

## Miten koodi käydään läpi?

`grep 'if not ('` löytää syntaksin, ei bugia. Tämä toimi:

1. Vedä jokaisen funktion runko yhdellä kyselyllä käyttäen `pg_get_functiondef`.
2. Poista jokaisesta `if not (...) then` ja `if a <> b then` -kohdasta `coalesce(...)`, `exists(...)` ja `(select ...)`. Paljas `or`-ketju tai vertailu nullable-sarakkeen yli on ehdokas.
3. Etsi myös `if not <variable> then` ja jäljitä, missä muuttuja asetetaan. `_ok := has_perm(...) or created_by = me;` kätkee saman bugin kahdelle riville.
4. Kutsu jokaista apufunktiota, jota käytetään muodossa `if not helper(...)`, kolmella tavalla peruutettavassa transaktiossa: tuntematon id, `NULL`-argumentti ja oikea käyttäjä, jolta odotettu rivi puuttuu. Apufunktio, joka palauttaa `NULL` missä tahansa näistä, tarvitsee `coalesce`-kutsun kutsukohdassa tai sisällä.

Siinä sovelluksessa läpikäynti tarkisti 14 apufunktiota, ja kaikki olivat jo turvallisia. Bugi rajoittui kloonausfunktioon ja kolmeen myöhemmin löytyneeseen. Aiheeseen liittyvä monivuokraajaongelma: [mitä RLS ei huomaa kahden organisaation käyttäjän kohdalla](https://madsalt.tech/fi/notes/supabase-rls-user-in-two-organizations/). Sovellus oli tilintarkastusyhteisön tuntiseurantatyökalu; sen muut hiljaiset virheet ovat kirjoituksessa [tuntiseuranta tilintarkastusyhteisölle](https://madsalt.tech/fi/blog/time-tracking-for-audit-firms/). [Supabase tietoturvatarkistus](https://madsalt.tech/fi/tools/supabase-security-check/) listaa SECURITY DEFINER -funktiot 46 sääntönsä joukossa, mutta se ei voi kertoa, avautuuko niiden sisällä oleva IF auki virhetilanteessa, joten tämä läpikäynti pysyy käsityönä.