Re: Persistent Connections in Webserver Environment - Mailing list pgsql-general

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@theendofthetunnel.de>
To: <pgsql-general@postgresql.org>
Sent: Monday, May 02, 2005 8:45 AM
Subject: [GENERAL] 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@postgresql.org so that your
>      message can get through to the mailing list cleanly
>