Thread: Switching roles as an replacement of connection pooling tools

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

On Tue, May 31, 2016 at 9:45 AM, CN <cnliou9@fastmail.fm> wrote:
...
> If command "SET SESSION AUTHORIZATION" is enhanced to accept two
> additional arguments
> PASSWORD <password>
...
> SET SESSION AUTHORIZATION user2 PASSWORD p2;
> SET SEARCH_PATH TO schema2,pg_category;
> Does my points make sense?

It does, but I feel it must be greatly expanded. If it does the same
as a reconnect it must accept the same kind of checks a login does (
pg_hba.conf ), which I think means putting some complicated and
somehow critical code in another place. And also it must specify how
it interacts with open transactions ( i.e. does it work like the
current command or like a reconnection ). It also means you have to
use passwords in your DDL/DML code, instead of keeping them hidden in
your connection setup code ( which makes it less atractive, for me at
least ).

> Is it eligible for feature request?

This is not for me to say, but I think it would complicate things too
much for a narrow use case ( and I doubt poolers are used for this
kind of things anyway ).

Francisco Olarte.

Hi Melvin:

On Tue, May 31, 2016 at 3:55 PM, Melvin Davidson <melvin6925@gmail.com> wrote:
> On Tue, May 31, 2016 at 3:45 AM, CN <cnliou9@fastmail.fm> wrote:
>> SET SESSION AUTHORIZATION user2 PASSWORD p2;

> Your points make no sense. You can accomplish the same with:
> GRANT ROLE user2 TO user1;

I'm not discussing wether it makes sense, but you do not acomplish the
same. In his case you cannot swict to the other role unless you know
the password for it, in yours you can. Also I suspect he wants it to
work like a login, i.e., if you have N roles and you add another one
he wants it to be like a new login user, and apps/people could have a
set of X role+password combos different for each one. I think it's a
bizarre thing, but not the same as granting some roles to other ( of
course if you have M people and N schemas ( in his example )you can
have M login roles and grant combos of N 'schema roles' to them to
achieve this, but if N is, say, a hundred, and you have a huge M, like
ten thousand, with a different combo for each one, his solution may
make sense ( I do not think such a bizarre case justifies the bug-risk
of including the feature, but it can make sense ) )

Francisco Olarte.

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 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?

I believe your thoughts are on the same line with an idea some people had about using connection pools on Java EE
environments,in a manner that does not use a generic "catch all" user, but uses the  
individual users sharing the security context from the app server.
This way one could have the benefits of the connection pool, and the benefits of fine-grained and rich PostgreSQL
securityframework, the ability to log user's activity, debug the system easier, see  
real users on pg_stat_activity, on ps(1), on top(1) etc etc.
The way we do it currently is by having personalized connection pools for pgsql in jboss. It does the job from every
aspect,except one : it sucks as far as performance is concerned. Every user is  
tied to his/her number of connections. It creates a sandbox around each user, so that a "malicious" greedy user (with
thehelp of a poorly designed app of course) can only bring down his own pool,  
while others run unaffected, but still performance suffers. The idea would be to use a common pool of connections and
assignusers on demand as they are taken from the common pool, and later also  
return them to the common pool, once closed.
Whenever I talked to PG ppl about it, they told me that redesigning the SET ROLE functionality so that it correctly
appliesall the security checks and also so that it results in reflecting the  
effective user in all references in logs, sys views, OS (ps, top, etc) etc.. was hard to do, and the convo stopped
rightthere. 

With all the new and modern cloud-inspired paradigms out there, our traditional architecture might not of much interest
anymore, still I would love to make the above happen some time. 

> Best Regards,
> CN
>


--
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt

CN <cnliou9@fastmail.fm> writes:
> 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.

I'm pretty sure this has been proposed before, and rejected before.
Two big problems with it are 1) it doesn't work for installations that
use non-password authentication methods, and 2) it leaves all the
passwords exposed in the postmaster log, if log_statement is on.

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 include
* You retain the original login role's abilities to issue SET SESSION
AUTHORIZATION, either back to itself or to a third role.
* You can also get back to the original role with DISCARD ALL.
* Any session-level settings specified for the new role with ALTER
USER SET don't get adopted.
While you could imagine that specific applications might be okay with
these things, they're pretty fatal for a general-purpose connection
pooler; the first two in particular would be unacceptable security
holes.

            regards, tom lane

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.

That being said, IMHO, I believe having a separate schema for every user is poor database design

