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.

Page top