public inbox for [email protected]
help / color / mirror / Atom feedRe: set role command
4+ messages / 4 participants
[nested] [flat]
* Re: set role command
@ 2025-11-24 22:30 [email protected]
2025-11-27 06:25 ` Re: set role command Calvin Guo <[email protected]>
2025-11-28 21:41 ` Re: set role command Peter J. Holzer <[email protected]>
0 siblings, 2 replies; 4+ messages in thread
From: [email protected] @ 2025-11-24 22:30 UTC (permalink / raw)
To: Calvin Guo <[email protected]>; pgsql-generallists.postgresql.org <[email protected]>
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!
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: set role command
2025-11-24 22:30 Re: set role command [email protected]
@ 2025-11-27 06:25 ` Calvin Guo <[email protected]>
2025-11-27 06:56 ` Re: set role command Laurenz Albe <[email protected]>
1 sibling, 1 reply; 4+ messages in thread
From: Calvin Guo @ 2025-11-27 06:25 UTC (permalink / raw)
To: [email protected]; +Cc: pgsql-generallists.postgresql.org <[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!
>
>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: set role command
2025-11-24 22:30 Re: set role command [email protected]
2025-11-27 06:25 ` Re: set role command Calvin Guo <[email protected]>
@ 2025-11-27 06:56 ` Laurenz Albe <[email protected]>
0 siblings, 0 replies; 4+ messages in thread
From: Laurenz Albe @ 2025-11-27 06:56 UTC (permalink / raw)
To: Calvin Guo <[email protected]>; [email protected]; +Cc: pgsql-generallists.postgresql.org <[email protected]>
On Thu, 2025-11-27 at 14:25 +0800, Calvin Guo wrote:
> create role usera_sandbox in group usera;
> \c - usera_sandbox
> but what will happen if I then issue:
> reset role?
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: set role command
2025-11-24 22:30 Re: set role command [email protected]
@ 2025-11-28 21:41 ` Peter J. Holzer <[email protected]>
1 sibling, 0 replies; 4+ messages in thread
From: Peter J. Holzer @ 2025-11-28 21:41 UTC (permalink / raw)
To: [email protected]
On 2025-11-24 17:30:14 -0500, [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
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 protected] | -- Charles Stross, "Creative writing
__/ | http://www.hjp.at/ | challenge!"
Attachments:
[application/pgp-signature] signature.asc (833B, 2-signature.asc)
download
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2025-11-28 21:41 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-11-24 22:30 Re: set role command [email protected]
2025-11-27 06:25 ` Calvin Guo <[email protected]>
2025-11-27 06:56 ` Laurenz Albe <[email protected]>
2025-11-28 21:41 ` Peter J. Holzer <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox