PostgreSQL Audit Data Collection Reference

G PostgreSQL Audit Data Collection Reference

Learn how to collect audit data from PostgreSQL.

G.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.

G.2 Installing PostgreSQL

Learn how to install PostgreSQL.

Refer to the documentation in the following link:

https://www.postgresql.org/download/linux/redhat/
Install the relevant PostgreSQL version.

G.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:

Run the following command to log in as user postgres:
```
sudo -u postgres psql
```

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;

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

Log in using the newly created PostgreSQL non super user.
Create a sample database by running the following commands:
```
createdb <new database name>
```
Log in to the newly created database with the newly created PostgreSQL non super user by running the following command:
```
psql <new database name>
```
Exit the database prompt by running exit or \q command.

Install the pgaudit Extension on PostgreSQL Database

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
```
Follow the steps provided in Compile and Install section in the below link, to install pgaudit extension.

https://github.com/pgaudit/pgaudit
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'
```
Log in as root user. Restart the PostgreSQL service by running these commands:
```
/sbin/service postgresql-11 stop
```
```
/sbin/service postgresql-11 start
```

Enable pgaudit Audit Logging in PostgreSQL Database

Note:

For complete information on all the PGAUDIT settings, refer to the respective documentation.

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_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

/sbin/service postgresql-11 stop

/sbin/service postgresql-11 start

Generate Audit Log

Log in as the newly created PostgreSQL non super user. Now, log in to the newly created database using below command:
```
psql <database name>
```
Run commands to create tables, insert data into the tables, and query the data from the tables.
The logs are present in the following location for PostgreSQL 11 (example) installation on Oracle Linux 6.

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