Hi,
I'm not sure if this is the right forum for this question, please let me
know if it isn't :)
I'm working in a jboss 4.0.x + hibernate 3.1 + PostgreSQL 8.1 environment
that's running a web application.
I'm using standard J2EE form based authentication on my servlets to log-in
my users.
I have a standard Jboss data source that provides the application with a
jdbc connection pool, configured to connect to PostgreSQL.
Everything works fine if I use a single username and password for the
connections back to PostgreSQL, configured in the datasource description
file...
I'd like to propogate the user back to PostgreSQL, such that the value of
CURRENT_USER has the username of the logged in user.
This can be achieved using a "Caller Identity"
application-policy/login-module and a corisponding security-domain entry.
However, this approach (I belive) creates a sub-pool per Subject - which
ends up using lots of connections back to the database :(
I think that there should be a way to use connection reauthentication to
take a connection from the pool, set up the current user information, use
the connection then return it to the pool where it could be used by any
other user (so that I still get the benefits of pooling across all users).
Has anyone tried to do this (or anything else that acheives the same
effect)?
Thanks
Andy