# Postgres RLS Unique Constraint Leak: Hidden Rows Collide

- Author: Abdullah Chaudary
- Category: Full-Stack
- Published: 2026-11-11T17:37:01.541Z
- Updated: 2026-11-11T17:37:01.566Z
- Reading time: 7 min
- Tags: Postgres, Row-Level Security, Multi-Tenancy, Database Design, Security

> A Postgres unique constraint ignores row-level security, so a tenant that cannot SELECT a row can still find it by INSERT. Scoping the key to UNIQUE (tenant_id, email) closes it.

On 11 November 2026, in a sandbox, tenant 1 ran `SELECT` for `bob@tenant-two.test` and got zero rows. Then it ran an `INSERT` for the same address and got `ERROR: 23505: duplicate key value violates unique constraint "users_email_key"`. Postgres had dropped the `DETAIL` line that would print the key, and the error code still told tenant 1 that Bob's row exists. That is a Postgres RLS unique constraint leak.

A reader of my Postgres schemas post pointed out that unique constraints leak rows a role cannot read. That post already showed the foreign-key version, where [a foreign key check reveals whether an id exists to a role with no access to the table](/blog/postgres-schemas-fastapi-sqlalchemy-async). This post covers the unique-constraint version, which I built and ran on three Postgres versions.

**TL;DR:** A Postgres RLS unique constraint leak exists because unique checks bypass row-level security. A tenant who cannot `SELECT` a row can still learn it exists from the error code, or from a row count with `ON CONFLICT DO NOTHING`. Suppressed `DETAIL` does not close it. Scoping the key to `UNIQUE (tenant_id, email)` does, where per-tenant uniqueness is acceptable.

## Does a Postgres RLS Unique Constraint Leak Hidden Rows?

Yes. In my demo a tenant-scoped role saw one row, yet its `INSERT` of another tenant's email failed with SQLSTATE `23505`, while an unused email succeeded. The PostgreSQL documentation says as much: unique checks "always bypass row security", and the `CREATE POLICY` notes name duplicate inserts as an indirect way to learn a value exists.

The setup is one table with row-level security (RLS) and a global `UNIQUE` on `email`:

```sql
CREATE SCHEMA app;

CREATE TABLE app.users (
  id        serial PRIMARY KEY,
  tenant_id int  NOT NULL,
  email     text NOT NULL,
  name      text NOT NULL,
  CONSTRAINT users_email_key UNIQUE (email)
);
ALTER TABLE app.users ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON app.users
  USING      (tenant_id = current_setting('app.tenant_id')::int)
  WITH CHECK (tenant_id = current_setting('app.tenant_id')::int);

CREATE ROLE tenant_app LOGIN PASSWORD 'demo_ta';
GRANT USAGE ON SCHEMA app TO tenant_app;
GRANT SELECT, INSERT, UPDATE ON app.users TO tenant_app;
GRANT USAGE ON SEQUENCE app.users_id_seq TO tenant_app;

INSERT INTO app.users (tenant_id, email, name) VALUES
  (1, 'alice@tenant-one.test', 'Alice'),
  (2, 'bob@tenant-two.test',   'Bob'),
  (2, 'carol@tenant-two.test', 'Carol');
```

`tenant_app` is not the table owner, so RLS applies to it. I connect as `tenant_app` with `PGOPTIONS='-c app.tenant_id=1'`. The data is invented, all on `.test` domains, and the container ran with `--network none`.

Tenant 1 sees only Alice, and a count for Bob returns 0. The insert then fails:

```sql
INSERT INTO app.users (tenant_id, email, name) VALUES (1, 'bob@tenant-two.test', 'probe');
```

```text
ERROR:  23505: duplicate key value violates unique constraint "users_email_key"
SCHEMA NAME:  app
TABLE NAME:  users
CONSTRAINT NAME:  users_email_key
LOCATION:  _bt_check_unique, nbtinsert.c:666
```

The same insert with `nobody@tenant-two.test` succeeded with `INSERT 0 1`, so the two outcomes differ and a caller can tell them apart.

## Is the Duplicate Key in DETAIL if the Role Cannot Read It?

No. When RLS is enabled on the table, or the role lacks `SELECT`, Postgres omits the `DETAIL` line. The error text stays as it was, with the same SQLSTATE and constraint name, so the existence of the row is still disclosed. Hiding the value does not hide the fact.

The suppression is deliberate. In `BuildIndexValueDescription` in `src/backend/access/index/genam.c` on `REL_18_STABLE`, the comment reads: "First check if RLS is enabled for the relation. If so, return NULL to avoid leaking data." After that check, the function requires table-level `SELECT`, then column-level `SELECT` on every key column.

