I'm dreaming of a simple proxy that securely holds a pool of su-connections and uses:
SET SESSION AUTHORIZATION $foo; $query; RESET SESSION AUTHORIZATION;
It would just have to filter queries that contain "SESSION AUTHORIZATION" to prevent sql injection..
I wonder why pgPool doesn't work that way..
On 02.05.2005 15:23, Sean Davis wrote:
I have only a few connections, but I just connect with the equivalent of your "apache" user. My database is pretty much query-only with a few exceptions that are not "sensitive". But for you, could you just write a stored function to do the transaction and write the audit trail for data-altering queries? That way, the application can still provide a "username" to the function for the audit trail and the audit trail can be made "safe" within the database framework (ie., it will only be written if the transaction succeeds). Alternatively, this could be done on the client side by doing all data changes and auditing within the same transaction block, but having all the code on the server side makes altering the schema later easier (?). This should be a balance between having cached connections (VERY important for any even slightly-loaded system, in my very limited experience) and having robust auditing.
Sean
----- Original Message ----- From: "Hannes Dorbath" <light@xxxxxxxxxxxxxxxxxxxx>
To: <pgsql-general@xxxxxxxxxxxxxx>
Sent: Monday, May 02, 2005 8:45 AM
Subject: Persistent Connections in Webserver Environment
Hi,
as the subject says I need some advice on setting up connection handling to PG in a webserver environment. It's a typical dual Xeon FreeBSD box running Apache2 with mod_php5 and PG 8. About 20 different applications (ecommerce systems) will be running on this box. Each app resides in it's own schema inside a single database. As far as I understand persistent connections from apache processes can only be reused if the authentication information of the allready existing connection is the same. So in case an apache process holds a persistent connection to database "test", auth'ed with username "user1" and another app wants to connect as "user2" the connection can't be reused and a new one will be spawned.
So what we are doing atm is telling all apps to use the user "apache", grant access for this user to all schemas and fire "SET search_path TO <app_schema>;" at the startup of each app / script. It works, but I really would like to have an dedicated user for each app / schema for security reasons.
The next better idea I came up with was to fire "SET SESSION AUTHORIZATION TO <user>;" at each app / script startup, but for this to work I would need to initially connect as superuser - and I really dislike the idea of having a webserver connecting as superuser :/
Any ideas? I can't be the first person on earth with that problem ;/
Thanks in advance
---------------------------(end of broadcast)--------------------------- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to majordomo@xxxxxxxxxxxxxx so that your message can get through to the mailing list cleanly
---------------------------(end of broadcast)--------------------------- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to majordomo@xxxxxxxxxxxxxx so that your message can get through to the mailing list cleanly
---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
