On Wed, Jun 21, 2023 at 04:28:43PM -0400, Joseph Koshakow wrote: > Currently, a user is allowed to execute SET SESSION AUTHORIZATION [1] > if the role they connected to PostgreSQL with was a superuser at the > time of connection. Even if the role is later altered to no longer be a > superuser, the session can still execute SET SESSION AUTHORIZATION, as > long as the session isn't disconnected. As a consequence, if that role > is altered to no longer be a superuser, then the user can use SET > SESSION AUTHORIZATION to switch to another role that is a superuser and > regain superuser privileges. They can even re-grant themselves the > superuser attribute.
I suspect most users aren't changing the superuser attribute on roles very often, so it's unlikely to be a problem. But it might still be worth revisiting. > It is possible that the user had already run SET SESSION AUTHORIZATION > to set their session to a superuser before their connecting role lost > the superuser attribute. In this case there's not much we can do. Right. > Also, from looking at the code and documentation, it looks like SET > SESSION AUTHORIZATION works this way intentionally. However, I'm unable > to figure out why we'd want it to work this way. I found a brief mention in the archives about this implementation decision [0], but I don't think it explains the reasoning. > I've attached a patch that would fix this issue by checking the catalog > to see if the connecting role is currently a superuser every time SET > SESSION AUTHORIZATION is run. However, according to the comment I > deleted there's something invalid about reading the catalog from that > function, though I wasn't able to understand it fully. This comment was added in e5d6b91. I see that RESET SESSION AUTHORIZATION with a concurrently dropped role will FATAL with your patch but succeed without it, which could be part of the reason. > One downside is that if a user switches their session authorization to > some role, then loses the superuser attribute on their connecting role, > they may be stuck in a that role with no way to reset their session > authorization without disconnecting and reconnecting. It looks like SetSessionAuthorization() skips the privilege checks if the target role is the authenticated role, so I don't think they'll get stuck. [0] https://postgr.es/m/Pine.LNX.4.30.0104182119290.762-100000%40peter.localdomain -- Nathan Bossart Amazon Web Services: https://aws.amazon.com
