MIME-Version: 1.0
References: <202511241909.nudhcfkcugfl@alvherre.pgsql> <988533.1764013580@sss.pgh.pa.us>
In-Reply-To: <988533.1764013580@sss.pgh.pa.us>
From: Ron Johnson <ronljohnsonjr@gmail.com>
Date: Mon, 24 Nov 2025 15:25:53 -0500
Message-ID: <CANzqJaB424ydLyw3VNPD=Yrvcvb2MmksczH9VY967EA3E=5v4w@mail.gmail.com>
Subject: Re: set role command
To: "pgsql-generallists.postgresql.org" <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="0000000000000c92c206445cf95b"
Archived-At: <https://www.postgresql.org/message-id/CANzqJaB424ydLyw3VNPD%3DYrvcvb2MmksczH9VY967EA3E%3D5v4w%40mail.gmail.com>
Precedence: bulk

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

On Mon, Nov 24, 2025 at 2:46=E2=80=AFPM Tom Lane <tgl@sss.pgh.pa.us> wrote:

> =3D?utf-8?Q?=3DC3=3D81lvaro?=3D 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?

--=20
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!

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

<div dir=3D"ltr"><div dir=3D"ltr">On Mon, Nov 24, 2025 at 2:46=E2=80=AFPM T=
om Lane &lt;<a href=3D"mailto:tgl@sss.pgh.pa.us">tgl@sss.pgh.pa.us</a>&gt; =
wrote:</div><div class=3D"gmail_quote gmail_quote_container"><blockquote cl=
ass=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid=
 rgb(204,204,204);padding-left:1ex">=3D?utf-8?Q?=3DC3=3D81lvaro?=3D Herrera=
 &lt;<a href=3D"mailto:alvherre@kurilemu.de" target=3D"_blank">alvherre@kur=
ilemu.de</a>&gt; writes:<br>
&gt; On 2025-Nov-24, Tom Lane wrote:<br>
&gt;&gt; I don&#39;t think so.=C2=A0 They are just shorthand for issuing a =
SET to the<br>
&gt;&gt; original value, so how do they break the model in a way that that<=
br>
&gt;&gt; doesn&#39;t?<br>
<br>
&gt; No, because the new user doesn&#39;t have privs to become the previous=
 one.<br>
<br>
Don&#39;t think you can make that argument from the standard, since<br>
it explicitly disclaims saying what privs are required.<br>
<br>
&gt; It would be more<br>
&gt; secure to have a mechanism where the connection is initially<br>
&gt; unauthenticated altogether (which means: it&#39;s not a valid SQL sess=
ion),<br>
&gt; becomes authenticated at the pooler&#39;s will, and returns to<br>
&gt; unauthenticated state as the pooler decides.=C2=A0 Critically, from<br=
>
&gt; unauthenticated state you shouldn&#39;t be able to become superuser.<b=
r>
<br>
I don&#39;t like the idea that a pooler or pretend-to-be pooler<br>
can eat up a backend session without having authenticated at all.<br>
Also, exactly what does &quot;becomes authenticated at the pooler&#39;s wil=
l&quot;<br>
mean?=C2=A0 There had better be some actual authentication happening<br>
somewhere.<br></blockquote><div><br></div><div>A restriction that it can on=
ly happen when TLS authentication is used, and the pooler is using its serv=
ice account?</div><div><br></div></div><span class=3D"gmail_signature_prefi=
x">-- </span><br><div dir=3D"ltr" class=3D"gmail_signature"><div dir=3D"ltr=
">Death to &lt;Redacted&gt;, and butter sauce.<div>Don&#39;t boil me, I&#39=
;m still alive.<br><div><div>&lt;Redacted&gt; lobster!</div></div></div></d=
iv></div></div>

--0000000000000c92c206445cf95b--