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

>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(dot)Hilbert(at)gmx(dot)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
>
>
>
>
>