Re: Switching roles as an replacement of connection pooling tools

On Tue, May 31, 2016 at 10:48 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> "David G. Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> writes:
> > Is there a reason something "SET ROLE ... WITH SETTINGS" couldn't be
> > implemented?
>
> Unless there's something underlying that proposal that I'm not seeing,
> it only deals with one of the problems in this area. The security-
> related issues remain unsolved.
>
> AFAICS there's a pretty fundamental tension here around the question
> of how hard it is to revert to the original role. If it's not possible
> to do that then a connection pooler can't serially reuse a connection for
> different users, which largely defeats the point. If it is possible, how
> do you keep that from being a security hole, ie one of the pool users can
> gain privileges of another one?
>
> (And, btw, I repeat that all of this has been discussed before on our
> lists.)
>

​Understood. ​

​My motivation is to at least make SET ROLE more friendly by allowing easy
access to the pg_role_database_settings associated with it. I think the
main concern is inheritance handling (or non-handling as the case may be).
This particular complaint seems like an improvement generally even if the
larger functionality has undesirable security implications.

David J.