On 2019-Oct-08, Craig Ringer wrote:
> On Fri, 12 Jul 2019 at 01:27, Robert Haas <robertmhaas@gmail.com> wrote:
>
> > We have steadfastly refused to provide protocol-level tools for things
> > like "please change my user ID, and don't let anyone change it again
> > via SQL," and that's a huge problem for things like connection poolers
> > which can't parse all the SQL flowing through the connection (because
> > figuring out what it does requires solving the Halting Problem) and
> > wouldn't want to if they could for performance reasons. I think that's
> > a huge mistake.
>
> I very strongly agree. The inability to limit SET and RESET of SESSION
> AUTHORIZATION and ROLE is a huge pain point and it's far from the only one.
There's a reason the SQL standard defines SET SESSION AUTHORIZATION but
no RESET SESSION AUTHORIZATION: once you enter a security context, you
cannot escape it. ISTM that essentially we broke feature F321 "User
authorization" by adding RESET into the mix. (I think RESET ROLE breaks
the spirit of feature T331 too.) The SQL:2016 standard describes how
this is supposed to work in Foundation "4.40.1.1 SQL-session
authorization identifiers" (same section is numbered 4.35.1.1 in
SQL:2011), and ISTM we made a huge mess of it.
I don't see how to fix it, though. If we were to adopt the standard's
mechanism, we'd probably break tons of existing code.
--
Álvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services