Subject: Re: set role command

From: [email-masked]
Date: Mon, 24 Nov 2025 17:30:14 -0500

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!

---

From: Calvin Guo <[email-masked]>
Date: Thu, 27 Nov 2025 14:25:33 +0800

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-masked]> wrote:

> 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:
> 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
> 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!
>
>

---

From: Laurenz Albe <[email-masked]>
Date: Thu, 27 Nov 2025 07:56:46 +0100

> but what will happen if I then issue:

Nothing will happen.

\c disconnects the superuser session and establishes a new connection,
so there is nothing to reset to.

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

Precisely.  That's why you don't connect as a superuser, unless you
need to for administrative purposes.

Yours,
Laurenz Albe

---

From: "Peter J. Holzer" <[email-masked]>
Date: Fri, 28 Nov 2025 22:41:40 +0100

On 2025-11-24 17:30:14 -0500, [email-masked] wrote:
> Just because you did set role does not mean you lost your superuser privileges,
> it's correct behavior.
> 

But that just disconnects and reconnects as the new user. So you have to
be able to authenticate as that user.

        hjp

-- 
   _  | Peter J. Holzer    | Story must make more sense than reality.
|_|_) |                    |
| |   | [email-masked]         |    -- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |       challenge!"