public inbox for [email protected]
help / color / mirror / Atom feedRe: set role command
3+ messages / 3 participants
[nested] [flat]
* Re: set role command
@ 2025-11-24 12:57 Laurenz Albe <[email protected]>
0 siblings, 1 reply; 3+ messages in thread
From: Laurenz Albe @ 2025-11-24 12:57 UTC (permalink / raw)
To: Calvin Guo <[email protected]>; [email protected]
On Mon, 2025-11-24 at 16:15 +0800, Calvin Guo 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.
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 AUTHORIZATION.
You'll have to come up with a different security concept.
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: set role command
@ 2025-11-24 16:18 Tom Lane <[email protected]>
parent: Laurenz Albe <[email protected]>
0 siblings, 1 reply; 3+ messages in thread
From: Tom Lane @ 2025-11-24 16:18 UTC (permalink / raw)
To: Laurenz Albe <[email protected]>; +Cc: Calvin Guo <[email protected]>; [email protected]
Laurenz Albe <[email protected]> writes:
> On Mon, 2025-11-24 at 16:15 +0800, Calvin Guo wrote:
>> 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.
> 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
And it's also required by the SQL standard, which is very clear
that "user identifier" and "role" are different things, and
SET ROLE only changes the latter.
> There is SET SESSION AUTHORIZATION, which acts somewhet more like you want,
> except that you can become a superuser again with RESET SESSION AUTHORIZATION.
In the standard, the privileges required to do SET SESSION
AUTHORIZATION are "implementation defined", which means we could
change how it works without breaking standards conformance.
We'd still be breaking backwards compatibility, though --- for
instance, pg_dump dumps made with --use-set-session-authorization
would stop working. I think that a proposal to change this has
very little chance of succeeding.
The best way to lock things down is to start a new session under
the restricted user name.
regards, tom lane
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: set role command
@ 2025-11-24 19:27 Nico Williams <[email protected]>
parent: Tom Lane <[email protected]>
0 siblings, 0 replies; 3+ messages in thread
From: Nico Williams @ 2025-11-24 19:27 UTC (permalink / raw)
To: Tom Lane <[email protected]>; +Cc: Laurenz Albe <[email protected]>; Calvin Guo <[email protected]>; [email protected]
On Mon, Nov 24, 2025 at 11:18:20AM -0500, Tom Lane wrote:
> Laurenz Albe <[email protected]> writes:
> > On Mon, 2025-11-24 at 16:15 +0800, Calvin Guo wrote:
> >> 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.
>
> > 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
>
> And it's also required by the SQL standard, which is very clear
> that "user identifier" and "role" are different things, and
> SET ROLE only changes the latter.
>
> > There is SET SESSION AUTHORIZATION, which acts somewhet more like you want,
> > except that you can become a superuser again with RESET SESSION AUTHORIZATION.
>
> In the standard, the privileges required to do SET SESSION
> AUTHORIZATION are "implementation defined", which means we could
> change how it works without breaking standards conformance.
> We'd still be breaking backwards compatibility, though --- for
> instance, pg_dump dumps made with --use-set-session-authorization
> would stop working. I think that a proposal to change this has
> very little chance of succeeding.
Can we have an extension to say PERMANENTLY?
This is the SQL equivalent of `setreuid()`, essentially. Except that
unix has a way to do it permanently. It's a _very_ useful thing to
have.
Nico
--
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2025-11-24 19:27 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-11-24 12:57 Re: set role command Laurenz Albe <[email protected]>
2025-11-24 16:18 ` Tom Lane <[email protected]>
2025-11-24 19:27 ` Nico Williams <[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