Re: Rollback issue with SET ROLE

* Tom Lane (tgl(at)sss(dot)pgh(dot)pa(dot)us) wrote:
> 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.

In this case '<prior-auth-value>' is really both the 'user' and the
'role'. Not sure that really helps though.

> Even with a fix for that, there are some related nasty cases. Consider
> BEGIN;
> SET LOCAL SESSION AUTHORIZATION foo;
> SET ROLE bar;
> 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.

To do SET SESSION AUTH, wouldn't the outer-level session user have to be
superuser, and therefore you're actually fine (considering superuser is
in all roles, etc)?

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

My expectation is that they wouldn't be allowed in a transaction, I
wasn't actually aware that we allowed SET SESSION AUTH in a transaction.
I'm not sure I see what the use-case for it would be, to do SET SESSION
AUTH you have to be superuser, aiui, and that's not something I'd expect
an application to have generally (which is where I might think that
dropping privileges inside a transaction might make sense, maybe...).

I don't see a simple answer really. GUC variable settings still call C
functions though, so I would have thought it'd be possible to track
prior settings and reset them upon commit through various variables.
Not exactly pretty and perhaps I'm misunderstanding how GUC deals with
resetting back at a commit, but I though it called the function to deal
with that. Does the function not know if it's being called for
commit or rollback? Perhaps that's it, sorry I'm not of more help with
this...

Thanks,

Stephen