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.
--
Álvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services