Re: Preventing non-superusers from altering session authorization

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