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
