Re: Preventing non-superusers from altering session authorization

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 <koshy44(at)gmail(dot)com> wrote:
>
>
>
> On Wed, Jun 21, 2023 at 11:48 PM Nathan Bossart <nathandbossart(at)gmail(dot)com <mailto:nathandbossart(at)gmail(dot)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>