I ran three cases to see the effect. The first is the RLS tenant above, with no `DETAIL`. The second is a write-only role. `intake` has `INSERT` on `app.signups` and nothing else, with no RLS on that table:

```sql
SELECT email FROM app.signups;
INSERT INTO app.signups (email) VALUES ('dana@tenant-two.test');
```

```text
ERROR:  42501: permission denied for table signups
ERROR:  23505: duplicate key value violates unique constraint "signups_email_key"
SCHEMA NAME:  app
TABLE NAME:  signups
CONSTRAINT NAME:  signups_email_key
```

The role cannot read the table, and it still learns that `dana@tenant-two.test` is in it. The third case is the superuser repeating the tenant's duplicate insert, which gets the full message:

```text
ERROR:  23505: duplicate key value violates unique constraint "users_email_key"
DETAIL:  Key (email)=(bob@tenant-two.test) already exists.
```

That contrast is what the source predicts: `DETAIL` appears for a role that may read the key and is dropped for one that may not.

## Does ON CONFLICT DO NOTHING Reveal a Hidden Row?

Yes, and without raising an error. In the demo, `ON CONFLICT (email) DO NOTHING RETURNING id` returned `INSERT 0 0` and zero rows for an email held by another tenant, and `INSERT 0 1` with one row for an unused email. The row count is the oracle, so error handling in the attacker is not needed.

```sql
BEGIN;
INSERT INTO app.users (tenant_id, email, name)
  VALUES (1, 'carol@tenant-two.test', 'probe')
  ON CONFLICT (email) DO NOTHING RETURNING id;   -- (0 rows), INSERT 0 0
INSERT INTO app.users (tenant_id, email, name)
  VALUES (1, 'nobody@tenant-two.test', 'probe')
  ON CONFLICT (email) DO NOTHING RETURNING id;   -- 1 row, INSERT 0 1
ROLLBACK;
```

`DO UPDATE` behaved differently. Against Bob's hidden row, `ON CONFLICT (email) DO UPDATE SET name = 'probe'` failed with `ERROR: 42501: new row violates row-level security policy (USING expression) for table "users"`. That is a different code from `23505` and a different outcome from a silent zero. I did not run `DO UPDATE` against an unused email, so I do not count it as a third oracle.

## How Quickly Can a Tenant Enumerate Hidden Emails?

In my sandbox, in milliseconds. A PL/pgSQL loop of 1,000 candidate emails, with two real ones hidden in tenant 2 at positions 500 and 900, found both in 34.024 ms on PostgreSQL 18.6. Each insert is undone by a raised exception inside a subtransaction, so the loop leaves no rows behind and keeps probing.

```sql
DO $$
DECLARE hits int := 0; i int; guess text;
BEGIN
  FOR i IN 1..1000 LOOP
    guess := CASE i WHEN 500 THEN 'bob@tenant-two.test' WHEN 900 THEN 'carol@tenant-two.test'
                    ELSE 'user' || i || '@tenant-two.test' END;
    BEGIN
      INSERT INTO app.users (tenant_id, email, name) VALUES (1, guess, 'probe');
      RAISE EXCEPTION USING ERRCODE = 'P0001';  -- undo the insert, keep probing
    EXCEPTION
      WHEN unique_violation THEN hits := hits + 1; RAISE NOTICE 'exists: %', guess;
      WHEN raise_exception THEN NULL;
    END;
  END LOOP;
  RAISE NOTICE '% of 1000 guesses exist in another tenant', hits;
END $$;
```

```text
NOTICE:  00000: exists: bob@tenant-two.test
NOTICE:  00000: exists: carol@tenant-two.test
NOTICE:  00000: 2 of 1000 guesses exist in another tenant
DO
Time: 34.024 ms
```

The same loop took 61.109 ms on PostgreSQL 17.11 and 28.997 ms on 16.13. Those are one run each, on one laptop-class host, inside a container. They are not a benchmark. The loop runs as SQL inside the database; I did not test how fast the same guesses go through an application's sign-up endpoint. The point is the count, 2 of 1000, from a role that could read none of those rows.

## How Do You Make a Unique Constraint Tenant-Safe With RLS?

Scope the constraint to the tenant: replace `UNIQUE (email)` with `UNIQUE (tenant_id, email)`. Uniqueness is then checked only within one tenant, which is the set of rows the tenant can already see, so a collision reveals nothing new. The fix is two statements, run as the owner.

```sql
ALTER TABLE app.users DROP CONSTRAINT users_email_key;
ALTER TABLE app.users ADD CONSTRAINT users_tenant_email_key UNIQUE (tenant_id, email);
```

