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? An alternative would be to log into the event.log some information (like the username, that's stored in the session) and the query source. Can you point me to some information? Thanks
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
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@n.leeuwen.net http://www.xs4all.nl/~reinoud __________________________________________________
Am Montag, den 19.12.2005, 15:43 +0100 schrieb Reinoud van Leeuwen: ....
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?
Well this \connect command really reconnecting. There is SET SESSION AUTHORIZATION but I'm not sure if it really helps. Regards Tino Wildenhain
--On 19. Dezember 2005 15:32:46 +0100 Pier Luigi Fiorini <pierluigi.fiorini@yacme.com> wrote:
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?
The short version: forget it. DA connections are tied to a particular user. Connections are persistent and shared across threads and requests. You really don't want to connect/re-connect for every request and user. You would have to implement your own connection management including connection pooling *somehow*. -aj
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Andreas Jung wrote:
--On 19. Dezember 2005 15:32:46 +0100 Pier Luigi Fiorini <pierluigi.fiorini@yacme.com> wrote:
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?
The short version: forget it. DA connections are tied to a particular user. Connections are persistent and shared across threads and requests. You really don't want to connect/re-connect for every request and user. You would have to implement your own connection management including connection pooling *somehow*.
Heh, one could create a user-specific connection and stash it in the session. ;) This would actually be a lot like what some of the more RDB-centric web frameworks do already. Tres. - -- =================================================================== Tres Seaver +1 202-558-7113 tseaver@palladion.com Palladion Software "Excellence by Design" http://palladion.com -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.1 (GNU/Linux) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFDptj2+gerLs4ltQ4RAt3NAJ93414VDh+tOibJGXENarPaai1x1gCggOD6 sRvr/l2jNeYXaTExZm2A4Wg= =zWbW -----END PGP SIGNATURE-----
Andreas Jung wrote:
--On 19. Dezember 2005 15:32:46 +0100 Pier Luigi Fiorini <pierluigi.fiorini@yacme.com> wrote:
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?
The short version: forget it. DA connections are tied to a particular user. Connections are persistent and shared across threads and requests. You really don't want to connect/re-connect for every request and user. You would have to implement your own connection management including connection pooling *somehow*.
I would also be interested in multiuser ZSQLs, though it's not my main goal and I don't expect participating on such coding. Provided there wont be thousands of users (mine number is just about 20), how about to create a pool of connections in some ZODB folder, simply identified as sqlconn_jack, sqlconn_john, sqlconn_martin, ... and teach ZSQLMethod to select appropriate connection upon request? SQL connections currently support auto-connection upon request, so a timeout disconnection would be nice added feature in this case, but for small number of users it does not seem to be necessary. I think this would be a considerable, but easily implemented, step to obtain SQL userbase binding. Is there a problem somewhere? -- \//\/\
[At 19.12.2005 18:02, Andreas Jung kindly sent the following quotation.]
--On 19. Dezember 2005 17:54:31 +0100 Vlada Macek <tuttle@bbs.cvut.cz> wrote:
Is there a problem somewhere?
The problem is likely that someone needs to write the code :-)
Indeed, we have already voted for this. :-) I'm too unexperienced in Zope coding. Just wanted to know whether my idea is sane. -- \//\/\ (Sometimes credited as 1494 F8DD 6379 4CD7 E7E3 1FC9 D750 4243 1F05 9424.)
--On 19. Dezember 2005 18:06:49 +0100 Vlada Macek <tuttle@bbs.cvut.cz> wrote:
Indeed, we have already voted for this. :-) I'm too unexperienced in Zope coding. Just wanted to know whether my idea is sane.
Better collect some money to get this feature implemented *wink* -aj
I would also be interested in multiuser ZSQLs, though it's not my main goal and I don't expect participating on such coding.
Provided there wont be thousands of users (mine number is just about 20), how about to create a pool of connections in some ZODB folder, simply identified as sqlconn_jack, sqlconn_john, sqlconn_martin, ... and teach ZSQLMethod to select appropriate connection upon request?
SQL connections currently support auto-connection upon request, so a timeout disconnection would be nice added feature in this case, but for small number of users it does not seem to be necessary.
I think this would be a considerable, but easily implemented, step to obtain SQL userbase binding.
Is there a problem somewhere? Yep, I thought to do it as well but it's not so elegant (which may not be a problem) and you cannot maintain it when users create other users because you also have to create another psql connection. -- YACME S.r.l. Via del Mobiliere, 9 40138 Bologna Tel: +39 051 538709 Fax: +39 051 532399 pierluigi.fiorini@yacme.com www.yacme.com
Pier Luigi Fiorini wrote:
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?
An alternative would be to log into the event.log some information (like the username, that's stored in the session) and the query source.
Your alternative is dead easy. zLOG (or, better, the Python logging module, for which zLOG is now a facade) is quite easy to use. You can even make your own log file to contain only such events. --jcc -- "Building Websites with Plone" http://plonebook.packtpub.com
Alle 18:42, lunedì 19 dicembre 2005, hai scritto:
Pier Luigi Fiorini wrote:
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?
An alternative would be to log into the event.log some information (like the username, that's stored in the session) and the query source.
Your alternative is dead easy. zLOG (or, better, the Python logging module, for which zLOG is now a facade) is quite easy to use. You can even make your own log file to contain only such events. I know _how_ to use zLOG. I just don't know how to log the query source. Is there some documentation about these things. I can find only documentation about making things like a simple form which is not so useful because you can learn it in some days. -- YACME S.r.l. Via del Mobiliere, 9 40138 Bologna Tel: +39 051 538709 Fax: +39 051 532399 pierluigi.fiorini@yacme.com www.yacme.com
Pier Luigi Fiorini wrote:
Alle 18:42, lunedì 19 dicembre 2005, hai scritto:
Pier Luigi Fiorini wrote:
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?
An alternative would be to log into the event.log some information (like the username, that's stored in the session) and the query source.
Your alternative is dead easy. zLOG (or, better, the Python logging module, for which zLOG is now a facade) is quite easy to use. You can even make your own log file to contain only such events.
I know _how_ to use zLOG. I just don't know how to log the query source. Is there some documentation about these things. I can find only documentation about making things like a simple form which is not so useful because you can learn it in some days.
That depends. What do you mean by "query source"? --jcc -- "Building Websites with Plone" http://plonebook.packtpub.com
participants (7)
-
Andreas Jung -
J Cameron Cooper -
Pier Luigi Fiorini -
Reinoud van Leeuwen -
Tino Wildenhain -
Tres Seaver -
Vlada Macek