cscli parsers install crowdsecurity/pgsql-logsPostgreSQL fail authentication parser.
warning : By default (at least on debian with pgsql 12), postgreSQL logs do not contain the source IP, and log_line_prefix needs to be edited to contain %h (the remote host). This parser assumes the log_line_prefix is %m [%p] %h%q %u@%d (instead of the default %m [%p] %q%u@%d )
Please note that the parser ignores the timezone written by postgres.
1onsuccess: next_stage2name: crowdsecurity/pgsql-logs3description: "Parse PgSQL logs"4filter: "evt.Parsed.program == 'postgres'"5nodes:6 - grok:7 #pgsql 128 pattern: '%{DATESTAMP:timestamp} %{WORD:zone} \[%{INT:PID}\] %{IP:source_ip} %{USERNAME:pgsql_user}@%{GREEDYDATA:pgsql_dbname} FATAL: %{WORD:auth_method} authentication failed for user "%{USERNAME:pgsql_target_user}"'9 apply_on: message10 - grok:11 #aws aurora12 #%{DATESTAMP:timestamp} %{WORD:zone}:%{IP:source_ip}\(%{INT:source_port}\):%{USERNAME:pgsql_user}@%{GREEDYDATA:pgsql_dbname}:\[%{INT:pid}\]:FATAL: password authentication failed for user "%{USERNAME:pgsql_target_user}"13 pattern: '%{DATESTAMP:timestamp} %{WORD:zone}:%{IP:source_ip}\(%{INT:source_port}\):%{USERNAME:pgsql_user}@%{GREEDYDATA:pgsql_dbname}:\[%{INT:pid}\]:FATAL: %{WORD:auth_method} authentication failed for user "%{USERNAME:pgsql_target_user}"'14 apply_on: message15statics:16 - meta: log_type17 value: pgsql_failed_auth18 - meta: auth_method19 expression: "evt.Parsed.auth_method"20 - meta: source_ip21 expression: "evt.Parsed.source_ip"22 - meta: user23 expression: "evt.Parsed.pgsql_target_user"24 - meta: db25 expression: "evt.Parsed.pgsql_dbname"26 - target: evt.StrTime27 expression: evt.Parsed.timestamp