Re: Proposal: Role Sandboxing for Secure Impersonation

On Thu, 5 Dec 2024 at 16:35, Eric Hanson <eric@aquameta.com> wrote:
> When pgbouncer is in transaction mode, the pipeline doesn't stop when
> the transaction ends?  Mayhaps I have the common misunderstanding.

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.

> > P.S. If we're going to move forward in this direction, then SET
> >
> > SESSION AUTHORIZATION should have the same functionality. Poolers
> > probably would want to lock that instead of ROLE, that way users can
> > still use SET ROLE to change the role that the current SESSION
> > AUTHORIZATION is allowed to change to.
>
> "should have" for consistency and general usefulness?  At least for
> poolers, this would require the authenticator role to be a superuser,
> which is scary to me but maybe people do it.  But as far as bringing
> sandboxing to PostgreSQL in general, I see the point.

Hmm, I didn't realize that SET SESSION AUTHORIZATION required
superuser. I had expected you could set it to any roles that you are
part of. That seems like a fixable problem at least, we could add some
new role that would allow that, like pg_session_authorization.