Re: pgaudit - an auditing extension for PostgreSQL

On 2/25/15 11:40 PM, Alvaro Herrera wrote:
> Fujii Masao wrote:
>> On Tue, Feb 24, 2015 at 1:29 AM, David Steele <david(at)pgmasters(dot)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?

True enough, but I thought it might be useful as an option. I tend to
lock down users pretty tightly so there's not much they can do without
somehow getting superuser access, at which point all bets are off anyway.

In the case where users are highly constrained, the idea here would be
to just keeps tabs on the sort of things they are reading/writing for
financial audits and ISO compliance.

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

Sure, but then the thing they created/modified would also be logged
somewhere earlier (assuming pg_audit.log = 'all'). It would require
some analysis to figure out what they did but I think that would be true
in many cases.

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

If the purpose of the auditing it to catch malicious activity then all
statements would need to be logged. If only top-level statements are
logged then it might be harder to detect, but it would still be logged.

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