Re: role self-revocation

On Mon, Feb 28, 2022 at 2:09 PM Stephen Frost <sfrost@snowman.net> wrote:
> The ability of a role to revoke itself from some other role is just
> something we need to accept as being a change that needs to be made, and
> I do believe that such a change is supported by the standard, in that a
> REVOKE will only work if you have the right to make it as the user who
> performed the GRANT in the first place.

First, a quick overview of the
issue for those who have not followed the earlier threads in their
grueling entirety:

rhaas=# create user boss;
CREATE ROLE
rhaas=# create user peon;
CREATE ROLE
rhaas=# grant peon to boss;
GRANT ROLE
rhaas=# \c - peon
You are now connected to database "rhaas" as user "peon".
rhaas=> revoke peon from boss; -- i don't like being bossed around!
REVOKE ROLE

I argue (and Stephen seems to agree) that the peon shouldn't be able
to undo the superuser's GRANT.

4. Should we apply this rule to other types of grants, rather than
just to role membership? Why or why not? Consider this:

The fact that the accountant may not be not in favor
of the auditor seeing what the accountant is doing with the money is
precisely the reason why we have auditors.

However, when the superuser
performs the GRANT as in the above example, the grantor is recorded as
the table owner, not the superuser! So if we really want role
membersip and other kinds of grants to behave in the same way, we have
our work cut out for us here.