[ please keep the list cc'd ]
Dominique Devienne <ddevienne@gmail.com> writes:
> On Wed, Jan 5, 2022 at 7:26 PM Tom Lane <tgl@sss.pgh.pa.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