pgsql-logs Log Parser by crowdsecurity

Installation

sudo cscli parsers install crowdsecurity/pgsql-logs

Then reload CrowdSec: sudo systemctl reload crowdsec

Sign in to see which of your engines run it →

Configuration

Hover a line or a note to see what each field does.

parsers/s01-parse/crowdsecurity/pgsql-logs.yaml· 31 linesGitHub

1onsuccess: next_stage1
2name: crowdsecurity/pgsql-logs
3description: "Parse PgSQL and PgBouncer logs"
4filter: "evt.Parsed.program in ['postgres', 'pgbouncer']"2
5nodes:3
6  - grok:4
7    #pgsql 123
8      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: message
10  - grok:4
11    #aws aurora3
12    #%{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: message
15  - grok:4
16    #pgbouncer authentication failed3
17      pattern: '%{TIMESTAMP_ISO8601:timestamp} %{WORD:zone} \[%{INT:PID}\] ERROR C-%{NOTSPACE:connection_id}: %{DATA:pgsql_dbname}/%{DATA:pgsql_target_user}@\[?%{IP:source_ip}\]?:%{INT:source_port} %{WORD:auth_method} authentication failed'
18      apply_on: message
19statics:5
20  - meta: log_type
21    value: pgsql_failed_auth
22  - meta: auth_method
23    expression: "evt.Parsed.auth_method"
24  - meta: source_ip
25    expression: "evt.Parsed.source_ip"
26  - meta: user
27    expression: "evt.Parsed.pgsql_target_user"
28  - meta: db
29    expression: "evt.Parsed.pgsql_dbname"
30  - target: evt.StrTime
31    expression: evt.Parsed.timestamp

Documentation

PostgreSQL and PgBouncer Logs Parser

PostgreSQL and PgBouncer failed authentication parser.

Supports PostgreSQL (v12+, AWS Aurora) as well as PgBouncer authentication failure formats (IPv4 and IPv6).

Warning: By default (at least on Debian with PostgreSQL 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.

Acquisition Configuration

PostgreSQL

Example configuration for PostgreSQL log file in /etc/crowdsec/acquis.yaml:

1filenames:
2  - /var/log/postgresql/postgresql-*.log
3labels:
4  type: postgres

PgBouncer

Example configuration for PgBouncer log file in /etc/crowdsec/acquis.yaml:

1filenames:
2  - /var/log/postgresql/pgbouncer.log
3labels:
4  type: pgbouncer

Note: Both type: pgbouncer and type: postgres are supported by this parser.

Parsed Fields

Field	Description
`source_ip`	Remote client IP address (IPv4 or IPv6)
`user`	Database user being authenticated
`db`	Target database name
`auth_method`	Authentication method used
`log_type`	Set to `pgsql_failed_auth`

pgsql-logs