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

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.

Admittedly, in your example there's a difference between what "the app"
should know and what "the user using the app" should know.  But I'm not
really seeing how Postgres could usefully model that situation.  We have
no idea about the structure of the client-side logic.

BTW, I notice that ExecBuildSlotValueDescription punts and never prints
anything if the table in question has RLS enabled.  So maybe a workable
kluge for the OP is to enable RLS?

            regards, tom lane