Hi, I’ve just stumbled upon this patch and thread and thought I could share an idea of adding an optional temporary secret to SET SESSION AUTHORIZATION so that it is only possible to RESET SESSION AUTHORIZATION by providing the same secret ,like:
SET SESSION AUTHORIZATION [role] GUARDED BY ‘[secret]’; ... RESET SESSION AUTHORIZATION WITH ‘[secret]’; The use case is: I have a set of Liquibase scripts I would like to execute as a different role each and make sure they cannot escape the sandbox. As I am not a Postgres hacker I wonder how difficult to implement it might be… Thanks, Michal > On 23 Jun 2023, at 00:39, Joseph Koshakow <kosh...@gmail.com> wrote: > > > > On Wed, Jun 21, 2023 at 11:48 PM Nathan Bossart <nathandboss...@gmail.com > <mailto:nathandboss...@gmail.com>> wrote: > > > > On Wed, Jun 21, 2023 at 04:28:43PM -0400, Joseph Koshakow wrote: > > > + roleTup = SearchSysCache1(AUTHOID, > > ObjectIdGetDatum(AuthenticatedUserId)); > > > + if (!HeapTupleIsValid(roleTup)) > > > + ereport(FATAL, > > > + > > (errcode(ERRCODE_INVALID_AUTHORIZATION_SPECIFICATION), > > > + errmsg("role with OID %u > > does not exist", AuthenticatedUserId))); > > > + rform = (Form_pg_authid) GETSTRUCT(roleTup); > > > > I think "superuser_arg(AuthenticatedUserId)" would work here. > > Yep, that worked. I've attached a patch with this change. > > > I see that RESET SESSION AUTHORIZATION > > with a concurrently dropped role will FATAL with your patch but succeed > > without it, which could be part of the reason. > > That might be a good change? If the original authenticated role ID no > longer exists then we may want to return an error when trying to set > your session authorization to that role. > > Thanks, > Joe Koshakow > <v2-0001-Prevent-non-superusers-from-altering-session-auth.patch>