Re: pgaudit - an auditing extension for PostgreSQL

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?

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

--
Álvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services