Re: Row data is reflected in DETAIL message when constraints fail on insert/update

"David G. Johnston" <david.g.johnston@gmail.com> writes:
> Just to be clear here, the OP provided the following query example:

> test=# update person set email = null;
> ERROR:  null value in column "email" violates not-null constraint
> DETAIL:  Failing row contains (william, denton, null).

> The presence of william and denton in the error detail was because the user
> updating the table has select access on first and last name.  If they did
> not those fields would not have been part of the error message?  I'm not in
> a position to experiment right now but what does/should it show in the
> restrictive case?

regression=# create user joe;
CREATE ROLE
test=# create table person(first text, last text, email text not null);
CREATE TABLE
test=# grant select(email),update(email) on person to joe;
GRANT
test=# insert into person values('william','denton','wd40@gmail.com');
INSERT 0 1
test=# \c - joe
You are now connected to database "test" as user "joe".
test=> update person set email = null;
psql: ERROR:  null value in column "email" violates not-null constraint
DETAIL:  Failing row contains (email) = (null).

The DETAIL in this case would be the same whether joe had select(email)
privilege or not; the email value is considered OK to report since it
came from the query not the table.

            regards, tom lane