Обсуждение: set role command

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.
>
> It's unsafe!


Hello,

firstly you have to create a role usera and in doing so give it the
privileges you want usera to have. You can also restrict its privileges
by specifying the NO* range of options.

So, if you are logged in as userb, say, doing "set role usera" simply
switches you out of userb into usera, and you behave as usera in that
session.

HTH,
Rob

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

> On 24 Nov 2025, at 09:15, Calvin Guo <newoakllc2023@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
thoughthey 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
assumemy super user power whenever I want. As this make the "set role usera" pretty much useless. 
>
> It's unsafe!

It is a known issue and there were various proposals (need to search pgsql-hackers list). One of them being “set role”
messageat the protocol level (ie. unavailable from SQL). Another being “SET ROLE … PASSWORD …” and “RESET ROLE PASSWORD
…”which would allow resetting the role only when password is known. 
I don’t think any of them gained traction to be honest.

Kind regards,

--
Michal

On Mon, Nov 24, 2025 at 9:15 AM Calvin Guo <newoakllc2023@gmail.com> 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.

SET ROLE is only about switching between USAGE and MEMBER of other ROLEs.
Since v16, one can control individual ROLE-to-ROLE GRANTs, for INHERIT'ance.
You can be GRANT'ed a ROLE, yet the permissions (ACLs on objects) associated to
that ROLE may not be "active", unless you INHERIT that ROLE. W/o INHERIT true,
you must explicit SET ROLE to that role, to activate that role and its
permissions.

I thought like you initially that after a SET ROLE, that restricted
which ROLE I could endorsed.
But no, of course. Restricting SET ROLE on ROLEs from the subgraph
makes no sense, when
you can RESET ROLE to "get back up" to your "root" role (your LOGIN
role, or SESSION_ROLE),
and then SET ROLE to a different subgraph role.

Any ROLE you are a MEMBER of, you can SET ROLE to, whatever the current_role.
But by playing with INHERIT'ance, you CAN control what's
active/effective, for a given current role.
One can always SET ROLE to some other ROLE, of course.

What really matters is who CONNECT'ed / logged-IN to the DB. I.e. who
is authenticated.
If you start as a SUPERUSER, then you can always become SUPERUSER again, as
Laurenz pointed out. Changing ROLE is only about activate this or that set of
permissions / privileges, on DB objects. Nothing more. --DD

Laurenz Albe <laurenz.albe@cybertec.at> 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

On 2025-Nov-24, Tom Lane wrote:

> Laurenz Albe <laurenz.albe@cybertec.at> writes:
> > On Mon, 2025-11-24 at 16:15 +0800, Calvin Guo wrote:
> > 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.

For what it's worth, I think we break the SQL standard's security model
by providing RESET ROLE and RESET SESSION AUTHORIZATION, neither of
which the standard has.  This means that in the standard model you have
commands to lower your privilege, but once you've lowered them, you
cannot return (in the same connection) to what you had.

Section 4.42 "Basic security model" of the 2023 edition of the standard
explains this.  There is a stack of authorizations -- but when you do
SET SESSION AUTHORIZATION, you don't add another cell of the stack.
Instead, the current session user is replaced.  They provide no way to
return.  The stack is used for
  <routine invocation>,
  <externally-invoked procedure>,
  triggered action,
  <execute statement>, or
  <direct SQL statement>
and the stack cell so created is automatically removed when that
operation completes.

Of course, I may be misreading the standard.

-- 
Álvaro Herrera        Breisgau, Deutschland  —  https://www.EnterpriseDB.com/
"No renuncies a nada. No te aferres a nada."

=?utf-8?Q?=C3=81lvaro?= Herrera <alvherre@kurilemu.de> writes:
> For what it's worth, I think we break the SQL standard's security model
> by providing RESET ROLE and RESET SESSION AUTHORIZATION, neither of
> which the standard has.

I don't think so.  They are just shorthand for issuing a SET to the
original value, so how do they break the model in a way that that
doesn't?

> This means that in the standard model you have
> commands to lower your privilege, but once you've lowered them, you
> cannot return (in the same connection) to what you had.

The reason PG acts as it does is that we interpret "the permissions
required to do SET SESSION AUTHORIZATION" as "did your originally
authenticated ID have permission to do that SET?".  Since those
permissions are implementation-defined, I don't think it's possible
to argue that our choice breaks standards compliance.  You can argue
that it's a bad idea, but it's a bit late to change it now.

