pgaudit - an auditing extension for PostgreSQL - Mailing list pgsql-hackers

Hi

Here is an initial version of an auditing extension for Postgres to
generate log output suitable for compiling a comprehensive audit trail
of database operations.


Why auditing?

Various laws and regulations (HIPAA, PCI DSS, EU Data Protection
Directive etc.) as well as internal business requirements mandate
auditing at database level. While many proprietary and some open
source databases offer auditing facilities, Postgres does not currently
provide any kind of auditing feature. Availability of such a feature
will assist PostgreSQL's adoption in key sectors such as finance
and health.


About pgaudit

pgaudit uses Event Triggers to log unambiguous representation of DDL,
as well as a combination of executor and utility hooks for other
commands (DML, including SELECT, as well as other utility commands):
    https://github.com/2ndQuadrant/pgaudit

To provide fully-featured auditing capability, pgaudit exploits the
capabilities of the new Event Trigger code, which 2ndQuadrant will be
submitting to core Postgres. Currently that means you'll have to
build against an enhanced version of Postgres [1]. However the
intention is that pgaudit will be both a useful module now (it is designed
to compile against 9.3 and 9.4), but  will also serve as a demonstration
of features proposed for 9.5.

[1] "deparse" branch of git://git.postgresql.org/git/2ndquadrant_bdr.git


Here's some example log output:

LOG:  [AUDIT],2014-04-30 17:13:55.202854+09,auditdb,ianb,ianb,DEFINITION,CREATE TABLE,TABLE,public.x,CREATE  TABLE
public.x(a pg_catalog.int4   , b pg_catalog.int4   )   WITH (oids=OFF)
 
LOG:  [AUDIT],2014-04-30 17:14:06.548923+09,auditdb,ianb,ianb,WRITE,INSERT,TABLE,public.x,INSERT INTO x VALUES(1,1);
LOG:  [AUDIT],2014-04-30 17:14:21.221879+09,auditdb,ianb,ianb,READ,SELECT,TABLE,public.x,SELECT * FROM x;
LOG:  [AUDIT],2014-04-30 17:15:25.620213+09,auditdb,ianb,ianb,READ,SELECT,VIEW,public.v_x,SELECT * from v_x;
LOG:  [AUDIT],2014-04-30 17:15:25.620262+09,auditdb,ianb,ianb,READ,SELECT,TABLE,public.x,SELECT * from v_x;
LOG:  [AUDIT],2014-04-30 17:16:00.849868+09,auditdb,ianb,ianb,WRITE,UPDATE,TABLE,public.x,UPDATE x SET a=a+1;
LOG:  [AUDIT],2014-04-30 17:16:18.291452+09,auditdb,ianb,ianb,ADMIN,VACUUM,,,VACUUM x;
LOG:  [AUDIT],2014-04-30 17:18:01.08291+09,auditdb,ianb,ianb,DEFINITION,CREATE FUNCTION,FUNCTION,public.func_x(),CREATE
FUNCTION public.func_x() RETURNS  pg_catalog.int4 LANGUAGE sql  VOLATILE  CALLED ON NULL INPUT SECURITY INVOKER COST
100.000000  AS $dprs_$SELECT a FROM x LIMIT 1;$dprs_$
 
LOG:  [AUDIT],2014-04-30 17:18:09.694755+09,auditdb,ianb,ianb,FUNCTION,EXECUTE,FUNCTION,public.func_x,SELECT * FROM
func_x();
LOG:  [AUDIT],2014-04-30 17:18:09.694865+09,auditdb,ianb,ianb,READ,SELECT,TABLE,public.x,SELECT * FROM func_x();
LOG:  [AUDIT],2014-04-30 17:18:33.703007+09,auditdb,ianb,ianb,WRITE,DELETE,VIEW,public.v_x,DELETE FROM v_x;
LOG:  [AUDIT],2014-04-30 17:18:33.703051+09,auditdb,ianb,ianb,WRITE,DELETE,TABLE,public.x,DELETE FROM v_x;
LOG:  [AUDIT],2014-04-30 17:19:54.811244+09,auditdb,ianb,ianb,ADMIN,SET,,,set role ams;
LOG:  [AUDIT],2014-04-30 17:19:57.039979+09,auditdb,ianb,ams,WRITE,INSERT,VIEW,public.v_x,INSERT INTO v_x VALUES(1,2);
LOG:  [AUDIT],2014-04-30 17:19:57.040014+09,auditdb,ianb,ams,WRITE,INSERT,TABLE,public.x,INSERT INTO v_x VALUES(1,2);
LOG:  [AUDIT],2014-04-30 17:20:02.059415+09,auditdb,ianb,ams,ADMIN,SET,,,SET role ianb;
LOG:  [AUDIT],2014-04-30 17:20:09.840261+09,auditdb,ianb,ianb,DEFINITION,ALTER TABLE,TABLE,public.x,ALTER TABLE
public.xADD COLUMN c pg_catalog.int4
 
LOG:  [AUDIT],2014-04-30 17:23:58.920342+09,auditdb,ianb,ianb,ADMIN,ALTER ROLE,,,ALTER USER ams SET search_path =
'foo';


How is this different to log_statement='all'?

1. pgaudit logs fully-qualified relation names, so you don't have to   wonder if "SELECT * FROM x" referred to
"public.x"or "other.x".
 

2. pgaudit creates a log entry for each affected object, so you don't   have to wonder which tables "SELECT * FROM
someview"accessed, and   it's easy to identify all accesses to a particular table.
 

3. pgaudit allows finer-grained control over what is logged. Commands   are classified into read, write, etc. and
loggingfor these classes   can be individually enabled and disabled (either via pgaudit.log in   postgresql.conf, or as
aper-database or per-user setting).
 


Here's a quick overview of how it works:

0. In 9.3 and 9.4, we build without USE_DEPARSE_FUNCTIONS. In the   deparse branch (which I'll call 9.5 for
convenience),we build   with USE_DEPARSE_FUNCTIONS (set in the Makefile).
 

1. In 9.5, we create a ddl_command_end event trigger and use   pg_event_trigger_{get_creation_commands,expand_command}
tolog   a deparsed representation of any DDL commands supported by event   triggers.
 

2. We always use an sql_drop event trigger to log DROP commands, but   once 9.5 includes
pg_event_trigger_get_deletion_commands()or some   equivalent, we'll use that functionality as well.
 

3. We use a ProcessUtility_hook to deal with other utility commands that   are not handled by #1 and #2. For example,
DROPon global objects in   all versions and all non-DROP DDL for 9.3 or 9.4.
 

4. We use an ExecutorCheckPerms_hook to log SELECT and DML commands.

5. We use an object_access_hook and OAT_POST_CREATE/ALTER to handle   CREATE/ALTER on relations in 9.3/9.4. We use
OAT_FUNCTION_EXECUTE  to log (non-catalog) function execution.
 


Planned future improvements include:

1. Additional logging facilities, including to a separate audit   log file and to syslog, and potentially logging to a
table  (possibly via a bgworker process). Currently output is simply   emitted to the server log via ereport().
 

2. To implement per-object auditing configuration, it would be nice to use   extensible reloptions (or an equivalent
mechanism)

Details such as output format, command classification etc. are provisional
and open to further discussion.


Authors: Ian Barwick, Abhijit Menon-Sen (2ndQuadrant).
See README.md for more details.

We welcome your feedback and suggestions.


Ian Barwick

The research leading to these results has received funding from the
European Union's Seventh Framework Programme (FP7/2007-2013) under
grant agreement n° 318633. http://axleproject.eu

--  Ian Barwick                   http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training &
Services