Proposal: Role Sandboxing for Secure Impersonation

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. 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

[1]
https://postgr.es/m/flat/CACA6kxgdzt-oForijaxfXHHhnZ1WBoVGMXVwFrJqUu-Hg3C-jA%40mail.gmail.com