I’m trying to view slow queries in my Postgres log, but it’s filled with errors like:
2023-07-26 07:40:47.460 UTC [3446461] odoo@postgres FATAL: password authentication failed for user "odoo"
2023-07-26 07:40:47.460 UTC [3446461] odoo@postgres DETAIL: Role "odoo" does not exist.
Connection matched pg_hba.conf line 106: "host all all 0.0.0.0/0 scram-sha-256"
2023-07-26 07:40:47.996 UTC [3446463] rdsadmin@postgres FATAL: password authentication failed for user "rdsadmin"
2023-07-26 07:40:47.996 UTC [3446463] rdsadmin@postgres DETAIL: Role "rdsadmin" does not exist.
Connection matched pg_hba.conf line 106: "host all all 0.0.0.0/0 scram-sha-256"
Pretty sure these aren’t generated by me, it’s a bot trying different passwords. There’s not much I can do to prevent bots from guessing passwords while still being able to connect to the DB remotely. Is there a way to exclude these entries from the error log? I don’t consider wrong passwords to be errors, it’s part of the normal DB operations.
2
Answers
Logging in Postgres is controlled by log_error_verbosity.
Setting it to TERSE will exclude some information like DETAIL, QUERY, HINT. This is how you do it:
Documentation here.
No, not unless you are willing to compile your own PostgreSQL (in which case it is a one-line change). PostgreSQL doesn’t provide fine-grained configurability of what type of message gets logged. But it is pretty easy to find slow query entries anyway, just be searching for key string, like ‘duration:’. Or filtering them out should be easy with any decent log parsing tool I would think, though I have not done it.
If you did suppress those messages, it could make it hard to debug your own authentication problems if you ever run into them. If you want to suppress them only from unknown IP addresses, you might as well just kill those IP addresses at the firewall.