Installing the pgAudit plugin
To install the pgAudit plugin:
- 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
- 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.
- Create a backup copy of the postgres.conf configuration file.
- 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'
```
- Restart the PostgreSQL service using the command:
sudo systemctl restart postgresql
- To load the pgAudit plugin to PostgreSQL, run the following command on the PostgreSQL command line:
CREATE EXTENSION pgaudit
The pgAudit plugin is installed.