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

Hm. That would work, but there are so many data-altering queries, it's a
lot of work :/

I'm dreaming of a simple proxy that securely holds a pool of
su-connections and uses:

SET SESSION AUTHORIZATION $foo;
$query;
RESET SESSION AUTHORIZATION;

It would just have to filter queries that contain "SESSION
AUTHORIZATION" to prevent sql injection..

I wonder why pgPool doesn't work that way..


On 02.05.2005 15:23, Sean Davis wrote:
> 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
>>
>
>
>
> ---------------------------(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
>