Re: [GENERAL] Switching roles as an replacement of connection pooling tools

2016-06-01 Thread David G. Johnston
On Wed, Jun 1, 2016 at 9:56 AM, Tom Lane wrote: > "David G. Johnston" writes: > > ​Would a scheme whereby you basically only get to SET ROLE one time work? > > Basically the connection layer logs in and immediately SET SESSION > > AUTHORIZATION

Re: [GENERAL] Switching roles as an replacement of connection pooling tools

2016-06-01 Thread Tom Lane
"David G. Johnston" writes: > ​Would a scheme whereby you basically only get to SET ROLE one time work? > Basically the connection layer logs in and immediately SET SESSION > AUTHORIZATION AND SET ROLE [WITH SETTINGS?] to another role. For all > intents and purposes

Re: [GENERAL] Switching roles as an replacement of connection pooling tools

2016-06-01 Thread David G. Johnston
On Wed, Jun 1, 2016 at 8:59 AM, CN wrote: > On Tue, May 31, 2016, at 10:20 PM, Tom Lane wrote: > > There's also a bunch of issues having to do with the fact that the > > semantics of SET SESSION AUTHORIZATION are defined by the SQL standard > > and don't exactly match what

Re: [GENERAL] Switching roles as an replacement of connection pooling tools

2016-06-01 Thread CN
On Tue, May 31, 2016, at 10:23 PM, Melvin Davidson wrote: > That being said, IMHO, I believe having a separate schema for every > user is poor database design This is the best arrangement I can think of now for my use case: 1. Each schema holds an application independent from other schemas

Re: [GENERAL] Switching roles as an replacement of connection pooling tools

2016-06-01 Thread CN
On Tue, May 31, 2016, at 10:20 PM, Tom Lane wrote: > There's also a bunch of issues having to do with the fact that the > semantics of SET SESSION AUTHORIZATION are defined by the SQL standard > and don't exactly match what you'd want, in many cases, for "become > this other role". Some of them

Re: [GENERAL] Switching roles as an replacement of connection pooling tools

2016-05-31 Thread David G. Johnston
On Tue, May 31, 2016 at 10:48 AM, Tom Lane wrote: > "David G. Johnston" 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, >

Re: [GENERAL] Switching roles as an replacement of connection pooling tools

2016-05-31 Thread Tom Lane
"David G. Johnston" 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

Re: [GENERAL] Switching roles as an replacement of connection pooling tools

2016-05-31 Thread Achilleas Mantzios
On 31/05/2016 17:23, Melvin Davidson wrote: Actually, you do not need to SWITCH, you just need permission to change to path and gain access to all user2 privs, which is exactly what SET ROLE user2 does. There is no need for a password, since user1 is already connected to the DB. Any superuser

Re: [GENERAL] Switching roles as an replacement of connection pooling tools

2016-05-31 Thread David G. Johnston
On Tue, May 31, 2016 at 10:20 AM, Tom Lane wrote: > CN writes: > > If command "SET SESSION AUTHORIZATION" is enhanced to accept two > > additional arguments > > PASSWORD > > , then a client simply establishes only one connection to server and do > >

Re: [GENERAL] Switching roles as an replacement of connection pooling tools

2016-05-31 Thread Tom Lane
CN writes: > If command "SET SESSION AUTHORIZATION" is enhanced to accept two > additional arguments > PASSWORD > , then a client simply establishes only one connection to server and do > jobs for a million roles. I'm pretty sure this has been proposed before, and rejected

Re: [GENERAL] Switching roles as an replacement of connection pooling tools

2016-05-31 Thread Melvin Davidson
Actually, you do not need to SWITCH, you just need permission to change to path and gain access to all user2 privs, which is exactly what SET ROLE user2 does. There is no need for a password, since user1 is already connected to the DB. Any superuser can give the GRANT ROLE to any other user.

Re: [GENERAL] Switching roles as an replacement of connection pooling tools

2016-05-31 Thread Achilleas Mantzios
On 31/05/2016 10:45, CN 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

Re: [GENERAL] Switching roles as an replacement of connection pooling tools

2016-05-31 Thread Francisco Olarte
Hi Melvin: On Tue, May 31, 2016 at 3:55 PM, Melvin Davidson wrote: > On Tue, May 31, 2016 at 3:45 AM, CN wrote: >> SET SESSION AUTHORIZATION user2 PASSWORD p2; > Your points make no sense. You can accomplish the same with: > GRANT ROLE user2 TO user1;

Re: [GENERAL] Switching roles as an replacement of connection pooling tools

2016-05-31 Thread Melvin Davidson
On Tue, May 31, 2016 at 3:45 AM, CN 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: > >

[GENERAL] Switching roles as an replacement of connection pooling tools

2016-05-31 Thread CN
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