[Zope] ZSQL with different user

[Tino Wildenhain]

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.

Regards
Tino