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):


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 
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 
LOG:  [AUDIT],2014-04-30 
17:15:25.620262+09,auditdb,ianb,ianb,READ,SELECT,TABLE,public.x,SELECT * from 
LOG:  [AUDIT],2014-04-30 
17:16:00.849868+09,auditdb,ianb,ianb,WRITE,UPDATE,TABLE,public.x,UPDATE x SET 
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  
COST 100.000000   AS $dprs_$SELECT a FROM x LIMIT 1;$dprs_$
LOG:  [AUDIT],2014-04-30 
 * FROM func_x();
LOG:  [AUDIT],2014-04-30 
17:18:09.694865+09,auditdb,ianb,ianb,READ,SELECT,TABLE,public.x,SELECT * FROM 
LOG:  [AUDIT],2014-04-30 
17:18:33.703007+09,auditdb,ianb,ianb,WRITE,DELETE,VIEW,public.v_x,DELETE FROM 
LOG:  [AUDIT],2014-04-30 
17:18:33.703051+09,auditdb,ianb,ianb,WRITE,DELETE,TABLE,public.x,DELETE FROM 
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 
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.x ADD 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 logging for these classes
   can be individually enabled and disabled (either via pgaudit.log in
   postgresql.conf, or as a per-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} to log
   a deparsed representation of any DDL commands supported by event

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, DROP on 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

Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:

Reply via email to