This is the same fix that [Bytebase's list of RLS footguns](https://www.bytebase.com/blog/postgres-row-level-security-footguns/) gives in its section on cross-tenant unique constraints, which uses `(tenant_id, lower(email))`. I did not test a `lower(email)` expression index, and I did not test what the change does to foreign keys that referenced `email`.

Within a single tenant the constraint still enforces uniqueness, and I checked that tenant 1 inserting its own `alice@tenant-one.test` still fails with `23505` on `users_tenant_email_key`. Even there the output has no `DETAIL` line, because RLS is on for the table. An application cannot count on parsing `DETAIL` to tell the user which value collided.

## Does UNIQUE (tenant\_id, email) Fully Close the Leak?

Yes, for the tested table. After the fix, tenant 1 inserted `bob@tenant-two.test` successfully, the 1,000-guess loop found `0 of 1000`, and writes aimed at tenant 2 failed identically whether or not the email existed. Where a value must be globally unique, the leak is the requirement itself.

The results after the fix, run as tenant 1:

- **Bob's address:** `INSERT ... (1, 'bob@tenant-two.test', ...) RETURNING id, tenant_id, email` returned `1010 | 1 | bob@tenant-two.test`, then I rolled it back. The id is 1010 because the loop's undone inserts had already consumed sequence values.
- **Writing into tenant 2:** an insert with `tenant_id = 2` returned `ERROR: 42501: new row violates row-level security policy for table "users"` for `bob@tenant-two.test` and for `nobody@tenant-two.test`. Bob's row in tenant 2 would have collided with the new key, so `WITH CHECK` rejected the row before the unique index answered, and the response carries no signal.
- **The loop:** `0 of 1000 guesses exist in another tenant`.

The fix cannot solve one case. If a login email must be unique across the whole system, then a global `UNIQUE (email)` is the requirement, and anyone who can attempt the insert learns whether an address is taken. Scoping works only where per-tenant uniqueness is acceptable. This is my reasoning from the results above, not something I tested.

## Limitations: What Does This Demo Not Cover?

The demo is a single sandbox with sample data. It covers PostgreSQL 16.13, 17.11 and 18.6 (official Alpine images, `x86_64-pc-linux-musl`), and results were identical on all three except the loop timing. Nothing here describes a production system, and I make no claim about any deployment's use of RLS.

Not tested:

- PgBouncer, or how an ORM or driver maps `23505` and `42501` into application errors.
- Timing side channels, such as index lookup time for a present versus an absent key.
- Whether sequence gaps, like the jump to id 1010, let a tenant infer anything.
- `ON CONFLICT DO NOTHING` with no conflict target, and `ON CONFLICT` for the `intake` role, which has no `SELECT`. The `CREATE POLICY` notes say an arbiter needs `SELECT` on the relation.
- `ON CONFLICT DO UPDATE` against an email that does not exist, so I do not claim it distinguishes presence the way `DO NOTHING` does.
- PostgreSQL 19, because `postgres:19-alpine` was not available on 11 November 2026.

The roles use throwaway passwords in a container with no network. For real credentials, the post on [rotating database credentials](/blog/vault-secret-rotation-application-downtime) covers the operational side. The demo's scripts (`setup.sql`, `probe.sql`, `probe_intake.sql`, `probe_owner.sql`, `fix.sql`, `probe_after.sql`) are short enough to paste, so I have not published a repository.

## References

- [PostgreSQL documentation, Row Security Policies](https://www.postgresql.org/docs/current/ddl-rowsecurity.html): referential integrity checks, "such as unique or primary key constraints and foreign key references, always bypass row security", and the warning about "covert channel" leaks.
- [PostgreSQL documentation, CREATE POLICY](https://www.postgresql.org/docs/current/sql-createpolicy.html): "This means there are indirect ways to determine that a given value exists", and the `SELECT` requirement for `ON CONFLICT`.
- [PostgreSQL documentation, INSERT](https://www.postgresql.org/docs/current/sql-insert.html): `ON CONFLICT` syntax and privileges.
- [PostgreSQL source, genam.c on REL\_18\_STABLE](https://github.com/postgres/postgres/blob/REL_18_STABLE/src/backend/access/index/genam.c): `BuildIndexValueDescription`, the `DETAIL` suppression under RLS.
- [Bytebase, Common Postgres Row-Level-Security footguns](https://www.bytebase.com/blog/postgres-row-level-security-footguns/): prior art for the tenant-scoped key.

The missing `DETAIL` line kept Bob's address off the screen, and SQLSTATE `23505` still told tenant 1 that it exists. How do you handle a login email that has to stay globally unique in an RLS multi-tenant schema: accept the disclosure, change the sign-up flow, or something else?