Thread

1. set role command

   Calvin Guo <newoakllc2023@gmail.com> — 2025-11-24T08:15:03Z

   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!
   

2. Re: set role command

   rob stone <floriparob@tpg.com.au> — 2025-11-24T12:06:20Z

   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
   
   
    
   
   
   
   

3. Re: set role command

   Calvin Guo <newoakllc2023@gmail.com> — 2025-11-24T12:18:18Z

   No, that's not the case. right now: usera can totally do: reset role, and
   then behave like userb.
   this behavior should not be allowed.
   userb can impernate usera, once the impersonation is done, it becomes
   usera, and it should NOT be able to impersonate userb. but right now, reset
   role will allow it.
   
   On Mon, Nov 24, 2025 at 8:06 PM rob stone <floriparob@tpg.com.au> wrote:
   
   > 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
   >
   >
   >
   >
   

4. Re: set role command

   Laurenz Albe <laurenz.albe@cybertec.at> — 2025-11-24T12:57:49Z

   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
   
   
   
   

5. Re: set role command

   Michał Kłeczek <michal@kleczek.org> — 2025-11-24T13:13:19Z

   
   > 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 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!
   
   It is a known issue and there were various proposals (need to search pgsql-hackers list). One of them being “set role” message at 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
   
   
   

6. Re: set role command

   Dominique Devienne <ddevienne@gmail.com> — 2025-11-24T13:46:20Z

   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
   
   
   
   

7. Re: set role command

   Tom Lane <tgl@sss.pgh.pa.us> — 2025-11-24T16:18:20Z

   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
   
   
   
   

8. Re: set role command

   Álvaro Herrera <alvherre@kurilemu.de> — 2025-11-24T17:05:50Z

   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."
   
   
   
   

9. Re: set role command

   Tom Lane <tgl@sss.pgh.pa.us> — 2025-11-24T18:43:31Z

   =?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
   
   
   
   

10. Re: set role command

    Álvaro Herrera <alvherre@kurilemu.de> — 2025-11-24T19:21:23Z

    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)
    
    
    
    

11. Re: set role command

    Nico Williams <nico@cryptonector.com> — 2025-11-24T19:27:04Z

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

12. Re: set role command

    Tom Lane <tgl@sss.pgh.pa.us> — 2025-11-24T19:46:20Z

    =?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
    
    
    
    

13. Re: set role command

    Ron Johnson <ronljohnsonjr@gmail.com> — 2025-11-24T20:25:53Z

    On Mon, Nov 24, 2025 at 2:46 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
    
    > =?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.
    >
    
    A restriction that it can only happen when TLS authentication is used, and
    the pooler is using its service account?
    
    -- 
    Death to <Redacted>, and butter sauce.
    Don't boil me, I'm still alive.
    <Redacted> lobster!
    

14. Re: set role command

    pg254kl@georgiou.vip — 2025-11-24T22:30:14Z

    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!
    

15. Re: set role command

    Calvin Guo <newoakllc2023@gmail.com> — 2025-11-27T06:25:33Z

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

16. Re: set role command

    Laurenz Albe <laurenz.albe@cybertec.at> — 2025-11-27T06:56:46Z

    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
    
    
    
    

17. Re: set role command

    Peter J. Holzer <hjp-pgsql@hjp.at> — 2025-11-28T21:41:40Z

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