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.