E PostgreSQL Audit Data Collection Reference

Learn how to collect audit data from PostgreSQL.

E.1 Introduction to PostgreSQL Audit Data Collection

Learn how to collect audit data from PostgreSQL.

PostgreSQL is a open source object relational database system that uses and extends the SQL language combined with many features. It safely stores and scales the most complicated data workload. The origin of PostgreSQL dates back to 1986 as part of the POSTGRES project at the University of California in Berkeley, and has more than 30 years of active development on the core platform.

PostgreSQL must be configured to generate audit data in CSV format. The PostgreSQL audit extension (or pgaudit) provides detailed session and object audit logging through the standard logging facility provided by PostgreSQL.

Installing pgaudit extension on the PostgreSQL database is a must for audit collection. Audit Vault Agent supports collection of PostgreSQL audit events only if the pgaudit extension is installed and PostgreSQL is configured to generate audit data in CSV format.

E.2 Installing PostgreSQL

Learn how to install PostgreSQL.

Refer to the following link to install relevant PostgreSQL version:

https://www.postgresql.org/download/linux/redhat/

  1. Log in as user root and run the commands in this procedure.
  2. Run the following command to install the repository RPM:
    yum install <URL to download the RPM>

    Example command to install Postgres 11 on Oracle 6 with x86_64 architecture:

    yum install https://download.postgresql.org/pub/repos/yum/reporpms/EL-6-x86_64/pgdg-redhat-repo-latest.noarch.rpm
  3. Run the following example command to install PostgreSQL 11 server:
    yum install postgresql11-server
  4. Run the following example commands to optionally initialize the PostgreSQL 11 database and enable automatic start:
    /sbin/service postgresql-11 initdb
    /sbin/chkconfig postgresql-11 on
    /sbin/service postgresql-11 start

E.3 Steps After Installing PostgreSQL

Run post PostgreSQL installation steps.

Update PostgreSQL Super User Password

A default super user postgres is created during PostgreSQL installation. Run the following command to change the password for postgres user:

sudo passwd postgres

Create PostgreSQL Non Super User

Create a new user with necessary permission to create databases, and set the password. Run the following command to create the new user:

sudo -u postgres createuser <new user name> -d -P

Create root Permission on PostgreSQL Database

The role root is required for installing pgaudit extension on PostgreSQL database. Perform these steps:

  1. Run the following command to log in as user postgres:

    sudo -u postgres psql
  2. Run the following commands in the postgres command prompt:

    create role root superuser;
    alter user root with password <new password>;
    alter role root with login;
  3. Find the PostgreSQL configuration file location by running the following commands, and then exit the postgres command prompt. Make a note of the configuration file details, as it is updated in the later part of this topic.

    show config_file;
    exit;

Create a Sample Database

  1. Log in using the newly created PostgreSQL non super user.

  2. Create a sample database by running the following commands:

    createdb <new database name>
  3. Log in to the newly created database with the newly created PostgreSQL non super user by running the following command:

    psql <new database name>
  4. Exit the database prompt by running exit or \q command.

Install the pgaudit Extension on PostgreSQL Database

Refer to the below link for detailed steps to install pgaudit extension:

https://github.com/pgaudit/pgaudit

  1. Log in as root user and run the below commands to install PostgreSQL developer libraries. The commands used in this section are for installing PostgreSQL 11 developer libraries as example only.

    yum install postgresql11-devel
  2. Log in as root user. In the PostgreSQL configuration file, update the shared_preload_libraries parameter to include pgaudit, and save the file.

    shared_preload_libraries = 'pgaudit'
  3. Log in as root user. Restart the PostgreSQL service by running these commands:

    /sbin/service postgresql-11 stop
    /sbin/service postgresql-11 start
  4. Log in as root user and modify the path variable as follows:

    export PATH="$PATH:/usr/pgsql-11/bin"
  5. Log in as root user and run the following commands to install pgaudit:

    git clone https://github.com/pgaudit/pgaudit.git
    cd pgaudit
    git checkout REL_11_STABLE
    make install USE_PGXS=1
    make installcheck USE_PGXS=1

Note:

In case the installcheck command throws error 1 of 1 tests failed, then ignore the error message.

Enable pgaudit Audit Logging in PostgreSQL Database

Note:

For complete information on all the PGAUDIT settings, refer to the respective documentation.
  1. Log in as postgres user:

    sudo -u postgres psql
  2. Run the following commands in postgres command prompt, for enabling auditing for the newly created database:

    CREATE EXTENSION pgaudit;
    alter database <database name> set pgaudit.log = 'all';
    SET pgaudit.log = 'ALL';
    SET pgaudit.log_level = 'notice';
    SET pgaudit.log_client = ON;
    SET pgaudit.log_relation = ON;
    SET pgaudit.log_parameter = ON;
    SET pgaudit.log_catalog = ON;
    alter system set log_connections=on;
    alter system set log_disconnections=on;
    alter system set log_statement='all';
    alter system set pgaudit.log_parameter TO 'on';
    SELECT pg_reload_conf();

Update Audit Logging Parameters in PostgreSQL Configuration File

Note:

For complete information on all the PostgresSQL logging related parameters, refer to the respective documentation.

Log in as root user. Edit the PostgreSQL configuration file and update below parameters as follows:

log_destination = 'csvlog'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
log_min_messages = info
log_checkpoints = on
log_connections = on
log_disconnections = on
log_duration = on
log_error_verbosity = verbose
log_hostname = on
log_statement = 'all'
log_directory = '<full path of directory where log files need to be stored>'
log_file_mode = 0640

Log in as root user and restart the PostgreSQL service. The following example commands are for PosgreSQL 11:

/sbin/service postgresql-11 stop
/sbin/service postgresql-11 start

Generate Audit Log

  1. Log in as the newly created PostgreSQL non super user. Now, log in to the newly created database using below command:

    psql <database name>
  2. Run commands to create tables, insert data into the tables, and query the data from the tables.

  3. The logs are present in the following location for PostgreSQL 11 (example) installation on Oracle Linux 6.

    /var/lib/pgsql/11/data/log/