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
