On Wed, Dec 4, 2024 at 4:54 PM Jelte Fennema-Nio <postg...@jeltef.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
