Re: Proposal: Role Sandboxing for Secure Impersonation - Mailing list pgsql-hackers

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?

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

I don't even know why SET SESSION AUTH exists.  To me, session auth is
the role that started the session, which is immutable.  The docs say

> Using this command, it is possible, for example, to temporarily become
> an unprivileged user and later switch back to being a superuser.

Sort of?  It's not really "unprivileged" when the client can just
reset it back to privileged.  There must be good reasons, but it seems
like another "we don't have a sandbox" seam.

Regards,
Eric