RESET ROLE and search_path, Connection pool

From: "Marc Mamin" <M(dot)Mamin(at)intershop(dot)de>
To: <pgsql-general(at)postgresql(dot)org>
Subject: RESET ROLE and search_path, Connection pool
Date: 2010-12-03 10:13:50
Message-ID: C4DAC901169B624F933534A26ED7DF31034BB961@JENMAIL01.ad.intershop.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hello,

We are thinking about using a (java based) connection pool.
An issue is that there are many different users to connect.
My idea is to only have superuser connections in the pool
and change the connection role (with SET ROLE) each time
a user pick a connection there.

However, I'have noticed that the RESET ROLE command does not reset the
search_path to its original.
Could this be considered as bug (see below) ?

Are there other side effects to expect, or a better approach to
implement a multi user connection pool ?

(I'm also using GUC variables at some places, currently bound to the
pg_backend_pid().
Here I will have to add the current_user within the GUC name to better
isokate them)

show search_path;
"$user",public

SET ROLE 'xxx';

show search_path;
xxx,public

RESET ROLE

show search_path;
xxx,public

best regards,

Marc Mamin

Responses

Browse pgsql-general by date

  From Date Subject
Next Message James B. Byrne 2010-12-03 12:40:08 Re: PG84 and SELinux
Previous Message Devrim GÜNDÜZ 2010-12-03 09:56:21 Re: PostgreSQL 9.0 RPMs for RHEL 6 and Fedora 14 released