On Thu, Feb 26, 2015 at 1:40 PM, Alvaro Herrera
<alvherre@2ndquadrant.com> wrote:
> Fujii Masao wrote:
>> On Tue, Feb 24, 2015 at 1:29 AM, David Steele <david@pgmasters.net> wrote:
>
>> > 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.
>
> Doesn't this mean that a user could conceal malicious activity simply by
> doing a innocuous query that silences all further activity?
>
>> > 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.
>
> ... assuming the user does not create stuff on the fly behind your back.
>
>> > 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?
>
> Clearly if you log only DROP TABLE, and then the malicious user hides
> one such call inside a function that executes the drop (which is called
> via a SELECT top-level SQL), you're not going to be happy.
Yep, so what SQL should be logged in this case? Only "targeted" nested DDL?
Both top and nested ones? Seems the later is better to me.
What about the case where the function A calls the function B executing DDL?
Every ancestor SQLs of the "targeted" DDL should be logged? Probably yes.
Regards,
--
Fujii Masao
