Configuring receipt of PostgreSQL events

KUMA lets you monitor and audit PostgreSQL events on Linux devices using rsyslog.

Events are audited using the pgAudit plugin. The plugin supports PostgreSQL 9.5 and later. For details about the pgAudit plugin, see https://github.com/pgaudit/pgaudit.

Configuring event receiving consists of the following steps:

1. Installing the pdAudit plugin.
2. Creating a KUMA collector for PostgreSQL events.

   To receive PostgreSQL events using rsyslog, in the collector installation wizard, at the Event parsing step, select the [OOTB] PostgreSQL pgAudit syslog normalizer.

3. Installing a collector in the KUMA network infrastructure.
4. Configuring the event source server.
5. Verifying receipt of PostgreSQL events in the KUMA collector

   You can verify that the PostgreSQL event source server is correctly configured in the Searching for related events section of the KUMA Console.

Installing the pgAudit plugin

To install the pgAudit plugin:

1. On the OS command line, run the following commands as a user with administrator rights:

   sudo apt update

   sudo apt -y install postgresql-<PostgreSQL version>-pgaudit

   You must select the plugin version to match the PostgresSQL version. For information about PostgreSQL versions and the matching plugin versions, see https://github.com/pgaudit/pgaudit#postgresql-version-compatibility.

   Example: sudo apt -y install postgresql-12-pgaudit

2. Find the postgres.conf configuration file. To do so, run the following command on the PostgreSQL command line:

   show data_directory

   The response will indicate the location of the configuration file.

3. Create a backup copy of the postgres.conf configuration file.
4. Open the postgres.conf file and copy or replace the values in it with the values listed below.

   ```

   ## pgAudit settings

   shared_preload_libraries = 'pgaudit'

   ## database logging settings

   log_destination = 'syslog'

   ## syslog facility

   syslog_facility = 'LOCAL0'

   ## event ident

   syslog_ident = 'Postgres'

   ## sequence numbers in syslog

   syslog_sequence_numbers = on

   ## split messages in syslog

   syslog_split_messages = off

   ## message encoding

   lc_messages = 'en_US.UTF-8'

   ## min message level for logging

   client_min_messages = log

   ## min error message level for logging

   log_min_error_statement = info

   ## log checkpoints (buffers, restarts)

   log_checkpoints = off

   ## log query duration

   log_duration = off

   ## error description level

   log_error_verbosity = default

   ## user connections logging

   log_connections = on

   ## user disconnections logging

   log_disconnections = on

   ## log prefix format

   log_line_prefix = '%m|%a|%d|%p|%r|%i|%u| %e '

   ## log_statement

   log_statement = 'none'

   ## hostname logging status. dns bane resolving affect

   #performance!

   log_hostname = off

   ## logging collector buffer status

   #logging_collector = off

   ## pg audit settings

   pgaudit.log_parameter = on

   pgaudit.log='ROLE, DDL, MISC, FUNCTION'

   ```

5. Restart the PostgreSQL service using the command:

   sudo systemctl restart postgresql

6. To load the pgAudit plugin to PostgreSQL, run the following command on the PostgreSQL command line:

   CREATE EXTENSION pgaudit

The pgAudit plugin is installed.

Configuring a Syslog server to send events

The rsyslog service is used to transmit events from the server to KUMA.

To configure the sending of events from the server where PostgreSQL is installed to the collector:

1. To verify that the rsyslog service is installed on the event source server, run the following command as administrator:

   sudo systemctl status rsyslog.service

   If the rsyslog service is not installed on the server, install it by executing the following commands:

   yum install rsyslog

   sudo systemctl enable rsyslog.service

   sudo systemctl start rsyslog.service

2. In the /etc/rsyslog.d/ directory, create a pgsql-to-siem.conf file with the following content:

   If $programname contains 'Postgres' then @<IP address of the collector>:<port of the collector>

   For example:

   If $programname contains 'Postgres' then @192.168.1.5:1514

   If you want to send events via TCP, the contents of the file must be as follows:
   If $programname contains 'Postgres' then @@192.168.1.5:2514

   Save changes to the pgsql-to-siem.conf configuration file.

3. Add the following lines to the /etc/rsyslog.conf configuration file:

   $IncludeConfig /etc/pgsql-to-siem.conf

   $RepeatedMsgReduction off

   Save changes to the /etc/rsyslog.conf configuration file.

4. Restart the rsyslog service by executing the following command:

   sudo systemctl restart rsyslog.service