Re: Row data is reflected in DETAIL message when constraints fail on insert/update - Mailing list pgsql-general

From David G. Johnston
Subject Re: Row data is reflected in DETAIL message when constraints fail on insert/update
Date
Msg-id CAKFQuwYLfTQLnB4a1DPDcRT1xApWoupDwjcDbxsW48Vq5h-47A@mail.gmail.com
Whole thread Raw
In response to Re: Row data is reflected in DETAIL message when constraints fail on insert/update  (Tom Lane <tgl@sss.pgh.pa.us>)
Responses Re: Row data is reflected in DETAIL message when constraints fail on insert/update
List pgsql-general
On Thu, Jun 20, 2019 at 9:17 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
Karsten Hilbert <Karsten.Hilbert@gmx.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.

pgsql-general by date:

Previous
From: Adrian Klaver
Date:
Subject: Re: Detaching multiple partitions in 1 ALTER TABLE statement
Next
From: Tom Lane
Date:
Subject: Re: Row data is reflected in DETAIL message when constraints fail on insert/update