PGAudit

PGAudit is an open source audit log generator useful for detailed auditing of usage of a database for financial, medical, or privacy data-related reporting requirements. It collects audit events from various sources and logs them in CSV format including a timestamp, user information, details of objects affected (if any), and the fully-qualified command text (when available). All DDL, DML (including SELECT), and utility commands are supported.

These are categorized as described below. Audit logging for each group of commands may be enabled or disabled by the superuser. Once enabled, however, audit logging may not be disabled by a user.

Settings

  1. Entire System: settings can be specified globally in postgresql.conf file or by using ALTER SYTEM.. SET
  2. Database level: at database level can be set by using ALTER DATABASE.. SET
  3. Role level - At role level can be set by using ALTER ROLE .. SET

Setup

Instal pgAudit from the command line as follows:

pgc update
pgc install pgaudit

Once the module is installed, edit postgresql.conf and set:

vi postgresql.conf
shared_preload_libraries='pgaudit'

Then restart the server, connect to psql and run:

CREATE EXTENSION pgaudit;

Session Audit Logging

Session audit logging provides detailed logs of all statements executed by a user in the backend.

Configuration

Session logging is enabled with the pgaudit.log setting

pgaudit.log = 'read, write, user'

pgaudit.log may be set to an empty string or "none" to disable logging, or to any combination of the following logging classes:

read, write, privilege, user, definition, config, admin, function

Verify the PGAudit extension is enabled.

 postgres=# show shared_preload_libraries;
 shared_preload_libraries
--------------------------
 pgaudit
(1 row)

Example 1

Here we are trying to set pgaudit.log for "read ,ddl"

postgres=# set pgaudit.log = 'read, ddl';
SET

postgres=# show pgaudit.log;
pgaudit.log
-------------
read, ddl
(1 row)

 
postgres=# create table industry(id int,name text);
CREATE TABLE

postgres=# insert into industry (id,name)values (11,'polo');
INSERT 0 1

postgres=# select * from industry;
 id | name
----+------
 11 | polo
 

Logs can be traced in postgres log and to verify logs:

postgres= show   
    log_directory;
    log_directory                  
------------------------------------------------
 /Users/vagrant/Downloads/bigsql/data/logs/pg96
 

Here we can look into logged output:

2016-07-21 14:48:54 PDT [33961]: [5-1] 
 user=postgres,db=postgres,app=psql,client=[local] LOG:  AUDIT: 
 SESSION,3,1,DDL,CREATE TABLE,TABLE,public.industry,"create table 
 industry(id int,name text);",<not logged> 

user=postgres,db=postgres,app=psql,client=[local] LOG:  AUDIT: 
SESSION,4,1,READ,SELECT,,,select * from industry;,<not logged>
 

Example 2

In this example, we are setting pgaudit.log for "write"

postgres=# set pgaudit.log = 'write';
SET
postgres=# show pgaudit.log;
 pgaudit.log
-------------
 write
(1 row)

postgres=# insert into industry(id,name)values (44,'henry');
INSERT 0 1
postgres=# select * from industry;
 id | name 
----+-------
 11 | polo
 44 | henry
(2 rows)

2016-07-21 15:32:09 PDT [43742]: [1-1] 
user=postgres,db=postgres,app=psql,client=[local] LOG:  AUDIT: 
SESSION,1,1,WRITE,INSERT,,,"insert into industry(id,name)values 
(44,'henry');",<not logged>

Object Audit Logging

Object audit logging logs statements that affect a particular relation. Only SELECT, INSERT, UPDATE and DELETE commands are supported. TRUNCATE is not included in object audit logging. Object audit logging is intended to be a finer-grained replacement for pgaudit.log = 'read, write'.

Configuration

Object-level audit logging is implemented via the roles system. The pgaudit.role setting defines the role that will be used for audit logging. A relation (TABLE, VIEW, etc.) will be audit logged when the audit role has permissions.

Set pgaudit.role to auditor and grant SELECT and DELETE privileges on the table. Any SELECT or DELETE statements on the table will now be logged:

First we need to create a role:

postgres=# create role auditor;
CREATE ROLE

We also need to set:

postgres=# set pgaudit.role = 'auditor';
SET 

Now we have two tables, test1 and test2, with same data and structure. Next we will grant access to "auditor role" for just test1.

postgres=# grant select,insert,update,delete on test1 to auditor;
GRANT

So if we try the following:

select * from test1;
postgres=# select * from test1;
 id |  name  
----+---------
 33 | patrick
(1 row)
select * from test2;
postgres=# select * from test2;
 id |  name  
----+---------
 33 | patrick
(1 row)If we look at AUDIT logs output:
LOG:  AUDIT: OBJECT,4,1,READ,SELECT,TABLE,public.test1,select * from 
test1;,<not logged>

Query to test2 was not logged because auditor role has access granted only to test1.

Now we can set per column in a table. For table test2:

  postgres=# select * from test2;
 id |  name   
----+---------
 33 | patrick
 

Now grant select (some_text) on test2 for auditor:

postgres=# grant select (name) on test2 to auditor;
GRANT

postgres=# select name from test2;
  name   
---------
 patrick
(1 row)

postgres=# select id from test2;
 id 
----
 33

postgres=# select * from test2;
 id |  name   
----+---------
 33 | patrick
(1 row)

Here is the output, only specified column is logged.

2016-07-21 21:17:43 PDT [502]: [3-1] 
user=postgres,db=postgres,app=psql,client=[local] LOG:  AUDIT: 
OBJECT,1,1,READ,SELECT,TABLE,public.test2,select name from test2;,<not logged>

2016-07-21 21:18:08 PDT [502]: [4-1] 
user=postgres,db=postgres,app=psql,client=[local] LOG:  AUDIT: 
OBJECT,2,1,READ,SELECT,TABLE,public.test2,select * from test2;,<not logged>

Learn more

Get more information about PGAudit here