Subject: Re: set role command

From: Laurenz Albe <[email-masked]>
Date: Mon, 24 Nov 2025 13:57:49 +0100

On Mon, 2025-11-24 at 16:15 +0800, Calvin Guo wrote:
> I feel that set role logic is kindof misleading.
> 
> I am a superuser, admin,
> I do:
> set role usera
> Now I am under the security context of usera, so I think running any sql is safe
> as long as it's allowed by usera.
> 
> Which is not the case!
> as usera can do:
> set role userb; other sql,
> or 
> reset role; orther sql,
> it turns out it's not safe at all, the sql can easily get access right of the
> super user. it can impernate userb though they do not have any relationship whatso ever.
> 
> I really feel, once you "set role usera", you should behave like usera, you should
> NOT have the power say: hi, I can assume my super user power whenever I want.
> As this make the "set role usera" pretty much useless.

I respect your feelings, but that is not how SET ROLE works.
The current behavior is intentional and documented in
https://www.postgresql.org/docs/current/sql-set-role.html

There is SET SESSION AUTHORIZATION, which acts somewhet more like you want,
except that you can become a superuser again with RESET SESSION AUTHORIZATION.

You'll have to come up with a different security concept.

Yours,
Laurenz Albe

---

From: Tom Lane <[email-masked]>
Date: Mon, 24 Nov 2025 11:18:20 -0500

> https://www.postgresql.org/docs/current/sql-set-role.html

And it's also required by the SQL standard, which is very clear
that "user identifier" and "role" are different things, and
SET ROLE only changes the latter.

> except that you can become a superuser again with RESET SESSION AUTHORIZATION.

In the standard, the privileges required to do SET SESSION
AUTHORIZATION are "implementation defined", which means we could
change how it works without breaking standards conformance.
We'd still be breaking backwards compatibility, though --- for
instance, pg_dump dumps made with --use-set-session-authorization
would stop working.  I think that a proposal to change this has
very little chance of succeeding.

The best way to lock things down is to start a new session under
the restricted user name.

			regards, tom lane

---

From: Nico Williams <[email-masked]>
Date: Mon, 24 Nov 2025 13:27:04 -0600

> very little chance of succeeding.

Can we have an extension to say PERMANENTLY?

This is the SQL equivalent of `setreuid()`, essentially.  Except that
unix has a way to do it permanently.  It's a _very_ useful thing to
have.

Nico
--