Auditing database changes using pgaudit

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';
$ ./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));

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 [4143]: [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);

The following line appears in the log file:

2016-09-15 08:08:10 EDT [4143]: [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>

Now let’s try creating a function, I am going to use Javascript (V8):

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;

This is resulting in following audit entry:

2016-09-15 08:10:15 EDT [4143]: [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;
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.

2 replies
  1. Hans
    Hans says:

    I can find the .zip download anymore. Will you be offering a ZIP archive for 9.6 like you did for 9.5? (

Comments are closed.