On Tue, May 31, 2016 at 10:18 AM, Achilleas Mantzios <achill@matrix.gatewaynet.com> wrote:

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 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?

I believe your thoughts are on the same line with an idea some people had about using connection pools on Java EE environments, in a manner that does not use a generic "catch all" user, but uses the individual users sharing the security context from the app server.
This way one could have the benefits of the connection pool, and the benefits of fine-grained and rich PostgreSQL security framework, the ability to log user's activity, debug the system easier, see real users on pg_stat_activity, on ps(1), on top(1) etc etc.
The way we do it currently is by having personalized connection pools for pgsql in jboss. It does the job from every aspect, except one : it sucks as far as performance is concerned. Every user is tied to his/her number of connections. It creates a sandbox around each user, so that a "malicious" greedy user (with the help of a poorly designed app of course) can only bring down his own pool, while others run unaffected, but still performance suffers. The idea would be to use a common pool of connections and assign users on demand as they are taken from the common pool, and later also return them to the common pool, once closed.
Whenever I talked to PG ppl about it, they told me that redesigning the SET ROLE functionality so that it correctly applies all the security checks and also so that it results in reflecting the effective user in all references in logs, sys views, OS (ps, top, etc) etc.. was hard to do, and the convo stopped right there.

With all the new and modern cloud-inspired paradigms out there, our traditional architecture might not of much interest any more, still I would love to make the above happen some time.

Best Regards,
CN

--
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

--

Melvin Davidson
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

-- 
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt

"David G. Johnston" <david.g.johnston@gmail.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.)

            regards, tom lane

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 include
> * You retain the original login role's abilities to issue SET SESSION
> AUTHORIZATION, either back to itself or to a third role.
> * You can also get back to the original role with DISCARD ALL.
> * Any session-level settings specified for the new role with ALTER
> USER SET don't get adopted.
> While you could imagine that specific applications might be okay with
> these things, they're pretty fatal for a general-purpose connection
> pooler; the first two in particular would be unacceptable security
> holes.

I understand most of your viewpoints.
Perhaps I should pay more attention to general purpose connection
spoolers.


Below comments are for those who are still interested in this topic.

My intention is to minimize the number of connections and re-use them,
and to contain each role into one distinct schema.
The following test results indicate that I am unlikely to get what I
want from these commands:

SET ROLES
SET SESSION AUTHORIZATION
GRANT
REVOKE

First, connect to server with superuser.

postgres=# create role r1;
CREATE ROLE
postgres=# create schema s1;
CREATE SCHEMA
postgres=# grant all on schema s1 to r1;
GRANT
postgres=# revoke all on schema public from r1;
REVOKE
postgres=# set role r1;
SET
postgres=> create table t1 (c1 text);
CREATE TABLE
postgres=> \dn
  List of schemas
  Name  |  Owner
--------+----------
 public | postgres
 s1     | postgres
(2 rows)

postgres=> \d
       List of relations
 Schema | Name | Type  | Owner
--------+------+-------+-------
 public | t1   | table | r1
(1 row)

As shown above, table "t1" gets created in schema "public" while my
intention is to keep role "r1" and  all its objects out of schema
"public".

Sure I can issue command "SET SEARCH_PATH TO s1" before command "CREATE
table". However, the problem with such arrangement is that role "r1" can
create in schema "s1" those commands like "SET ROLE r2" or "SET
SEARCH_PATH TO s2" and therefore create or access objects not belonging
to itself once these commands get executed.

Best Regards,
CN

--
http://www.fastmail.com - IMAP accessible web-mail

That being said, IMHO, I believe having a separate schema for every user is poor database design

-- 
http://www.fastmail.com - The professional email service

"David G. Johnston" <david.g.johnston@gmail.com> 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 the session now looks as if that role was the one that
> performed the login.  However, that role is forbidden from changing its
> identity.  This removes attack vectors but also means that applications
> cannot made use of finer grained grants without the main role inheriting
> all of them.  I can see this being an acceptable trade-off in some/many
> uses.

> When the session is returned to a pool it can either be kept around waiting
> for another request by the same user or it would have to be closed and
> reestablished should the connection need to be freed up for another user.

Doesn't seem like this'd actually provide any useful functionality for a
connection pooler.  It still has to restrict any one underlying connection
to be used by only one role.  You've added more bookkeeping (because
there's a state where a connection's role is unassigned) but no
flexibility.

            regards, tom lane