Re: Proposal: Role Sandboxing for Secure Impersonation - Mailing list pgsql-hackers

On 12/2/24 08:41, Eric Hanson wrote:
> 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.
> 
> 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;
> 
> 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.
> 
> 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.


I am very much in favor of functionality of this sort being built in to 
the core database. Very similar functionality is available in an 
extension I wrote years ago (without the SQL grammar support) -- see 
https://github.com/pgaudit/set_user

I have never proposed it (or maybe I did years ago, don't actually 
remember) because I did not think the community was interested in this 
approach, but perhaps the time is ripe to discuss it.

-- 
Joe Conway
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com