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
