It is a very common requirement in the database world to audit database activities such as Sarbanes-Oxley financial regulations or service level impacts. Reason for auditing, granularity of audit information and type of auditing may differ. The PostgreSQL Audit extension (pgaudit) for Postgres provides most of the necessary features for auditing. The BigSQL project ships the ready-to-use pgaudit extension along with Postgres binaries for different platforms.
Let us explore how easy it is to set it up along with Postgres 9.6
PostgreSQL should be loading the pgaudit extension libraries during start up. To achieve this, we just need to specify a parameter and restart the PostgreSQL instance.
postgres=# alter system set shared_preload_libraries='pgaudit'; ALTER SYSTEM
$ ./pgc restart pg96 pg96 stopping pg96 starting on port 5432
Its good to verify this step once postgres instance comes up
postgres=# select name,setting,pending_restart from pg_settings where name='shared_preload_libraries'; name | setting | pending_restart --------------------------+---------+----------------- shared_preload_libraries | pgaudit | f
Example Use Case
A typical case of auditing is the change control in a controlled production environment. Security policies may require every change to database structure (DDLs) be audited / auditable.
Enabling DDL auditing at Instance Level
postgres=# alter system set pgaudit.log = 'ddl';
This doesn’t require a bounce.
**pgaudit allows us to have more granularity at the database level or even at the particular role level. Please see the official documentation for all options available and extensive features.
Testing the DDL Audit.
Lets try creating a new table
postgres=# create table t1 (id int,name varchar(30)); CREATE TABLE
Postgres keeps all audit information in postgresql’s standard log file as specified by log_directory and log_filename parameters.
Now the following audit lines appear in the log file:
2016-09-15 08:05:42 EDT : [9-1] user=postgres,db=postgres,app=psql,client=[local] LOG: AUDIT: SESSION,2,1,DDL,CREATE TABLE,,,"create table t1 (id int,name varchar(30));",<not logged>
Let’s try altering the table:
postgres=# alter table t1 alter column name type varchar(50); ALTER TABLE
The following line appears in the log file:
2016-09-15 08:08:10 EDT : [18-1] user=postgres,db=postgres,app=psql,client=[local] LOG: AUDIT: SESSION,3,1,DDL,ALTER TABLE,,,alter table t1 alter column name type varchar(50);,<not logged>
postgres=# CREATE FUNCTION plv8max (a integer, b integer) postgres-# RETURNS integer AS $$ postgres$# if (a > b) postgres$# return a; postgres$# else postgres$# return b; postgres$# $$ LANGUAGE plv8; CREATE FUNCTION
This is resulting in following audit entry:
2016-09-15 08:10:15 EDT : [19-1] user=postgres,db=postgres,app=psql,client=[local] LOG: AUDIT: SESSION,4,1,DDL,CREATE FUNCTION,,,"CREATE FUNCTION plv8max (a integer, b integer) RETURNS integer AS $$ if (a > b) return a; else return b; $$ LANGUAGE plv8;",<not logged>
The PgAudit extension can audit not only DDLs, But SELECTs, INSERTS, UPDATES, etc. as well. We should also note that extensive auditing can cause excessive I/O in the system and hurt the overall system performance.