Re: pgaudit - an auditing extension for PostgreSQL

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. I'm following not only 2ndQuadrant's implementation, but
Oracle's as well.

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

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

--
- David Steele
david(at)pgmasters(dot)net