Re: JDBC gripe list

Hello,
what i think that it would be nice to have is being able to couple users in the app server
with postgresql users.
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.

What would be ideal is to have the app/web server users get connections from the pool
authenticated against their app server users.
There are ways to do that, I have implemented this in our installation in a test system.
However, the problem is that the pool is per user, the connections are named and each pool
can have e.g. max connections.
This does not scale well, since it requires
max_connections >= MAX_CONNECTIONS_PER_USER_DEFINED_IN_APP_SERVER * MAX_APP_SERVER_CONCURRENT_USERS

From what i played a little bit with SET SESSION AUTHORIZATION, SET ROLE,
those did not seem to affect the actual user (%u) in logging in postgresql logs.
The aim was to have one common pool with "unnamed" connections and assign them to a user
after/on geting the connection.
If that was possible, and if JDBC supported that, then this problem would be easily solved.

I know pgsql JDBC driver is the last part of the puzzle, but i just thought to mention it here
since the context seems right.

Στις Friday 25 March 2011 02:35:48 ο/η Dave Cramer έγραψε:
> Just got back from pgeast. Seems everyone seems to have a gripe about
> the JDBC driver.
>
> My sense is that for basic read/write/update/delete the driver works
> just fine. However I'd like to compile a list of everyone's gripe.
>
> Dave
>

--
Achilleas Mantzios