Unique per tenant in PostgreSQL: soft delete and RLS leaks

“An email address may occur only once” sounds like one line of SQL. In a multi-tenant SaaS it is three decisions: once per tenant or once overall, whether a deleted customer still counts, and whether Anna@Example.nl is the same address as anna@example.nl. Get the first one wrong and a unique constraint tells one tenant something about another tenant’s data — row-level security or not.

We tested each variant in PostgreSQL 17.10, as an application role under row-level security, and wrote down exactly what PostgreSQL answered.

Does a unique constraint leak across tenants under row-level security?

Yes, if it is global. With unique (email) across all tenants, tenant 2 tried to add anna@example.nl, an address that only exists at tenant 1. Tenant 2 cannot see that row. It still got:

ERROR:  duplicate key value violates unique constraint "cust_email_global"

That single line says: this address is a customer somewhere else. With on conflict do nothing there is no error, but the answer is INSERT 0 0 instead of INSERT 0 1 — the same information, more quietly.

PostgreSQL does hold back part of it. As a superuser, to whom row-level security does not apply, the error had a second line, DETAIL: Key (email)=(anna@example.nl) already exists.; as the application role under row-level security that line was missing. But the existence is the leak, not the detail. The documentation is explicit that this is how it works: “Referential integrity checks, such as unique or primary key constraints and foreign key references, always bypass row security”, with a warning about “covert channel” leaks. It is the same mechanism as the foreign key in gap 4 of our RLS article.

The fix is to put the tenant in the constraint: unique (tenant_id, email). Then tenant 2 could add the same address without any conflict, and nothing about tenant 1 is revealed. A constraint that really has to be global — a login address shared across all tenants — belongs in a table that is not tenant data, and then the “address already in use” message is a product decision rather than an accident.

What happens to a unique constraint with soft delete?

It keeps counting the deleted row. With unique (tenant_id, email) and a deleted_at column, a tenant that deleted a customer and then added the same address again got the duplicate-key error. The customer is gone from every screen and still blocks the address.

The standard answer is a partial unique index that only covers live rows, and while you are at it, compare addresses without case:

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

What it did, step by step, as tenant 1:

The restore row is the one to plan for. Undeleting is now a question your product has to answer — merge the two, or refuse — because the database will refuse it for you, with an error your users should not see raw.

Why does ON CONFLICT fail with a partial unique index?

Because the conflict target has to name the index’s condition too. With the partial index above:

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

gave ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification. With the predicate added — on conflict (tenant_id, lower(email)) where deleted_at is null do nothing — it worked: INSERT 0 0 for the existing address, INSERT 0 1 for a new one. An import written as on conflict (tenant_id, email) stops on its first statement the day you replace the old constraint with this partial index.

Are NULLs unique?

Not by default. With unique (tenant_id, external_ref), two customers of the same tenant without an external reference were both accepted — PostgreSQL treats every NULL as different from every other. Since PostgreSQL 15 you can say otherwise: with create unique index … on customers (tenant_id, external_ref) nulls not distinct the second customer without a reference was refused. Which one you want depends on the column; “not linked yet” should usually be allowed many times.

A checklist

1. Every unique constraint on tenant data includes tenant_id (preferably first). The check for this is the same query that finds indexes without tenant_id first, in PostgreSQL indexes for multi-tenant SaaS: a unique (number) in that list is a leak, not a performance issue.
2. With soft delete, make the unique index partial (where deleted_at is null), and decide what restoring a deleted row does when a live duplicate exists.
3. Compare what users type case-insensitively with lower() in the index — and know that lower() is not leakproof, so under RLS a lookup through it cannot use this index; measured in row-level security performance.
4. Name the predicate in ON CONFLICT for a partial index.
5. Decide per nullable column whether NULLS NOT DISTINCT is what you mean.

What we did not measure

Performance and index size, deferrable constraints, exclusion constraints, and MERGE. Every result above is a single, repeatable outcome — accepted or refused, with the exact message — rather than a timing.

Sources: the PostgreSQL 17 documentation on row security policies (referential integrity checks bypass row security) and the PostgreSQL 15 release notes (NULLS NOT DISTINCT), both read on 1 October 2026. All results are our own, on PostgreSQL 17.10, on 1 October 2026.