On Thu, 5 Dec 2024 at 16:35, Eric Hanson <e...@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.
