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