Re: Reducing the log spam - Mailing list pgsql-hackers

From Isaac Morland
Subject Re: Reducing the log spam
Date
Msg-id CAMsGm5eUBnrV6QR+-8ErTBXGw7qXEWAuJY+DwYZTSb5CGiXcwA@mail.gmail.com
Whole thread Raw
In response to Reducing the log spam  (Laurenz Albe <laurenz.albe@cybertec.at>)
Responses Re: Reducing the log spam
List pgsql-hackers
On Tue, 5 Mar 2024 at 07:55, Laurenz Albe <laurenz.albe@cybertec.at> wrote:
Inspired by feedback to [1], I thought about how to reduce log spam.

My experience from the field is that a lot of log spam looks like

  database/table/... "xy" does not exist
  duplicate key value violates unique constraint "xy"

So what about a parameter "log_suppress_sqlstates" that suppresses
logging ERROR and FATAL messages with the enumerated SQL states?

My idea for a default setting would be something like

  log_suppress_sqlstates = '23505,3D000,3F000,42601,42704,42883,42P01'

but that's of course bikeshedding territory.

I like the basic idea and the way of specifying states seems likely to cover a lot of typical use cases. Of course in principle the application should be fixed, but in practice we can't always control that.

I have two questions about this:

First, can it be done per role? If I have a particular application which is constantly throwing some particular error, I might want to suppress it, but not suppress the same error occasionally coming from another application. I see ALTER DATABASE name SET configuration_parameter … as being useful here, but often multiple applications share a database.

Second, where can this setting be adjusted? Can any session turn off logging of arbitrary sets of sqlstates resulting from its queries? It feels to me like that might allow security problems to be hidden. Specifically, the first thing an SQL injection might do would be to turn off logging of important error states, then proceed to try various nefarious things.

It seems to me the above questions interact; an answer to the first might be "ALTER ROLE role_specification SET configuration_parameter", but I think that would allow roles to change their own settings, contrary to the concern raised by the second question.

pgsql-hackers by date:

Previous
From: Nathan Bossart
Date:
Subject: Re: vacuumdb/clusterdb/reindexdb: allow specifying objects to process in all databases
Next
From: Michael Paquier
Date:
Subject: Re: Hooking into ExplainOneQuery() complicated by missing standard_ExplainOneQuery