On Mon, Dec 2, 2024 at 10:31 AM Wolfgang Walther <walt...@technowledgy.de>
wrote:
> 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.
Thanks all for the input so far. I think we are the "usual suspects" of
advocating for this feature. :)
set_user is a great extension. I think the functionality belongs in core
though, because it can't be used in hosted environments that don't support
it. Hopefully the cloud will wake up to the issue and start supporting
set_user in the meantime.
SET ROLE WITH <password> is really intriguing. If the authenticator role
has no privileges, then the pooler only elevates permissions, and auth is
integrated with the whole systems auth mechanism. Supporting other auth
mechanisms would be amazing. Big +1 from me.
I dug into existing poolers, to see where things stand. AFAICT, neither
pgbouncer nor pgpool-II do any user impersonation out of the box, so they
both have the "pool-per-role" bottleneck.
pgbouncer has three `pool_mode` options, defaulting to `session`.
;; When server connection is released back to pool:
> ;; session - after client disconnects (default)
> ;; transaction - after transaction finishes
> ;; statement - after statement finishes
> ;pool_mode = session
It also has a `disable_pqexec` config option:
;; Hackish security feature. Helps against SQL injection: when PQexec
> ;; is disabled, multi-statement cannot be made.
> ;disable_pqexec = 0
This effectively makes SET ROLE useless because the session is reset after
each single statement.
Pgpool-II has a `reset_query_list` config parameter, a set of commands run
each time a new client connects. For pg 8.3 and later they recommend
'ABORT; DISCARD ALL'.
AFAICT, it's left up to the developer to build user impersonation on top of
these poolers, which short of having a sandboxed session (or being flawless
w.r.t SQL injection) is inherently insecure.
As far as priorities, any thoughts on what would be the most beneficial
feature to add? I am not sure. SET SESSION AUTHORIZATION GUARDED BY seems
most powerful, but since it requires superuser, that's not ideal either.
Regards,
Eric
