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

On Thu, Jun 20, 2019 at 9:17 AM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Karsten Hilbert <Karsten(dot)Hilbert(at)gmx(dot)net> writes:
> > Sure, but we are currently exploring whether the database
> > reflects any values that it had not been given by the same
> > user beforehand.
>
> > There might be another scenario:
> > user enters value for column 1
> > app adds in secret-to-the-user value for column 2
> > UPDATE fails
> > error message reflects val 1 and secret val 2
> > app displays both values
> > user knows secret value 2
>
> I wondered about this, but a test case did not show it to be possible,
> and after some looking around I found the responsible code. The
> "Failing row contains ..." message is built by execMain.c's
> ExecBuildSlotValueDescription() function, and it will only print columns
> for which (a) the value was supplied in the current INSERT or UPDATE
> command, or (b) the calling user has privileges to SELECT that column.
> So we have expended at least some effort towards blocking that sort of
> hole.
>
>
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?

David J.