Re: Proposal: Role Sandboxing for Secure Impersonation - Mailing list pgsql-hackers
| From | Eric Hanson |
|---|---|
| Subject | Re: Proposal: Role Sandboxing for Secure Impersonation |
| Date | |
| Msg-id | CACA6kxgt7Ln+k5iqE4BLEVzHD9AZe0sgPuCdL+wiznCdQ_Ax+g@mail.gmail.com Whole thread Raw |
| In response to | Re: Proposal: Role Sandboxing for Secure Impersonation (Wolfgang Walther <walther@technowledgy.de>) |
| Responses |
Re: Proposal: Role Sandboxing for Secure Impersonation
Re: Proposal: Role Sandboxing for Secure Impersonation |
| List | pgsql-hackers |
On Mon, Dec 2, 2024 at 10:31 AM Wolfgang Walther <walther@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
pgsql-hackers by date: