Re: Switching roles as an replacement of connection pooling tools

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