Thread: RESET ROLE and search_path, Connection pool
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
On Fri, Dec 3, 2010 at 5:13 AM, Marc Mamin <M.Mamin@intershop.de> wrote:
Tangential to your question, but important:
Obviously each "user" could use RESET ROLE and become the super user. This means that every piece of code that uses this pool needs to have security appropriate for code using the super user. i.e. "Whatever, it's just using a read-only role, nothing bad can happen" is no longer a valid argument (if it ever was).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.
Tangential to your question, but important:
Do you have that much faith / trust in every "user"?
* "user" in quotes because I'm guessing you are referring to different portions of your application / application suite and hopefully not individual persons.
Derrick
Hello, > Obviously each "user" could use RESET ROLE and become the super user. Yes, this is a point not to forget, but isn't an issue in our case. best regards, Marc Mamin From: Derrick Rice [mailto:derrick.rice@gmail.com] Sent: Samstag, 4. Dezember 2010 00:21 To: Marc Mamin Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] RESET ROLE and search_path, Connection pool On Fri, Dec 3, 2010 at 5:13 AM, Marc Mamin <M.Mamin@intershop.de> wrote: 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. Tangential to your question, but important: Obviously each "user" could use RESET ROLE and become the super user. This means that every piece of code that uses thispool needs to have security appropriate for code using the super user. i.e. "Whatever, it's just using a read-only role,nothing bad can happen" is no longer a valid argument (if it ever was). Do you have that much faith / trust in every "user"? * "user" in quotes because I'm guessing you are referring to different portions of your application / application suite andhopefully not individual persons. Derrick