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 --
