Auditing in PostgreSQL using pgaudit
The PostgreSQL Audit Extension (or pgaudit) provides detailed session and/or object audit logging via the standard logging facility provided by PostgreSQL. The goal of PostgreSQL Audit to provide the tools needed to produce audit logs required to pass certain government, financial, or ISO certification audits.
We can perform the audit using the standard logging facility provided by PostgreSQL but it logs all the statements executed on the database cluster.If we want to log some specific statements like select or write operations or on specific object we can not do it using standard logging.
Advantages of Pgaudit:
1) Log specific statements like select , insert , update , delete.
2) Log statements of object level (table).
3) Log statements related to roles or privileges.
How to install pgaudit:
Follow below steps to perform the installation.
unzip or untar the downloaded file. Go to contrib location in the PostgreSQL software you have downloaded.
Rename the downloaded file to “pgaudit”.
[postgres@localhost contrib]$ mv pgaudit-1.1.1 pgaudit
[postgres@localhost contrib]$
[postgres@localhost contrib]$ cd pgaudit/
[postgres@localhost pgaudit]$
[postgres@localhost pgaudit]$ make -s check
============== creating temporary instance ==============
============== initializing database system ==============
============== starting postmaster ==============
running on port 57838 with PID 20941
============== creating database “contrib_regression” ==============
CREATE DATABASE
ALTER DATABASE
============== running regression test queries ==============
test pgaudit … ok
============== shutting down postmaster ==============
============== removing temporary instance ==============
=====================
All 1 tests passed.
=====================
[postgres@localhost pgaudit]$ make install
/bin/mkdir -p ‘/home/postgres/9.6_SOFTWARE/lib’
/bin/mkdir -p ‘/home/postgres/9.6_SOFTWARE/share/extension’
/bin/mkdir -p ‘/home/postgres/9.6_SOFTWARE/share/extension’
/bin/install -c -m 755 pgaudit.so ‘/home/postgres/9.6_SOFTWARE/lib/pgaudit.so’
/bin/install -c -m 644 ./pgaudit.control ‘/home/postgres/9.6_SOFTWARE/share/extension/’
/bin/install -c -m 644 ./pgaudit–1.1.1.sql ./pgaudit–1.0–1.1.1.sql ‘/home/postgres/9.6_SOFTWARE/share/extension/’
Configuration of pgaudit:
To use pgaudit. set the shared_preload_libraries to pgaudit.
shared_preload_libraries=pgaudit
restart postgreSQL cluster.
create extension pgaudit;
Configuration parameters for pgaudit:
pgaudit.log :
This is used to specify the statements to log.
READ : SELECT and COPY on a query or relation.
WRITE : INSERT , UPDATE , DELETE , TRUNCATE , COPY
FUNCTION : DO blocks
ROLE : GRANT , REVOKE , CREATE/ALTER/DROP ROLE
DDL : DDL that is not included with ROLE
MISC : DISCARD , FETCH , CHECKPOINT , VACUUM.
pgaudit.log_catalog : log the queries on catalog tables(pg_class, pg_tables etc).
pgaudit.log_level : Level of information need to be written in logfile. Refer to for logging level
pgaudit.log_parameter : Specifies that audit logging should include the parameters that were passed with the statement.
pgaudit.log_relation : audit log will create a separate log entry for each relation (TABLE, VIEW, etc.) referenced in a SELECT or DML statement.
pgaudit.log_statement_once : log the statement once, if any statement executed previously it will not be logged.
pgaudit.role : Specifies the master role to use for object audit logging.
Session Audit Logging :-
Session audit logging provides detailed logs of all statements executed by a user in the backend. It will log all the sessions.
Configuration :
Session logging is enabled with the pgaudit.log setting.
Example:
alter system set pgaudit.log = ‘read’;
execute some select queries.
Example logging output :
LOG: AUDIT: SESSION,5,1,READ,SELECT,TABLE,public.test,select count(*) from test join test_cp on (test.id=test_cp.id);,
LOG: AUDIT: SESSION,5,1,READ,SELECT,TABLE,public.test_cp,select count(*) from test join test_cp on (test.id=test_cp.id);,
LOG: AUDIT: SESSION,6,1,READ,SELECT,TABLE,public.test,select count(*) from test join test_cp on (test.id=test_cp.id);,
LOG: AUDIT: SESSION,6,1,READ,SELECT,TABLE,public.test_cp,select count(*) from test join test_cp on (test.id=test_cp.id);,
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-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 for the command executed or inherits the permissions from another role.
Set pgaudit.role to auditor and grant SELECT and DELETE privileges on the account table. Any SELECT or DELETE statements on the account table will now be logged:
alter system set pgaudit.role=’auditor’;
grant SELECT , insert , update , delete on test to auditor;
postgres=# select pg_reload_conf();
pg_reload_conf
—————-
t
(1 row)
postgres=# select count(*) from test;
count
——–
100001
(1 row)
postgres=# insert into test_cp values (1,’sd’);
INSERT 0 1
OUTPUT:
\2017-12-03 19:46:03.998 IST \postgres \postgres: LOG: AUDIT: OBJECT,25,1,READ,SELECT,TABLE,public.test,select count(*) from test;,
Viswamitra's blog 