Re: Rollback issue with SET ROLE
От | Tom Lane |
---|---|
Тема | Re: Rollback issue with SET ROLE |
Дата | |
Msg-id | 3925.1122388754@sss.pgh.pa.us обсуждение исходный текст |
Ответ на | Re: Rollback issue with SET ROLE (Stephen Frost <sfrost@snowman.net>) |
Список | pgsql-hackers |
Stephen Frost <sfrost@snowman.net> writes: > * Tom Lane (tgl@sss.pgh.pa.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. Yeah, the solutions I was looking at involved various combinations of storing both values in one or both of the GUC variables. They all seemed pretty messy and fragile 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)? Hmm, true, but that doesn't mean you're out of the woods. If you fix the other problem by making AUTH and ROLE be effectively a single variable, then what will happen here is that SET ROLE will set the variable's value as foo/bar, and then that value will persist past COMMIT, leaving you with the wrong AUTH setting at the outer level. >> 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, Yeah. I actually put in code to forbid them in a transaction, but took it out again when I found the regression tests failing :-(. The offending code is in the sequence test: CREATE USER seq_user; BEGIN; SET LOCAL SESSION AUTHORIZATION seq_user; CREATE SEQUENCE seq3; SELECT nextval('seq3'); REVOKE ALL ON seq3 FROM seq_user; SELECT lastval(); ROLLBACK; DROP USER seq_user; DROP SEQUENCE seq; There isn't any amazingly good reason why this couldn't be handled without a transaction, viz SET SESSION AUTHORIZATION seq_user; ... RESET SESSION AUTHORIZATION; so unless someone comes up with a reasonable implementation plan for handling changes to both values within a transaction, I'll probably fall back to doing that. regards, tom lane
В списке pgsql-hackers по дате отправления: