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

From William Denton
Subject Re: Row data is reflected in DETAIL message when constraints fail on insert/update
Date
Msg-id CAO8wz8TFWvqx+zVhpr-vp1X5vcC5+Z_W+QujCkKXXQ5LdPnsmQ@mail.gmail.com
Whole thread Raw
In response to Re: Row data is reflected in DETAIL message when constraints fail oninsert/update  (Karsten Hilbert <Karsten.Hilbert@gmx.net>)
Responses Re: Row data is reflected in DETAIL message when constraints fail oninsert/update
Re: Row data is reflected in DETAIL message when constraints fail oninsert/update
List pgsql-general
>I guess the original question basically boils down to "Given a
>rogue/dumb app, and a DBA who neglected his job, is it PG's
>business (or even within its possibilities) to mop up ?"

It feels like you aren't setting people up to land in the pit of success. 
It's easy to sit back and call people negligent because they failed to 
change settings from their defaults. Data breaches are all too common 
due to mis-configured systems, we can all have a good laugh at the 
poor people who have suffered breaches due to defaults that 
come/came with s3, mongo and many other data stores, but why must 
we operate on that level to rather than being a little more defensive?

How is it useful in a normally configured database to return row data in
error messages?

Is the client application supposed to parse that data?

Must the client perform another query to discover column names and 
attributes so the data can be parsed?

I can definitely see a use for it during debugging and development where a 
human has their eyes on what the database is returning, but I would argue 
if you wanted that information for debugging purposes you would enable 
verbose logging.

I have spent a few minutes searching google for terms like 
"harden postgres for production" or "locking down postgres" or  
"postgres production configuration". NONE mention log_error_verbosity. 
Searching the postgres wiki yields no results for log_error_verbosity. Only 
once you start searching for the problems caused by log_error_verbosity 
can you become aware that this setting exists and should be changed in 
production environments. Yet the only mention on of this parameter on any
postgres site (docs or wiki) is the one pasted below Calling people negligent
for not knowing something, when you have failed to tell them seems disingenuous.

Further, the documentation for log_error_verbosity mentions nothing about the 
data returned to the client. This text is explicitly talking about the server log.

>Controls the amount of detail written in the server log for each message that is 
>logged. Valid values are TERSE, DEFAULT, and VERBOSE, each adding more 
>fields to displayed messages. TERSE excludes the logging of DETAIL, 
>HINT, QUERY, and CONTEXT error information. VERBOSE output includes the 
>SQLSTATE error code (see also Appendix A) and the source code file name, 
>function name, and line number that generated the error. Only superusers can 
>change this setting.

I would suggest that row data should be reclassified as only appearing in 
VERBOSE configurations as there is nothing an application client could do with 
that information, it is only useful to a human operating interactively with the db.

Cheers,

William

On Sat, 22 Jun 2019 at 20:40, Karsten Hilbert <Karsten.Hilbert@gmx.net> wrote:
On Thu, Jun 20, 2019 at 12:16:53PM -0400, Tom Lane wrote:

> 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.

Absolutely. Perhaps another solution to that problem would be
for OP to tell PG about the desired client-side logic by
wrapping all data access into views and/or functions (cf data
masking).

I guess the original question basically boils down to "Given a
rogue/dumb app, and a DBA who neglected his job, is it PG's
business (or even within its possibilities) to mop up ?"

I'd be inclined to say No.

I would agree it is not entirely trivial to accept
that resolve, though.

Karsten
--
GPG  40BE 5B0E C98E 1713 AFA6  5BC0 3BEA AC80 7D4F C89B




pgsql-general by date:

Previous
From: Adrian Klaver
Date:
Subject: Re: Inserts restricted to a trigger
Next
From: Joe Conway
Date:
Subject: Re: Row data is reflected in DETAIL message when constraints fail oninsert/update