MIME-Version: 1.0
References: <CAM+6J97Jd5WnxdyFu1-s+wvJrWPL_YvP3yeuQVwy3HJvK=KUTQ@mail.gmail.com>
 <202411131029.qchduffwgzhm@alvherre.pgsql> <CAFCRh-9Le3fkJMWGuHdFPP-kfHdEAAchFZf0msEq3hNijixbFw@mail.gmail.com>
 <7d32e088-34a7-421a-9398-80958acb3f64@technowledgy.de> <CACA6kxh5EWZPMR5XCEXh3C-t-bBfdhbge1z+nGFpCOUgbZK+EA@mail.gmail.com>
 <c607b5e4-93c9-4c3d-9a1c-e3210ab91fb8@technowledgy.de>
In-Reply-To: <c607b5e4-93c9-4c3d-9a1c-e3210ab91fb8@technowledgy.de>
From: Eric Hanson <eric@aquameta.com>
Date: Fri, 22 Nov 2024 17:23:19 -0600
Message-ID: <CACA6kxiRjFCwq6toyY4iXAcQuQsgnobdcmXgdJTZ82fp4idpZg@mail.gmail.com>
Subject: Re: Fwd: A million users
To: walther@technowledgy.de
Cc: Dominique Devienne <ddevienne@gmail.com>, Alvaro Herrera <alvherre@alvh.no-ip.org>, 
	Vijaykumar Jain <vijaykumarjain.github@gmail.com>, 
	pgsql-general <pgsql-general@postgresql.org>, "kaare@jasonic.dk" <kaare@jasonic.dk>
Content-Type: multipart/alternative; boundary="000000000000bd45ed062788ab8c"
Archived-At: <https://www.postgresql.org/message-id/CACA6kxiRjFCwq6toyY4iXAcQuQsgnobdcmXgdJTZ82fp4idpZg%40mail.gmail.com>
Precedence: bulk

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

On Fri, Nov 22, 2024 at 6:57=E2=80=AFAM <walther@technowledgy.de> wrote:

> Yeah, this is still on my list of things to research more about
> eventually - currently still unsolved.
>
> For my use-case the NO RESET would need to apply until the end of the
> transaction, not end of the session.
>
> I imagine something like an extension, that would:
> - block any SET SESSION ROLE
> - block any RESET ROLE
> - only allow SET LOCAL ROLE when CURRENT_USER has the right to do so
>
> Then the effect of SET LOCAL ROLE would still be reversed at the end of
> the transaction, but you could never "escape" a SET LOCAL ROLE that was
> set earlier.


As things are now, would someone be able to do a RESET ROLE if *any*
code/function had a SQL injection vulnerability, or only if there was one
in the pooler?  Or (ideally) neither.  That's what a NO RESET option (or
some similar functionality) would provide with certainty.

I found this extension:

https://github.com/pgaudit/set_user

but haven't used it.  Seems to address this though, they introduce a
set_session_auth(token) function and then reset_role requires the token if
session_auth has been set.

Thanks,
Eric

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

<div dir=3D"ltr"><div dir=3D"ltr">On Fri, Nov 22, 2024 at 6:57=E2=80=AFAM &=
lt;<a href=3D"mailto:walther@technowledgy.de">walther@technowledgy.de</a>&g=
t; wrote:</div><div class=3D"gmail_quote"><blockquote class=3D"gmail_quote"=
 style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);p=
adding-left:1ex">Yeah, this is still on my list of things to research more =
about <br>
eventually - currently still unsolved.<br>
<br>
For my use-case the NO RESET would need to apply until the end of the <br>
transaction, not end of the session.<br>
<br>
I imagine something like an extension, that would:<br>
- block any SET SESSION ROLE<br>
- block any RESET ROLE<br>
- only allow SET LOCAL ROLE when CURRENT_USER has the right to do so<br>
<br>
Then the effect of SET LOCAL ROLE would still be reversed at the end of <br=
>
the transaction, but you could never &quot;escape&quot; a SET LOCAL ROLE th=
at was <br>
set earlier.</blockquote><div><br></div><div>As things are now, would someo=
ne be able to do a RESET ROLE if *any* code/function had a SQL injection vu=
lnerability, or only if there was one in the pooler?=C2=A0 Or (ideally) nei=
ther.=C2=A0 That&#39;s what a NO RESET option (or some similar functionalit=
y) would provide with certainty.</div><div><br></div><div><div>I found this=
 extension:</div><div><br></div><div><a href=3D"https://github.com/pgaudit/=
set_user">https://github.com/pgaudit/set_user</a></div><div><br></div><div>=
but haven&#39;t used it.=C2=A0 Seems to address this though, they introduce=
 a set_session_auth(token) function and then reset_role requires the token =
if session_auth has been set.</div><div><br></div></div><div>Thanks,</div><=
div>Eric</div></div></div>

--000000000000bd45ed062788ab8c--