Re: Connection reauthentication in jboss datasource connection pool

A Redhead wrote:
> Hi,
>
> I'm not sure if this is the right forum for this question, please let
> me know if it isn't :)

The JBoss web site has forums where you would stand a better chance of
obtaining helpful suggestions on this issue, since it really deals with
JBoss database connection pooling and is not really PG-specific. If you
think about it, connections require credentials. The only way you can
pool reusable connections is if they all use the same credentials. If
you want individual credentials, you'll need individual connections.
You should only have as many simultaneous connections as you have
simultaneous users.

This is not an uncommon problem, and it has been discussed frequently on
the JBoss forums. Search the archives there.

>
> 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
>
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org

--
Guy Rouillier