Achilleas Mantzios, 31.03.2011 09:29:
> Currently most people define an app server (usually super) user to access the database,
> and the connections in the pool are created with this db user connecting to the db.
> Numerous times I encountered situations where I would like to link entries
> from the web/app server access log (where app server usernames are logged)
> with entries from the postgresql log, where only the app server db user is logged.
> In order to to this corelation, or even worse to *prove* that app server user (lets call him Uapp)
> caused or did not cause this erroneous/malicious DB statement, one has to grep for SQL stmts
> through the source, then idntify some apps as candidated for having been run,
> and then try to find from the web/app server access logs who those people are.
> This does not work nice and is not accurate.
>
If you are on 9.0 and have control over the connection initialization in the pool, then using 9.0's "application_name"
mightbe a solution to this.
If you can configure the pool to run
SET application_name = 'app_user_name'
when a connection is taken out of the pool, then this name can be part of the log message in the PostgreSQL logfile.
I don't think the standard pools that are available in Java (C3P0 or DBCP) can use a "dynamic" statement like this (as
itrequires injecting a value that changes during time), but maybe there is a way...
Regards
Thomas