Re: pgaudit - an auditing extension for PostgreSQL - Mailing list pgsql-hackers

On 2/25/15 10:42 PM, Fujii Masao wrote:
> On Tue, Feb 24, 2015 at 1:29 AM, David Steele <david@pgmasters.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.

I'll look at how this is done in the logging code and see if it can be
used in pg_audit.

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

I think logging nested statements should at least be an option.  And
yes, I think that nested statements should be logged even if the
top-level SQL is not (depending on configuration). The main case for
this would be DO blocks which can be run by anybody.

--
- David Steele
david@pgmasters.net