Message-ID: <464edf94a18cf09a49e275d9e9d723949d7682d3.camel@cybertec.at>
Subject: Re: set role command
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Calvin Guo <newoakllc2023@gmail.com>, pgsql-general@lists.postgresql.org
Date: Mon, 24 Nov 2025 13:57:49 +0100
In-Reply-To: <CA+bysH_or91tt7r0gKLJtw5Wp+DEYwnaRJoTvLfKO33dcdQ_rQ@mail.gmail.com>
References: 
	<CA+bysH_or91tt7r0gKLJtw5Wp+DEYwnaRJoTvLfKO33dcdQ_rQ@mail.gmail.com>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
User-Agent: Evolution 3.56.2 (3.56.2-2.fc42) 
MIME-Version: 1.0
Archived-At: <https://www.postgresql.org/message-id/464edf94a18cf09a49e275d9e9d723949d7682d3.camel%40cybertec.at>
Precedence: bulk

On Mon, 2025-11-24 at 16:15 +0800, Calvin Guo wrote:
> I feel that set role logic is kindof misleading.
>=20
> 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.
>=20
> Which is not the case!
> as usera can do:
> set role userb; other sql,
> or=C2=A0
> 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 relationsh=
ip whatso ever.
>=20
> I really feel, once you "set role usera", you should behave like usera, y=
ou should
> NOT have the power say: hi, I can assume my super user power whenever I w=
ant.
> 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 AUTHORIZATI=
ON.

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

Yours,
Laurenz Albe