Re: Persistent Connections in Webserver Environment

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(at)ESI(dot)it