Re: SET ROLE x NO RESET

On Tue, Jan 02, 2024 at 12:36:38PM -0500, Robert Haas wrote:
> IMHO, the best solution here would be a protocol message to change the
> session user. The pooler could use that repeatedly on the same
> session, but refuse to propagate such messages from client
> connections.

But this requires upgrading clients too.

IMO `SET ROLE .. NO RESET` would be terribly useful.  One could build:

 - login systems (e.g., bearer tokens, passwords) in SQL / PlPgSQL / etc

 - sudo-like things

Though maybe `NO RESET` isn't really needed to build these, since after
all one could use an unprivileged role and a SECURITY DEFINER function
that does the `SET ROLE` following some user-defined authentication
method, and so what if the client can RESET the role, since that brings
it back to the otherwise unprivileged role.

Who needs to RESET roles anyways?  Answer: connection pools, but not
every connection is used via a pool.  This brings up something: attempts
to reset a NO RESET session need to fail in such a way that a connection
pool can detect this and disconnect, or else it needs to fail by
terminating the connection altogether.

Nico
--