Re: allowing for control over SET ROLE

On Mon, Sep 12, 2022 at 11:41 AM Peter Eisentraut
<peter(dot)eisentraut(at)enterprisedb(dot)com> wrote:
> I think this is because we have (erroneously) make SET ROLE to be the
> same as SET SESSION AUTHORIZATION. If those two were separate (i.e.,
> there is a current user and a separate current role, as in the SQL
> standard), then this would be more straightforward.
>
> I don't know if it's possible to untangle that at this point.

I think that it already works as you describe:

rhaas=# create role foo;
CREATE ROLE
rhaas=# create role bar;
CREATE ROLE
rhaas=# grant bar to foo;
GRANT ROLE
rhaas=# set session authorization foo;
SET
rhaas=> set role bar;
SET
rhaas=> select current_user;
current_user
--------------
bar
(1 row)

rhaas=> select session_user;
session_user
--------------
foo
(1 row)

There may well be problems here, but this example shows that the
current_user and session_user concepts are different in PostgreSQL.
It's also true that the privileges required to execute the commands
are different: SET SESSION AUTHORIZATION requires that the session
user is a superuser, and SET ROLE requires that the identity
established via SET SESSION AUTHORIZATION has the target role granted
to it.

--
Robert Haas
EDB: http://www.enterprisedb.com