Uniek per klant in PostgreSQL: soft delete en RLS-lekken

“Een e-mailadres mag maar één keer voorkomen” klinkt als één regel SQL. In een multi-tenant SaaS zijn het drie beslissingen: één keer per klant of één keer overal, of een verwijderd klantrecord nog meetelt, en of Anna@Example.nl hetzelfde adres is als anna@example.nl. Zit je bij de eerste fout, dan vertelt een unieke constraint de ene klant iets over de gegevens van de andere — met of zonder row-level security.

We testten elke variant in PostgreSQL 17.10, als applicatierol onder row-level security, en schreven precies op wat PostgreSQL antwoordde.

Lekt een unieke constraint tussen klanten onder row-level security?

Ja, als hij globaal is. Met unique (email) over alle klanten probeerde klant 2 anna@example.nl toe te voegen, een adres dat alleen bij klant 1 bestaat. Klant 2 kan die rij niet zien. Toch kreeg hij:

ERROR:  duplicate key value violates unique constraint "cust_email_global"

Die ene regel zegt: dit adres staat bij een andere klant. Met on conflict do nothing komt er geen fout, maar is het antwoord INSERT 0 0 in plaats van INSERT 0 1 — dezelfde informatie, stiller.

PostgreSQL houdt wel een deel achter. Als superuser, voor wie row-level security niet geldt, had de fout een tweede regel, DETAIL: Key (email)=(anna@example.nl) already exists.; als applicatierol onder row-level security ontbrak die regel. Maar het bestaan is het lek, niet het detail. De documentatie zegt letterlijk dat het zo werkt: “Referential integrity checks, such as unique or primary key constraints and foreign key references, always bypass row security”, met een waarschuwing voor “covert channel”-lekken. Het is hetzelfde mechanisme als de vreemde sleutel in gat 4 van ons RLS-artikel.

De oplossing is de klant in de constraint zetten: unique (tenant_id, email). Dan kon klant 2 hetzelfde adres toevoegen zonder conflict, en verraadt de database niets over klant 1. Een constraint die echt globaal moet zijn — een inlogadres voor alle klanten samen — hoort in een tabel buiten de gegevens van één klant, en dan is de melding “dit adres is al in gebruik” een productbeslissing in plaats van een ongeluk.

Wat gebeurt er met een unieke constraint bij soft delete?

Hij blijft de verwijderde rij meetellen. Met unique (tenant_id, email) en een kolom deleted_at kreeg een klant die een klantrecord had verwijderd en daarna hetzelfde adres opnieuw toevoegde de dubbele-sleutelfout. Het record is van elk scherm verdwenen en houdt het adres toch bezet.

Het gangbare antwoord is een partiële unieke index die alleen levende rijen dekt, en als je toch bezig bent: vergelijk adressen zonder hoofdlettergevoeligheid.

create unique index customers_email_live
  on customers (tenant_id, lower(email))
  where deleted_at is null;

Wat hij deed, stap voor stap, als klant 1:

Op de terugzet-regel moet je voorbereid zijn. Terugzetten wordt nu een vraag die je product moet beantwoorden — samenvoegen of weigeren — want de database weigert het anders voor je, met een foutmelding die je gebruikers niet rauw moeten zien.

Waarom faalt ON CONFLICT bij een partiële unieke index?

Omdat het conflictdoel ook de voorwaarde van de index moet noemen. Met de partiële index hierboven gaf

insert into customers (tenant_id, email) values (1, 'anna@example.nl')
on conflict (tenant_id, lower(email)) do nothing;

ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification. Met de voorwaarde erbij — on conflict (tenant_id, lower(email)) where deleted_at is null do nothing — werkte het: INSERT 0 0 voor het bestaande adres, INSERT 0 1 voor een nieuw. Een import geschreven als on conflict (tenant_id, email) stopt bij zijn eerste opdracht op de dag dat je de oude constraint vervangt door deze partiële index.

Zijn NULLs uniek?

Standaard niet. Met unique (tenant_id, external_ref) werden twee klantrecords van dezelfde klant zonder externe referentie allebei geaccepteerd — PostgreSQL behandelt elke NULL als verschillend van elke andere. Sinds PostgreSQL 15 kun je dat anders zeggen: met create unique index … on customers (tenant_id, external_ref) nulls not distinct werd het tweede record zonder referentie geweigerd. Welke je wilt, hangt af van de kolom; “nog niet gekoppeld” moet meestal vaak mogen.

Een controlelijst

1. Elke unieke constraint op klantdata bevat tenant_id (bij voorkeur vooraan). De controle daarvoor is dezelfde query die indexen zonder tenant_id vooraan vindt, in PostgreSQL-indexen voor multi-tenant SaaS: een unique (number) in die lijst is een lek, geen prestatieprobleem.
2. Maak bij soft delete de unieke index partieel (where deleted_at is null), en beslis wat terugzetten doet als er al een levende dubbele bestaat.
3. Vergelijk wat gebruikers typen zonder hoofdlettergevoeligheid met lower() in de index — en weet dat lower() niet leakproof is, zodat een opzoeking daarlangs onder RLS deze index niet kan gebruiken; nagemeten in row-level security in PostgreSQL: wat kost het.
4. Noem de voorwaarde in ON CONFLICT bij een partiële index.
5. Beslis per kolom die leeg mag zijn of NULLS NOT DISTINCT is wat je bedoelt.

Wat we niet hebben gemeten

Prestaties en indexgrootte, uitstelbare constraints, exclusion constraints en MERGE. Elke uitkomst hierboven is een enkele, herhaalbare uitkomst — geaccepteerd of geweigerd, met de precieze melding — en geen tijdmeting.

Bronnen: de PostgreSQL 17-documentatie over row security policies (referentiële-integriteitscontroles omzeilen row security) en de release notes van PostgreSQL 15 (NULLS NOT DISTINCT), beide gelezen op 1 oktober 2026. Alle uitkomsten zijn van onszelf, in PostgreSQL 17.10, op 1 oktober 2026.