From: Tom Lane <tgl@sss.pgh.pa.us>
To: =?utf-8?Q?=C3=81lvaro?= Herrera <alvherre@kurilemu.de>
cc: Laurenz Albe <laurenz.albe@cybertec.at>,
        Calvin Guo <newoakllc2023@gmail.com>,
        pgsql-general@lists.postgresql.org
Subject: Re: set role command
In-reply-to: <202511241909.nudhcfkcugfl@alvherre.pgsql>
References: <202511241909.nudhcfkcugfl@alvherre.pgsql>
Comments: In-reply-to =?utf-8?Q?=C3=81lvaro?= Herrera <alvherre@kurilemu.de>
	message dated "Mon, 24 Nov 2025 20:21:23 +0100"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <988532.1764013580.1@sss.pgh.pa.us>
Date: Mon, 24 Nov 2025 14:46:20 -0500
Message-ID: <988533.1764013580@sss.pgh.pa.us>
Archived-At: <https://www.postgresql.org/message-id/988533.1764013580%40sss.pgh.pa.us>
Precedence: bulk

=?utf-8?Q?=C3=81lvaro?= 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.

If we tried doing that, I'd prefer that the "rest state" be validly
authenticated, but it could be as a low-privilege user that can't
do much of anything.  However, then we'd need to have an actual
authentication exchange to raise privilege to whatever you wanted
to do useful work as, and that would have to be a protocol-level
thing not a SQL command.  I also wonder how much poolers would
really want to use that, because it'd partially defeat the goal
of quickly switching to different users.

			regards, tom lane