On Tue, May 31, 2016 at 3:45 AM, CN <cnli...@fastmail.fm> wrote: > I have a feeling that slight enhancement to commands "SET ROLE" or "SET > SESSION AUTHORIZATION" can obsolete and outperform external connection > pooling tools in some use cases. > > Assume we are in the following situation: > > - There are a million schemas each owned by a distinct role. > - Every role is not allowed to access any other schema except its own. > > If command "SET SESSION AUTHORIZATION" is enhanced to accept two > additional arguments > > PASSWORD <password> > > , then a client simply establishes only one connection to server and do > jobs for a million roles. > > Say I want to gain full access to "schema2", I simply issue these two > commands > > SET SESSION AUTHORIZATION user2 PASSWORD p2; > SET SEARCH_PATH TO schema2,pg_category; > > , where "p2" is the password associated with role "user2". > > If the current role is superuser "postgres" and it wants to downgrade > itself to role "user3", then it simply sends these commands: > > SET SESSION AUTHORIZATION user3; > SET SEARCH_PATH TO schema3,pg_category; > > Does my points make sense? > Is it eligible for feature request? > > Best Regards, > CN > > -- > http://www.fastmail.com - Accessible with your email software > or over the web > > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >
Your points make no sense. You can accomplish the same with: GRANT ROLE user2 TO user1; Then user2 simply does SET ROLE user2; SET SEARCH_PATH TO schema2,pg_category; No need to reconnect. This has been available in PostgreSQL since 8.1 -- *Melvin Davidson* I reserve the right to fantasize. Whether or not you wish to share my fantasy is entirely up to you.