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.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. -
Log in as root user. In the PostgreSQL configuration file, update the
shared_preload_libraries
parameter to includepgaudit
, 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.-
Log in as postgres user:
sudo -u postgres psql
-
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
-
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/