Thread: Connection reauthentication in jboss datasource connection pool
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
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
Hi, thanks for your reply. > 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. Thanks for the pointer, I'd actually just come from those forums... > 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. > Agreed, if you want the connection to be set up with both the application users username and password... The scheme I was thinking of was to create the connections using a (probably "hobbled") postgresql superuser then when a connection is taken out of the pool, do a SET SESSION AUTHORISATION to the current user. With this approach, I can define some views involving CURENT_USER which will limit what the "real" user can see. If I make those views "updateable", then I can do the hibernate mapping on the views so I can control what people are updating and inserting as well... I found a couple of items on the jboss site which relate to this: http://www.jboss.org/index.html?module=bb&op=viewtopic&p=3848357#3848357 http://jira.jboss.com/jira/browse/JBAS-1429 Upshot of these is that it doesn't look like the connection pool in jboss supports this "reauthentication" yet and its not completely trivial to implement. I think I need to go and have a play with this :) Cheers, Andy > 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 > > > ---------------------------(end of > broadcast)--------------------------- > TIP 5: don't forget to increase your free space map settings >