In practical terms, the one-way changes that Calvin wants are just not
that attractive.  What people have actually asked for, particularly
connection-pooler authors, are a way to switch session authorization
in such a way that you can only go back with some additional secret
sauce, like a one-time password generated at the pooler level.
That'd allow sharing the same connection across different user IDs,
which isn't safe today.

            regards, tom lane

On 2025-Nov-24, Tom Lane wrote:

> =?utf-8?Q?=C3=81lvaro?= Herrera <alvherre@kurilemu.de> writes:
> > For what it's worth, I think we break the SQL standard's security model
> > by providing RESET ROLE and RESET SESSION AUTHORIZATION, neither of
> > which the standard has.
> 
> I don't think so.  They are just shorthand for issuing a SET to the
> original value, so how do they break the model in a way that that
> doesn't?

No, because the new user doesn't have privs to become the previous one.

> > This means that in the standard model you have
> > commands to lower your privilege, but once you've lowered them, you
> > cannot return (in the same connection) to what you had.
> 
> The reason PG acts as it does is that we interpret "the permissions
> required to do SET SESSION AUTHORIZATION" as "did your originally
> authenticated ID have permission to do that SET?".

I have the impression that that's broken, because for <set session user
identifier statement>,  the standard says that the current user
identifier is set to the new user, which means the previous user
identifier is lost.  You don't retain rights to become that user again.

> In practical terms, the one-way changes that Calvin wants are just not
> that attractive.  What people have actually asked for, particularly
> connection-pooler authors, are a way to switch session authorization
> in such a way that you can only go back with some additional secret
> sauce, like a one-time password generated at the pooler level.

Yeah, I think connection poolers would be better served by a different
security model than what the standard offers.  The pooler-generated
one-time password, or something similar, seems somewhat appropriate
given what we offer today.  I'm not sure it's the best model either,
because it requires that the connection is initially in a superuser-like
state (so that it can become any other user).  But that's somewhat
dangerous, because if an attacker manages to steal the one-time
password, they are database superuser and all is lost.  It would be more
secure to have a mechanism where the connection is initially
unauthenticated altogether (which means: it's not a valid SQL session),
becomes authenticated at the pooler's will, and returns to
unauthenticated state as the pooler decides.  Critically, from
unauthenticated state you shouldn't be able to become superuser.

(Also, last I looked at pgbouncer, it had to do some weird tricks to
mimick the authentication on their side without involving the server, so
it wanted to keep its own list of user/passwords or something like that.
Maybe that's changed in the last few years though.)

-- 
Álvaro Herrera               48°01'N 7°57'E  —  https://www.EnterpriseDB.com/
"Having your biases confirmed independently is how scientific progress is
made, and hence made our great society what it is today" (Mary Gardiner)

On Mon, Nov 24, 2025 at 11:18:20AM -0500, Tom Lane wrote:
> Laurenz Albe <laurenz.albe@cybertec.at> 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
-- 

=?utf-8?Q?=C3=81lvaro?= Herrera <alvherre@kurilemu.de> writes:
> On 2025-Nov-24, Tom Lane wrote:
>> I don't think so.  They are just shorthand for issuing a SET to the
>> original value, so how do they break the model in a way that that
>> doesn't?

> No, because the new user doesn't have privs to become the previous one.

Don't think you can make that argument from the standard, since
it explicitly disclaims saying what privs are required.

> It would be more
> secure to have a mechanism where the connection is initially
> unauthenticated altogether (which means: it's not a valid SQL session),
> becomes authenticated at the pooler's will, and returns to
> unauthenticated state as the pooler decides.  Critically, from
> unauthenticated state you shouldn't be able to become superuser.

I don't like the idea that a pooler or pretend-to-be pooler
can eat up a backend session without having authenticated at all.
Also, exactly what does "becomes authenticated at the pooler's will"
mean?  There had better be some actual authentication happening
somewhere.

If we tried doing that, I'd prefer that the "rest state" be validly
authenticated, but it could be as a low-privilege user that can't
do much of anything.  However, then we'd need to have an actual
authentication exchange to raise privilege to whatever you wanted
to do useful work as, and that would have to be a protocol-level
thing not a SQL command.  I also wonder how much poolers would
really want to use that, because it'd partially defeat the goal
of quickly switching to different users.

            regards, tom lane

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

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!

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

On 2025-11-24 17:30:14 -0500, pg254kl@georgiou.vip 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.
|_|_) |                    |
| |   | hjp@hjp.at         |    -- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |       challenge!"