Re: Auditing extension for PostgreSQL (Take 2)

On 6 April 2015 at 16:34, Peter Eisentraut <peter_e(at)gmx(dot)net> wrote:
> On 2/14/15 9:34 PM, David Steele wrote:
>> The patch I've attached satisfies the requirements that I've had from
>> customers in the past.
>
> What I'm missing is a more precise description/documentation of what
> those requirements might be.
>
> "Audit" is a "big word". It might imply regulatory or standards
> compliance on some level. We already have ways to log everything. If
> customers want "auditing" instead, they will hopefully have a precise
> requirements set, and we need a way to map that to a system
> configuration. I don't think "we need auditing" -> "oh there's this
> pg_audit thing, and it has a bunch of settings you can play with" is
> going to be enough of a workflow.

Yes, this needs better documentation, as does RLS.

> For starters, I would consider the
> textual server log to be potentially lossy in many circumstances, so
> there would need to be additional information on how to configure that
> to be robust.

It was intended to be used with a log filter plugin, to allow it to be
routed wherever is considered safe.

> (Also, more accurately, this is an "audit trail", not an "audit". An
> audit is an examination of a system, not a record of interactions with a
> system. An audit trail might be useful for an audit.)

No problem with calling it pg_audit_trail

> I see value in what you call object auditing, which is something you
> can't easily do at the moment. But what you call session auditing seems
> hardly distinct from statement logging. If we enhance log_statements a
> little bit, there will not be a need for an extra module to do almost
> the same thing.

Agreed: generating one line per statement isn't much different from
log_statements.

The earlier version of pg_audit generated different output.
Specifically, it allowed you to generate output for each object
tracked; one line per object.

The present version can trigger an audit trail event for a statement,
without tracking the object that was being audited. This prevents you
from searching for "all SQL that touches table X", i.e. we know the
statements were generated, but not which ones they were. IMHO that
makes the resulting audit trail unusable for auditing purposes. I
would like to see that functionality put back before it gets
committed, if that occurs.

--
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, RemoteDBA, Training & Services