Re: Trouble DROP'ing a ROLE, despite REVOKE'ing its privileges

[ please keep the list cc'd ]

Dominique Devienne <ddevienne(at)gmail(dot)com> writes:
> On Wed, Jan 5, 2022 at 7:26 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> That will only revoke privileges that were granted by the role
>> doing the REVOKE, so I surmise that you did this as some other role.

> Hmmm, that's interesting... But it brings up something I didn't think of.
> The point of making a NOLOGIN ROLE for the owner of all DB objects,
> is to be able to GRANT that ROLE to several LOGIN USERs, which are
> all basicall Administrators of that system. So any one of them should be
> able to DROP the whole shebang, but what you are saying seems to be
> that only the admin that *originally* created the "shebang" can get rid of
> "owner" ROLE, or a SUPERUSER of course. Is that correct?

The admins would need to be careful to SET ROLE to the shared role
when manipulating privileges granted by that role --- either to
grant them, or to revoke them.

Alternatively, they could be careful to specify "GRANTED BY shared_role"
in all these commands. But I think our coverage for that option isn't
complete, especially in older branches.

(IIRC there is some hack that causes superuser-issued revokes
to nuke privileges granted by the object's owner, even without an
explicit GRANTED BY. But that doesn't apply to ordinary users.)

regards, tom lane