MIME-Version: 1.0
References: <CA+bysH_or91tt7r0gKLJtw5Wp+DEYwnaRJoTvLfKO33dcdQ_rQ@mail.gmail.com>
 <176402341688.7.8547269556353940402.1025478372@georgiou.vip>
In-Reply-To: <176402341688.7.8547269556353940402.1025478372@georgiou.vip>
Sender: calvinguo@gmail.com
From: Calvin Guo <newoakllc2023@gmail.com>
Date: Thu, 27 Nov 2025 14:25:33 +0800
Message-ID: <CA+bysH86U8QRX+HWSRQTNdY8mu5i_qOFy1OF9-spenWCOtngaw@mail.gmail.com>
Subject: Re: set role command
To: pg254kl@georgiou.vip
Cc: "pgsql-generallists.postgresql.org" <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="0000000000003fcec506448d9583"
Archived-At: <https://www.postgresql.org/message-id/CA%2BbysH86U8QRX%2BHWSRQTNdY8mu5i_qOFy1OF9-spenWCOtngaw%40mail.gmail.com>
Precedence: bulk

--0000000000003fcec506448d9583
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

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=E2=80=AFAM <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!
>
>

--0000000000003fcec506448d9583
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><span style=3D"font-family:monospace;font-size:large">crea=
te role usera_sandbox in group usera;</span><br style=3D"font-family:monosp=
ace;font-size:large"><span style=3D"font-family:monospace;font-size:large">=
\c - usera_sandbox</span><div><span style=3D"font-family:monospace;font-siz=
e:large">but what will happen if I then issue:</span></div><div><span style=
=3D"font-family:monospace;font-size:large">reset role?</span></div><div><sp=
an style=3D"font-family:monospace;font-size:large">I don&#39;t think it is =
a real sandbox. You can always escape.</span></div><div><span style=3D"font=
-family:monospace;font-size:large"><br></span></div><div><span style=3D"fon=
t-family:monospace;font-size:large">To make it worse, I tested &quot;set se=
ssion authorization rolename&quot;, which will change the session user and =
current user to new rolename. But I can still do=C2=A0</span></div><div><sp=
an style=3D"font-family:monospace;font-size:large">reset session authorizat=
ion</span><span style=3D"font-family:monospace;font-size:large"></span></di=
v><div><span style=3D"font-family:monospace;font-size:large">to go back to =
super user.</span></div><div><span style=3D"font-family:monospace;font-size=
:large"><br></span></div><div><span style=3D"font-family:monospace;font-siz=
e:large">Seems like once I connect as a super user, there is no way for dro=
p the previledge.</span></div></div><br><div class=3D"gmail_quote gmail_quo=
te_container"><div dir=3D"ltr" class=3D"gmail_attr">On Tue, Nov 25, 2025 at=
 6:30=E2=80=AFAM &lt;pg254kl@georgiou.vip&gt; wrote:<br></div><blockquote c=
lass=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px soli=
d rgb(204,204,204);padding-left:1ex"><u></u>

 =20
   =20
 =20
  <div>
    <p>Just because you did <font face=3D"monospace" size=3D"4">set role</f=
ont>
      does not mean you lost your superuser privileges, it&#39;s correct
      behavior.</p>
    <p>If you want to impersonate in a permissions sandbox it&#39;s easy:<b=
r>
      <br>
      <font face=3D"monospace"><font size=3D"4">create role usera_sandbox i=
n
          group usera;<br>
          \c - usera_sandbox</font><br>
      </font>=C2=A0=C2=A0</p>
    <pre cols=3D"72">--=20
regards,
Kiriakos Georgiou</pre>
    <p><br>
    </p>
    <div>On 11/24/25 3:15 AM, Calvin Guo -
      newoakllc2023 at <a href=3D"http://gmail.com" target=3D"_blank">gmail=
.com</a> wrote:<br>
    </div>
    <blockquote type=3D"cite">
     =20
      <div dir=3D"ltr">I feel that set role logic is kindof misleading.
        <div><br>
        </div>
        <div>I am a superuser, admin,</div>
        <div>I do:</div>
        <div>set role usera</div>
        <div>Now I am under the security context of usera, so I think
          running any sql is safe as long as it&#39;s allowed by usera.</di=
v>
        <div><br>
        </div>
        <div>Which is not the case!</div>
        <div>as usera can do:</div>
        <div>set role userb; other sql,</div>
        <div>or=C2=A0</div>
        <div>reset role; orther sql,</div>
        <div>it turns out it&#39;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.</div>
        <div><br>
        </div>
        <div>I really feel, once you &quot;set role usera&quot;, 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
          &quot;set role usera&quot; pretty much useless.</div>
        <div><br>
        </div>
        <div>It&#39;s unsafe!</div>
      </div>
    </blockquote>
    <pre cols=3D"72"></pre>
  </div>

</blockquote></div>

--0000000000003fcec506448d9583--