On 12/31/23 1:19 PM, Joe Conway wrote:

On 12/30/23 17:19, Michał Kłeczek wrote:

On 30 Dec 2023, at 17:16, Eric Hanson <eric@aquameta.com> wrote:

What do you think of adding a NO RESET option to the SET ROLE command?

What I proposed some time ago is SET ROLE … GUARDED BY ‘password’, so that you could later: RESET ROLE WITH ‘password'

I like that too, but see it as a separate feature. FWIW that is also supported by the set_user extension referenced elsewhere on this thread.

That means you'd need to manage that password. ISTM a better mechanism to do this in SQL would be a method of changing roles that returns a nonce that you'd then use to reset your role. I believe that'd also make it practical to do this server-side in the various PLs.