Proposal: Role Sandboxing for Secure Impersonation - Mailing list pgsql-hackers
| From | Eric Hanson |
|---|---|
| Subject | Proposal: Role Sandboxing for Secure Impersonation |
| Date | |
| Msg-id | CACA6kxh4MfRCHuY+uC2ZvXRQUP63LqumNtxtLsDF-mJswAJR5w@mail.gmail.com Whole thread Raw |
| Responses |
Re: Proposal: Role Sandboxing for Secure Impersonation
Re: Proposal: Role Sandboxing for Secure Impersonation |
| List | pgsql-hackers |
Hi all,
I'd like to revisit a previously discussed feature [1] that PostgreSQL could benefit from a "role sandbox", a feature that would build on SET [LOCAL] ROLE, and prevent or restrict RESET ROLE.
Rationale: Connection pooling is widely used to optimize database performance by reducing use of memory, process creation, etc. However, connection pools typically operate on a "pool-per-role" basis, because each connection is bound to a single role and can't be reused by another role. For systems that make use of many roles, this limits the effectiveness of connection pooling because each role has their own "pool space" and max_connections puts a hard limit on how many connections can exist.
To work around this, projects (e.g. PostgREST) employ the "user impersonation" pattern:
- All connections use a shared "authenticator" role
- When a user (e.g. Alice) sends a request to the connection pooler, it temporarily sets the role using:
SET [LOCAL] ROLE alice;
- After processing Alice's request, the session resets the role back to the "authenticator" role by either issuing a "RESET ROLE" or ending the "local" transaction.
This approach works well in theory, but poses a significant security concern:
RESET ROLE allows a client to reset the role back to the "authenticator" role, *before* handing the session back to the pooler. Any SQL injection vulnerability or anything else that allows arbitrary SQL allows the client to issue a `RESET ROLE; SET ROLE anybody_else;`, bypassing authentication. Depending on the privileges of the "authenticator" role, the client can become any other user, or worse.
I'd like to revisit a previously discussed feature [1] that PostgreSQL could benefit from a "role sandbox", a feature that would build on SET [LOCAL] ROLE, and prevent or restrict RESET ROLE.
Rationale: Connection pooling is widely used to optimize database performance by reducing use of memory, process creation, etc. However, connection pools typically operate on a "pool-per-role" basis, because each connection is bound to a single role and can't be reused by another role. For systems that make use of many roles, this limits the effectiveness of connection pooling because each role has their own "pool space" and max_connections puts a hard limit on how many connections can exist.
To work around this, projects (e.g. PostgREST) employ the "user impersonation" pattern:
- All connections use a shared "authenticator" role
- When a user (e.g. Alice) sends a request to the connection pooler, it temporarily sets the role using:
SET [LOCAL] ROLE alice;
- After processing Alice's request, the session resets the role back to the "authenticator" role by either issuing a "RESET ROLE" or ending the "local" transaction.
This approach works well in theory, but poses a significant security concern:
RESET ROLE allows a client to reset the role back to the "authenticator" role, *before* handing the session back to the pooler. Any SQL injection vulnerability or anything else that allows arbitrary SQL allows the client to issue a `RESET ROLE; SET ROLE anybody_else;`, bypassing authentication. Depending on the privileges of the "authenticator" role, the client can become any other user, or worse.
Proposal: What if PostgreSQL had a "role sandbox", a state where RESET ROLE was prohibited or restricted? If PostgreSQL could guarantee that RESET ROLE was not allowed, even SQL injection vulnerabilities would not allow a client to bypass database privileges and RLS when using user impersonation. Systems with many roles could safely and efficiently use many roles in parallel with connection pooling. The feature probably has other applications as well.
Sandboxing could happen at the session level, or the transaction level; both seem to have benefits. Here are some syntax ideas floating around:
SET ROLE IDEAS
a) Transaction ("local") Sandbox:
- SET LOCAL ROLE alice NO RESET;
- SET LOCAL ROLE alice WITHOUT RESET;
- BEGIN AS ROLE alice;
- 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 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.
Another aspect of this is SET SESSION AUTHORIZATION. I don't see preventing reset as particularly useful at least for connection poolers, since it then couldn't be reused. However, the GUARDED BY token idea would make it restricted but not prevented, which could be useful.
I'd love to hear your thoughts on this feature. If we can finalize the design, I would be willing to try implementing this. I haven't coded C for years though so I will probably need some help depending on how complex it is. SET ROLE is intertwined with the rest of the SET variable grammar but doesn't seem too hard to extend, if we go that route. Steve Chavez of PostgREST said he'd be willing to help, and could use the feature in PostgREST if it existed. I think other poolers could benefit from it as well.
Thanks,
Eric
Eric
pgsql-hackers by date: