[Zope] ZSQL with different user

[Reinoud van Leeuwen]

On Mon, Dec 19, 2005 at 03:39:51PM +0100, Tino Wildenhain wrote:
> Pier Luigi Fiorini schrieb:
> >Hello,
> >I'm developing a Zope application that uses a PostgreSQL connection and 
> >several ZSQL objects.
> >People should log in using a Postgres user and ZSQL object should be 
> >executed by the user that's logged in. Multiple people can be logged at 
> >the same time.
> >Unfortunately it is not possible because the same Postgres connection is 
> >used by all the ZSQL object. Is there a way to change the user executing a 
> >ZSQL query?
> 
> Well good news and bad news...
> Good news first:
> 
> yes it can be done
> 
> Bad news:
> 
> you would have to code it ;)
> 
> You could base on the database adapter you currently
> have but organize your connection pool on a per
> user basis - so you need a connection with
> a user-folder as well (Interesting
> if you could subclass Userfolder and PsycopgDA...)
> so when a user logs in you look in the connection
> pool and/or authorize the user and use an idle/new
> connection for this user when ZSQL methods are called.
> 
> Interesting project but not so easy.
> 
> Also in worst case you could end up having
> 
> zope-threads X users connection to your database.
> 
> And connection setup is somewhat expensive,
> so if your pool is too small performance will suffer.

The postgresql commandline tool psql has a command /user to change the 
details of the current connection. Wouldn't it be easier to use a command 
like that at the beginning of each database request? 

(I think that would still need some hacking in Userfolder and/or 
PsycopgDA, or even in Psyco itself...)


-- 
__________________________________________________
"Nothing is as subjective as reality"
Reinoud van Leeuwen    reinoud.v at n.leeuwen.net
http://www.xs4all.nl/~reinoud
__________________________________________________