On 2019-Oct-08, Craig Ringer wrote: > On Fri, 12 Jul 2019 at 01:27, Robert Haas <robertmh...@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
