Re: role self-revocation

On Fri, Mar 4, 2022 at 4:34 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> If we are not tracking the grantors of role authorizations,
> then we are doing it wrong and we ought to fix that.

Hmm, so maybe that's the place to start. We are tracking it in the
sense that we record an OID in the catalog, but nothing that happens
after that makes a lot of sense.

> > 3. What happens if a user is dropped after being recorded as a
> > grantor?
>
> Should work the same as it does now for ordinary ACLs, ie, you
> gotta drop the grant first.

OK, that makes sense to me.

> Changing it could have some bad compatibility consequences though.
> In particular, I believe it would break existing pg_dump files,
> in that after restore all privileges would be attributed to the
> restoring superuser, and there'd be no very easy way to clean that
> up.

I kind of wonder whether we ought to attribute all privileges granted
by any superuser to the bootstrap superuser. That doesn't seem to have
any meaningful downside, and it could avoid a lot of annoying
dependencies that serve no real purpose.

> Agreed, this is not something to move on quickly. We might want
> to think about adjusting pg_dump to use explicit GRANTED BY
> options in GRANT/REVOKE a release or two before making incompatible
> changes.

Uggh. I really want to make some meaningful progress here before the
heat death of the universe, and I'm not sure that this manner of
proceeding is really going in that direction. That said, I do entirely
see your point. Are you thinking we'd actually add a GRANTED BY clause
to GRANT/REVOKE, vs. just wrapping it in SET ROLE incantations of some
sort?

--
Robert Haas
EDB: http://www.enterprisedb.com