On 6/22/19 10:09 AM, Karsten Hilbert wrote:
> On Sat, Jun 22, 2019 at 06:40:10PM +0200, Peter J. Holzer wrote:
>
>>> How is it useful in a normally configured database to return row data in
>>> error messages?
>>
>> This is extremely useful. It tells you what data didn't match your
>> program's expectations. Otherwise you just get a vague "unique
>> constraint violation"
>
> Sure, except some argue that PG not send such information to
> the *client* by *default*, which seems to have some merit
> (the default should, however, keep logging such data to the
> PG log)
Two points:
1) From Tom Lanes post upstream, the client is the one sending the data
to the server so it/they already know what it is.
2) Defining the client. In most non-trivial cases there is a stack of
clients. For example in the Django framework I use when connecting to a
Postgres db there is:
a) psycopg2 --the client that the message is actually going to.
b) The ORM the client that sits above 1) --though it is possible to
bypass this level.
c) The views, the clients that sit above 1) & 2)
When deploying I ensure the DEBUG setting is set to False to ensure the
error does not bubble up from 1) to the end user that is looking at the
output of 3). I do capture the errors and log them(to secure file) for
use in identifying issues. I also pop up a generic message to the end
user based off the db error to give them context for why some action did
not happen e.g. duplicate item(key). Therefore for my use cases the
detailed information being sent to the low level client(psycopg2) is
very useful and essential to fixing problems.
What it comes down is that security is situation specific and ever
changing. Depending on a generic program be it a database or framework
or something else to anticipate all your requirements is unrealistic and
ultimately insecure.
>
> This can lead to the following problem:
>
>> and you can then search through a hundred million
>> rows of data to find that violation.
>
> which could be solved by passing to the client an identifier
> instead of the row data which is also logged to the server
> log alongside the row data. The combination of
>
> %m or %n - timestamp
> %c - session ID
> %l - in-session log line idx
> %e - SQLSTATE
>
> would probably suffice if sent to the client, given it is
> logged in the server log.
>
> (not that I suggest any such thing as I certainly lack the
> skills to provide a patch)
>
> Karsten
> --
> GPG 40BE 5B0E C98E 1713 AFA6 5BC0 3BEA AC80 7D4F C89B
>
>
>
--
Adrian Klaver
adrian.klaver@aklaver.com
