Re: Proposal: Role Sandboxing for Secure Impersonation

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