Switching roles as an replacement of connection pooling tools

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.

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

On Tue, May 31, 2016 at 3:45 AM, CN <cnliou9@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.

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.

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

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

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

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

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.

On Tue, May 31, 2016 at 10:20 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

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.

* 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.

Is there a reason something "SET ROLE ... WITH SETTINGS" couldn't be
implemented?

David J.​

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 can give the GRANT ROLE to any other user.

Still, PgSQL logs report the original user everywhere. Not useful for auditing, debugging, etc

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

I agree about this, there are much better ways to utilize schemata.

On Tue, May 31, 2016 at 10:18 AM, Achilleas Mantzios <achill@matrix.gatewaynet.com <mailto: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 <mailto: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

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

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

"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.)

​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.

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

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

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
within the same database.
2. Each schema is fully controlled by a role who is responsible for the
development of that application.

Best Regards,
CN

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

On Wed, Jun 1, 2016 at 8:59 AM, CN <cnliou9@fastmail.fm> 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 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.

​[...]​

First, connect to server with superuser.
​[...]

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.

​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.

You'd still have to make sure that the user that can invoke arbitrary SQL
commands can only get locked sessions from some central authority. This
seems problematic. In most co-tenant setups the tenants are simply
forbidden from executing arbitrary SQL and the SET ROLE is more for utility
than security. You are trusting that the piece of software that can
execute SQL.

​David J.​

"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

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

On Wed, Jun 1, 2016 at 9:56 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

"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.

Y
​ou basically delegate authentication for users to the pooler. Only the
pooler needs to be setup with credentials and given access via
pg_hba.conf. Though pg_hba isn't really a problem, you'd just allow
everyone in from the same machine.​ So mostly its about not have
credentials and giving the pooler some level of play with managing
resources.

​Every connection would have a role assigned just like it does today. The
difference is that now certain roles will have an added attribute
forbidding them from being SET ROLEd away from. I'm sure there is a lot
more to it than that but the concept of a one-way switch should remove many
of the hazards present today. The point of the password was to continue to
allow multiple-switching - but securely. That seems to be much more
complicated setup to implement and so the question should be asked whether
that capability is even necessary.

I'll admit that the trade-offs are not appealing for large scale but
resource constrained setups but I suspect for those setups sharding is
going to end up being the area of attention; not delegation/proxy.

David J.