Re: SET ROLE x NO RESET - Mailing list pgsql-hackers

From Nico Williams
Subject Re: SET ROLE x NO RESET
Date
Msg-id ZZXyB3aTBKagISrc@ubby
Whole thread Raw
In response to Re: SET ROLE x NO RESET  (Robert Haas <robertmhaas@gmail.com>)
List pgsql-hackers
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
-- 



pgsql-hackers by date:

Previous
From: Nico Williams
Date:
Subject: Re: SET ROLE x NO RESET
Next
From: Michael Paquier
Date:
Subject: Re: Adding facility for injection points (or probe points?) for more advanced tests