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


---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

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?modu...8357#384835 7

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
>



---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend