Re: role self-revocation

Greetings,

* Robert Haas (robertmhaas(at)gmail(dot)com) wrote:
> On Sun, Mar 6, 2022 at 11:34 AM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> > I was thinking the former ... however, after a bit of experimentation
> > I see that we accept "grant foo to bar granted by baz" a VERY long
> > way back, but the "granted by" option for object privileges is
> > (a) pretty new and (b) apparently restrictively implemented:
> >
> > regression=# grant delete on alices_table to bob granted by alice;
> > ERROR: grantor must be current user
> >
> > That's ... surprising. I guess whoever put that in was only
> > interested in pro-forma SQL syntax compliance and not in making
> > a usable feature.
>
> It appears so: https://www.postgresql.org/message-id/2073b6a9-7f79-5a00-5f26-cd19589a52c7%402ndquadrant.com
>
> It doesn't seem like that would be hard to fix. Maybe we should just do that.

Yeah, that seems like something that should be fixed. Superusers should
be allowed to set GRANTED BY to whatever they feel like, and I'd argue
that a role who wants a GRANT to actually be GRANTED BY some other role
they're a member of should also be allowed to (as they could anyway by
doing a SET ROLE), provided that role also has the privileges to do the
GRANT itself, of course.

> > So if we decide to extend this change into object privileges
> > it would be advisable to use SET ROLE, else we'd be giving up
> > an awful lot of backwards compatibility in dump scripts.
> > But if we're only talking about role grants then I think
> > GRANTED BY would work fine.
>
> OK.

I'm not quite following this bit. Where would SET ROLE come into play
when we're talking about old dump scripts and how the commands in those
scripts might be interpreted by newer versions of PG..?

Thanks,

Stephen