Re: Proposal: Role Sandboxing for Secure Impersonation

On Wed, Dec 4, 2024 at 4:54 PM Jelte Fennema-Nio <postgres(at)jeltef(dot)nl> wrote:
>
> > I wouldn't oppose a command that does an absolutely irrevocable SET
> > ROLE -- i.e. once you execute it, it is as if you logged in as the
> > target role originally, and the only way to get your privileges back
> > is a new connection.
>
> Agreed, that seems fine to me.

Irrevocable SET ROLE would only help with poolers in transaction mode,
via SET LOCAL ROLE x NO RESET (or whatever), right? (I kind of like
the syntax `BEGIN AS ROLE alice`).

Irrevocably setting the session/connection's role (non-local) could be
generally useful but doesn't seem to help with poolers, as I think
others have mentioned.

>
> e) SET ROLE jelte WITH GUARD;
> -> returns a single row with a 'random-token-abc'
> RESET ROLE WITH TOKEN 'random-token-abc';

Whoa, letting PostgreSQL generate the token is great!

Is there any issue with this being a SET, since SET commands don't
typically return results? How would you call it from say plpgsql?

> a): This would not be usable by transaction poolers. Because the
> design seems to be based on a common misunderstanding of how
> transaction pooling works. PgBouncer does not parse the COMMIT, it
> only knows that a transaction is finished because postgres will tell
> it. Since poolers allow pipelining of messages for performance
> reasons, a client can trivially bypass this by quickly sending another
> command after the COMMIT message.

When pgbouncer is in transaction mode, the pipeline doesn't stop when
the transaction ends? Mayhaps I have the common misunderstanding. So
guarded/unresettable transactions are not at all helpful for security
in pgbouncer? Is this generally true for others?

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

Something along the lines of `SET [LOCAL] ROLE x WITH GUARD` fulfills
all my hopes and dreams. Probably should get out more. :)

Regards,
Eric