Eric Hanson:
a) Transaction ("local") Sandbox:
- SET LOCAL ROLE alice NO RESET;
- SET LOCAL ROLE alice WITHOUT RESET;
- BEGIN AS ROLE alice;
Transaction-level sandboxes have the benefit that a pooler can simply
start a new sandboxed transaction for each request and never have to
worry about resetting or reusing them.
b) Session Sandbox:
- SET ROLE alice NO RESET;
- SET ROLE alice WITHOUT RESET;
- SET UNRESETTABLE ROLE alice; --veto
Session-level sandboxes have the benefit that they can do things that
can't be done inside a transaction (e.g. create extensions, vacuum,
analyze, etc.) It's a fully functional session. However if RESET ROLE
is prohibited for the rest of the session, a connection pooler couldn't
reuse it.
c) "Guarded" Transaction/Session
- SET [LOCAL] ROLE alice GUARDED BY reset_token;
- RESET ROLE WITH TOKEN reset_token;
Guarded sandboxes are nice because the session can also exit the sandbox
if it has the token.
d) SET [LOCAL] ROLE alice WITH <password>;
Which would allow you to change to a role for which you don't have any
grants, yet. Then, the "authenticator pattern" could be implemented by
having an authenticator role without any privileges to start with.
The client would provide the password to elevate their privileges. RESET
ROLE would not be problematic anymore. This would be much cheaper than
having those roles do full logins on a new connection and could be used
with connection poolers nicely.
Possibly, this could also be extended by supporting alternatives to just
a password, for example Json Web Tokens. Maybe building on top of the
ongoing OAuth effort? Those tokens would then contain a claim for the
role they are allowed to set.
Best,
Wolfgang
