Re: pgaudit - an auditing extension for PostgreSQL

Ian,

* Ian Barwick (ian@2ndquadrant.com) wrote:
> 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.

Neat stuff.

> Why auditing?

Yeah, we really need to improve here.  I've been hoping to make progress
on this and it looks like I'll finally have some time to.

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

While certainly a good approach to minimize the changes needed to the
backend, I'd really like to see us be able to, say, log to a table and
have more fine-grained control over what is logged, without needing an
extension.

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

Yeah, that's definitely an issue for any kind of real auditing.

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

Interesting- I'm a bit on the fence about this one.  Perhaps you can
elaborate on the use-case for this?

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

This is something I've been mulling over for a couple of years (you can
see notes from the discussion at the 2011 hacker meeting on the wiki
about how we might change our logging system to allow for better
filtering).

> 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().

Using the existing logging collector will almost certainly be a
contention point- we've seen that before.  I've had thoughts about
an option to log to individual files from each backend (perhaps based
on that backend's position in the proc table) or directly from each
backend to a remote service (eg: rabbitMQ/AMQP or something).

Regarding background worker processes, a thought that's been kicked
around a bit is to actually change our existing logging collector to
be a background worker (or perhaps be able to have multiple?) which
is fed from a DSM queue and then logs to a file (or maybe files), or
a table or something else.

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

Yeah, that's another interesting challenge.  This kind of auditing is
often about specific information (and therefore specific objects) and
it'd be ideal to have that set up and managed alongside the table
definition.  Having the auditing done in core instead of through an
extension would make this easier to address though.
Thanks,
    Stephen