public inbox for [email protected]  

help / color / mirror / Atom feed

From: Calvin Guo <[email protected]>
To: [email protected]
Cc: pgsql-generallists.postgresql.org <[email protected]>
Subject: Re: set role command
Date: Thu, 27 Nov 2025 14:25:33 +0800
Message-ID: <CA+bysH86U8QRX+HWSRQTNdY8mu5i_qOFy1OF9-spenWCOtngaw@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <CA+bysH_or91tt7r0gKLJtw5Wp+DEYwnaRJoTvLfKO33dcdQ_rQ@mail.gmail.com>
	<[email protected]>

create role usera_sandbox in group usera;
\c - usera_sandbox
but what will happen if I then issue:
reset role?
I don't think it is a real sandbox. You can always escape.

To make it worse, I tested "set session authorization rolename", which will
change the session user and current user to new rolename. But I can still
do
reset session authorization
to go back to super user.

Seems like once I connect as a super user, there is no way for drop the
previledge.

On Tue, Nov 25, 2025 at 6:30 AM <[email protected]> wrote:

> Just because you did set role does not mean you lost your superuser
> privileges, it's correct behavior.
>
> If you want to impersonate in a permissions sandbox it's easy:
>
> create role usera_sandbox in group usera;
> \c - usera_sandbox
>
>
> --
> regards,
> Kiriakos Georgiou
>
>
> On 11/24/25 3:15 AM, Calvin Guo - newoakllc2023 at gmail.com 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.
>
> It's unsafe!
>
>

view thread (4+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected]
  Subject: Re: set role command
  In-Reply-To: <CA+bysH86U8QRX+HWSRQTNdY8mu5i_qOFy1OF9-spenWCOtngaw@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox