Not getting "Detail" section of unique constraint error when row level security in effect

Question

TL;DR: On Postgres 10.6 When row level security is in effect - I'm not getting the "DETAIL" section of a unique constraint violation error even though that role can see/insert all rows.

I have 2 roles: admin (owner of the tables) and app_role on which row level security is in effect.

Here's how to reproduce:

--using the admin role
create table parents(
    parent_id int primary key, 
    parent_name text unique
);
create table childs(
    child_id int primary key, 
    child_name text, 
    parent_id int references parents (parent_id), 
    constraint childs_uq unique (parent_id, child_name)
);

insert into parents values(1,'aaa');
insert into childs values(1,'bbb',1);
insert into childs values(2,'bbb',1);
--SQL Error [23505]: ERROR: duplicate key value violates unique constraint "childs_uq"
--  Detail: Key (parent_id, child_name)=(1, bbb) already exists.

Note the "Detail: Key (parent_id, child_name)=(1, bbb) already exists."

The app_role role was given the following grants and RLS policies:

GRANT SELECT, UPDATE, INSERT, DELETE ON parents TO app_role;
GRANT SELECT, UPDATE, INSERT, DELETE ON childs TO app_role;

CREATE POLICY parents_select
ON parents 
AS permissive
FOR SELECT
TO app_role
USING (true);

alter table parents enable row level security;

CREATE POLICY childs_select
ON childs 
AS permissive
FOR SELECT
TO app_role
USING (true);

CREATE POLICY childs_insert
ON childs 
AS permissive
FOR INSERT
TO app_role
WITH CHECK (true);

alter table childs enable row level security;

Now for the problem:

--using `app_role`
insert into childs values(3,'ccc',1); -- works

insert into childs values(2,'bbb',1);
--SQL Error [23505]: ERROR: duplicate key value violates unique constraint "childs_uq"

The error does not contain the "Detail" section.

Is it a bug? Expected behavior (would love to see a documentation reference)?

Laurenz Albe Laurenz Albe · Accepted Answer · 2019-03-27T09:57:37

This is working as ~~designed~~implemented and is documented in src/backend/access/index/genam.c, in the comments to function BuildIndexValueDescription:

char *
BuildIndexValueDescription(Relation indexRelation,
                           Datum *values, bool *isnull)
{
[...]
    /*
     * Check permissions- if the user does not have access to view all of the
     * key columns then return NULL to avoid leaking data.
     *
     * First check if RLS is enabled for the relation.  If so, return NULL to
     * avoid leaking data.
     *
     * Next we need to check table-level SELECT access and then, if there is
     * no access there, check column-level permissions.
     */
[...]
    /* RLS check- if RLS is enabled then we don't return anything. */
    if (check_enable_rls(indrelid, InvalidOid, true) == RLS_ENABLED)
        return NULL;

The problem with showing the detail is obvious:

If you know which key your entry conflicts with, you may get some information about rows in the database that you cannot see.

This may seem unnecessary in the context of a primary key conflict, because you know what the conflicting values are, even if you don't get the detail message, but the case is different with – say – an exclusion constraint. It seems like nobody cared enough to make a distinction.

Also there could be a check if you can see the conflicting row or not and you could get the detail message in the former case, but it seems like nobody bothered.

Not getting "Detail" section of unique constraint error when row level security in effect

1 Answers