The new SET ROLE code has a bit of a problem with rollback of SET
operations. For example,
regression=# create user foo;
CREATE ROLE
regression=# create role bar;
CREATE ROLE
regression=# set role bar;
SET
regression=> show role;role
------bar
(1 row)
regression=> begin;
BEGIN
regression=> set session authorization foo;
SET
regression=> show role;role
------none
(1 row)
regression=> rollback;
ROLLBACK
regression=# show role;role
------none
(1 row)
Ideally the ROLLBACK should have restored the ROLE setting that obtained
prior to BEGIN. The reason it doesn't is that the ROLLBACK effectively
does a "SET SESSION AUTHORIZATION <prior-auth-value>", and that naturally
clears the ROLE setting.
I've been chewing on this problem for a couple hours and have come to
the conclusion that it's not going to be possible to fix it without some
changes to the GUC infrastructure --- there just isn't support for
tracking changes to related but separate GUC variables.
Even with a fix for that, there are some related nasty cases. ConsiderBEGIN;SET LOCAL SESSION AUTHORIZATION foo;SET
ROLEbar;COMMIT;
The SET ROLE, being nonlocal, should persist past the COMMIT. But the
right to do "SET ROLE bar" would have been checked against foo's role
memberships. If the outer-level session user doesn't have membership
in foo, this leaves us in an illegal situation.
A possible plan B is to forbid doing either SET SESSION AUTHORIZATION
or SET ROLE inside a transaction block, so that none of these cases
arise. This restriction is sanctioned by the SQL spec. However, we've
historically allowed SET SESSION AUTHORIZATION inside a transaction
block, and in fact there's a SET LOCAL SESSION AUTHORIZATION in the
regression tests.
Thoughts?
regards, tom lane
