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.

An unprivileged role that has the ability to assume any other role ;p

Also, last I checked you can't do SET ROLE in a security definer function.

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.

RESET ROLE is also useful for setting up a "superuser role" that DBAs have access to via a NO INHERIT role. It allows them to do things like...

SET ROLE su;

-- Do some superuserly thing

RESET ROLE;

Admittedly, the last step could be just to set their role back to themselves, but RESET ROLE removes the risk of typos.

-- 
Jim Nasby, Data Architect, Austin TX