Re: Connection reauthentication in jboss datasource connection pool - Mailing list pgsql-jdbc

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
>