PostgreSQL · 23505
duplicate key value violates unique constraint — and what it leaks
Luca Urti
duplicate key value violates unique constraint
Why do I get a duplicate key error for a row I cannot even see?
Unique constraints are enforced by an index over the whole table, and an index knows nothing about Row Level Security. Policies filter which rows a query returns; they do not partition the key space. So an insert that collides with another user's row is rejected with 23505 even though a select for that same value returns nothing — the row is invisible to you and still occupies the key. This is worth understanding rather than working around, because it means any table with a unique column on user-supplied data is an existence oracle: submit a value, and the error tells you whether somebody already has it.
The leak, stated plainly
If profiles.handle is unique and anyone can attempt an insert, then anyone can test whether a handle is taken. For a handle that is usually fine and often the point. For a column holding an email address, a phone number or a customer reference it is an enumeration endpoint, and it works even with flawless policies on the table, because the constraint is checked before any policy is consulted.
Decide deliberately which it is. If the value must be globally unique and secret, the uniqueness check belongs behind a server route that rate-limits and returns a neutral answer — not in a constraint the client can probe directly.
Scope the constraint to the owner when uniqueness is per-user
Very often uniqueness was never meant to be global. A tag name, a project slug, a category — these need to be unique per user, not across all users, and a global unique index on them is both a bug and a leak. A composite unique index on (user_id, name) says what was actually meant, removes the collision between strangers, and removes the oracle at the same time.
Why the upsert then fails with an RLS error
The usual next step is on conflict do update, and on somebody else's row that raises a policy error naming the USING expression. The reason is precise: an upsert that lands on an existing row performs an UPDATE, and an UPDATE has to be able to see the row it is updating. Your policy correctly says you cannot, so the statement is refused — which is Row Level Security doing exactly its job, and the last line of defence between an enumeration and an overwrite.
The fix
make the constraint say what you meant
-- Per-user uniqueness, which is what most of these constraints were for.
-- Removes both the spurious collision and the ability to probe for strangers' values.
alter table public.profiles drop constraint if exists profiles_handle_key;
create unique index profiles_handle_per_user
on public.profiles (user_id, handle);
-- If it genuinely must be globally unique, keep the constraint and handle 23505
-- as a normal outcome rather than an exception:
-- const { error } = await supabase.from('profiles').insert(row);
-- if (error?.code === '23505') return { taken: true };
-- Return the same shape and timing whether or not it was taken, or the endpoint
-- is still an oracle with extra steps.The fix that works and costs you the database
The upsert raises a policy error mentioning the USING expression, and the fix that makes it go away is loosening that expression — widening the UPDATE or SELECT policy until the statement is allowed to see the conflicting row. That is precisely the protection stopping one user from overwriting another's record: with using (true) in place, the upsert now succeeds against a stranger's row and replaces its contents. An enumeration weakness becomes a write primitive, and the error that was warning you about it is gone.
Whether the trap is already in your repo is a question you can answer
Sentris reads the SQL and the client code, so it reports the shortcut above where it was actually taken — a service_role key in a browser bundle, a policy that is using (true), a table with RLS switched off. A scan needs no account and no card. How often it is wrong is measured and published.