<!DOCTYPE html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
</head>
<body>
<div class="moz-cite-prefix">On 1/3/24 5:47 PM, Nico Williams wrote:<br>
</div>
<blockquote type="cite" cite="mid:ZZXyB3aTBKagISrc(at)ubby">
<pre>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.</pre>
</blockquote>
<p>An unprivileged role that has the ability to assume any other
role ;p</p>
<p>Also, last I checked you can't do SET ROLE in a security definer
function.<br>
</p>
<blockquote type="cite" cite="mid:ZZXyB3aTBKagISrc(at)ubby">
<pre>
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.
</pre>
</blockquote>
<p>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...</p>
<p>SET ROLE su;</p>
<p>-- Do some superuserly thing</p>
<p>RESET ROLE;</p>
<p>Admittedly, the last step could be just to set their role back to
themselves, but RESET ROLE removes the risk of typos.<br>
</p>
<pre class="moz-signature" cols="72">--
Jim Nasby, Data Architect, Austin TX</pre>
</body>
</html>