Re: Proposal: Role Sandboxing for Secure Impersonation

On 12/9/24 18:27, Eric Hanson wrote:
> On Thu, Dec 5, 2024 at 4:29 PM Jelte Fennema-Nio <postgres@jeltef.nl> wrote:
>> When PgBouncer is in transaction mode, the server connection will only
>> be unlinked, when PgBouncer receives a ReadyForQuery with the "idle"
>> flag from the server **and** there are no messages from the client in
>> flight anymore. It's totally valid for a client to send multiple
>> transactions in a single pipeline without waiting for their result.
>>
>>> So
>>> guarded/unresettable transactions are not at all helpful for security
>>> in pgbouncer?
>>
>> Correct.
>>
>>> Is this generally true for others?
>>
>> I'm not sure whether all poolers implement this correctly (pgbouncer
>> definitely had some recent bugs in this area), but none that I know
>> parse the COMMIT message. So they will happily forward commands to the
>> server after the COMMIT is sent if they haven't received a
>> ReadyForQuery with "idle" back yet.
> 
> Got it.
> 
> Would you agree that pipelines and connection pooling are somewhat
> orthogonal anyway?  At least in the abstract.  One can pool
> connections without pipelining and still at least avoid the
> max_connections bottleneck.  I would think that, had guarded
> sessions/transactions existed when PgBouncer was invented, they might
> have added another config mode that pooled and reused the
> authenticator role for multiple client roles, but somehow some way
> made sure that client requests couldn't spill over after the COMMIT.
> Reasonable?
> 

I'm not sure those features really are entirely orthogonal. Sure, you
could "disable" pipelining, but that could significantly limit
throughput, causing the connection to pile on ...


The pooler is meant to be transparent, and we don't start transactions
when connected directly to the DB, so this would surprise users.

Or what if the user really wants to do something that can't happen in a
transaction, like CREATE INDEX CONCURRENTLY? Or what if the user wants
to start a transaction with other features (e.g. read-only or a
different isolation level)?

It'd also interfere with procedures, which can commit/rollback and chain
transactions, but only when not in an explicit transaction. If the
pooler starts wrapping everything in a transaction, this would break.

Not sure if this might cause some issues with "idle in transaction"
sessions. Maybe not ...

So I don't think the pooler should be starting transactions, unless the
user actually started a transaction.



regards

-- 
Tomas Vondra