pgaudit - an auditing extension for PostgreSQL

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.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
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, 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