Re: pgaudit - an auditing extension for PostgreSQL

On Tue, Feb 24, 2015 at 1:29 AM, David Steele <david(at)pgmasters(dot)net> wrote:
> On 2/18/15 10:25 AM, David Steele wrote:
>> On 2/18/15 6:11 AM, Fujii Masao wrote:
>>> The pg_audit doesn't log BIND parameter values when prepared statement is used.
>>> Seems this is an oversight of the patch. Or is this intentional?
>>
>> It's actually intentional - following the model I talked about in my
>> earlier emails, the idea is to log statements only. This also follows
>> on 2ndQuadrant's implementation.
>
> Unfortunately, I think it's beyond the scope of this module to log bind
> variables.

Maybe I can live with that at least in the first version.

> I'm following not only 2ndQuadrant's implementation, but
> Oracle's as well.

Oracle's audit_trail (e.g., = db, extended) can log even bind values.
Also log_statement=on in PostgreSQL also can log bind values.
Maybe we can reuse the same technique that log_statement uses.

>> Logging values is interesting, but I'm sure the user would want to
>> specify which columns to log, which I felt was beyond the scope of the
>> patch.
>>
>>> The pg_audit cannot log the statement like "SELECT 1" which doesn't access to
>>> the database object. Is this intentional? I think that there are many users who
>>> want to audit even such statement.
>>
>> I think I see how to make this work. I'll work on it for the next
>> version of the patch.
>
> This has been fixed in the v2 patch.

Thanks!

>>> Imagine the case where you call the user-defined function which executes
>>> many nested statements. In this case, pg_audit logs only top-level statement
>>> (i.e., issued directly by client) every time nested statement is executed.
>>> In fact, one call of such UDF can cause lots of *same* log messages. I think
>>> this is problematic.
>>
>> I agree - not sure how to go about addressing it, though. I've tried to
>> cut down on the verbosity of the logging in general, but of course it
>> can still be a problem.
>>
>> Using security definer and a different logging GUC for the defining role
>> might work. I'll add that to my unit tests and see what happens.
>
> That didn't work - but I didn't really expect it to.
>
> Here are two options I thought of:
>
> 1) Follow Oracle's "as session" option and only log each statement type
> against an object the first time it happens in a session. This would
> greatly reduce logging, but might be too little detail. It would
> increase the memory footprint of the module to add the tracking.
>
> 2) Only log once per call to the backend. Essentially, we would only
> log the statement you see in pg_stat_activity. This could be a good
> option because it logs what the user accesses directly, rather than
> functions, views, etc. which hopefully are already going through a
> review process and can be audited that way.
>
> Would either of those address your concerns?

Before discussing how to implement, probably we need to consider the
spec about this. For example, should we log even nested statements for
the audit purpose? If yes, how should we treat the case where
the user changes the setting so that only DDL is logged, and then
the user-defined function which internally executes DDL is called?
Since the top-level SQL (calling the function) is not the target of
audit, we should not log even the nested DDL?

Regards,

--
Fujii Masao