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