Content-Type: text/plain;
	charset=utf-8
Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3864.200.81.1.6\))
Subject: Re: set role command
From: =?utf-8?Q?Micha=C5=82_K=C5=82eczek?= <michal@kleczek.org>
In-Reply-To: <CA+bysH_or91tt7r0gKLJtw5Wp+DEYwnaRJoTvLfKO33dcdQ_rQ@mail.gmail.com>
Date: Mon, 24 Nov 2025 14:13:19 +0100
Cc: pgsql-general@lists.postgresql.org
Content-Transfer-Encoding: quoted-printable
Message-Id: <539FB9D9-7542-487E-AFA7-152EFF9FCC89@kleczek.org>
References: <CA+bysH_or91tt7r0gKLJtw5Wp+DEYwnaRJoTvLfKO33dcdQ_rQ@mail.gmail.com>
To: Calvin Guo <newoakllc2023@gmail.com>
Archived-At: <https://www.postgresql.org/message-id/539FB9D9-7542-487E-AFA7-152EFF9FCC89%40kleczek.org>
Precedence: bulk


> On 24 Nov 2025, at 09:15, Calvin Guo <newoakllc2023@gmail.com> wrote:
>=20
> 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=20
> 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.
>=20
> 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.
>=20
> It's unsafe!

It is a known issue and there were various proposals (need to search =
pgsql-hackers list). One of them being =E2=80=9Cset role=E2=80=9D =
message at the protocol level (ie. unavailable from SQL). Another being =
=E2=80=9CSET ROLE =E2=80=A6 PASSWORD =E2=80=A6=E2=80=9D and =E2=80=9CRESET=
 ROLE PASSWORD =E2=80=A6=E2=80=9D which would allow resetting the role =
only when password is known.
I don=E2=80=99t think any of them gained traction to be honest.

Kind regards,

--
Michal=