Re: Auditing extension for PostgreSQL (Take 2)

On 6 April 2015 at 20:38, David Steele <david(at)pgmasters(dot)net> wrote:

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

That discussion covers recursive SQL. That is important too, but not
what I am saying.

My point is what we log when an SQL statement covers multiple tables,
e.g. join SELECTs, or inheritance cases, views.

> That is still doable, but is covered by object-level auditing. Even
> so, multiple log entries are possible (and even likely) with session
> auditing. See my response to Peter for details.
>
>> 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.
>
> Bringing this back would be easy (it actually requires removing, not
> adding code) but I'd prefer to make it configurable.

That is my preference also. My concern was raised when it was
*removed* without confirming others agreed.

Typical questions:

Who has written to table X?
Who has read data from table Y yesterday between time1 and time2?
Has anyone accessed a table directly, rather than through a security view?

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