On Mon, 2005-05-02 at 14:45 +0200, Hannes Dorbath wrote: > 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 ;/ > Have you measured the real gain in using persistent connections at all? In my experience, it's just a CPU vs RAM tradeoff. Before you go thru the pain of setting up a weird authentication mechanism, try and consider whether you really need persistent connections. Search the lists, it has been discussed in the past. I remember of this thread: http://archives.postgresql.org/pgsql-php/2005-02/msg00009.php There may be others, too. .TM. -- ____/ ____/ / / / / Marco Colombo ___/ ___ / / Technical Manager / / / ESI s.r.l. _____/ _____/ _/ Colombo@xxxxxx ---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to increase your free space map settings
