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