Re: Switching roles as an replacement of connection pooling tools

On Tue, May 31, 2016 at 3:45 AM, CN <cnliou9(at)fastmail(dot)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(at)postgresql(dot)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.