Re: role self-revocation

On Fri, Mar 4, 2022 at 1:50 PM Robert Haas <robertmhaas(at)gmail(dot)com> wrote:

> On Mon, Feb 28, 2022 at 2:09 PM Stephen Frost <sfrost(at)snowman(dot)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
>
>
The wording for this example is hurting my brain.
GRANT admin TO joe;
\c admin
REVOKE admin FROM joe;

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

I think I disagree. Or, at least, the superuser has full control of
dictating how role membership is modified and that seems sufficient.

The example above works because of:

"A role is not considered to hold WITH ADMIN OPTION on itself, but it may
grant or revoke membership in itself from a database session where the
session user matches the role."

If a superuser doesn't want "admin" to modify its own membership then they
can prevent anyone but a superuser from being able to have a session_user
of "admin". If that happens then the only way a non-superuser can modify
group membership is by being added to said group WITH ADMIN OPTION.

Now, if two people and a superuser are all doing membership management on
the same group, and we want to add permission checks and multiple grants as
tools, instead of having them just communicate with each other, then by all
means let us do so. In that case, in answer to questions 2 and 3, we
should indeed track which session_user made the grant and only allow the
same session_user or the superuser to revoke it (we'd want to stop
"ignoring" the GRANTED BY clause of REVOKE ROLE FROM so the superuser at
least could remove grants made via WITH ADMIN OPTION).

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.
>

Yes, this particular choice seems unfortunate, but also not something that
I think it is necessarily mandatory for us to improve. If the accountant
is the owner then yes they get to decide permissions. In the presence of
an auditor role either you trust the accountant role to keep the
permissions in place or you define a superior authority to both the auditor
and accountant to be the owner. Or let the superuser manage everything by
witholding login and WITH ADMIN OPTION privileges from the ownership role.

If we do extend role membership tracking I suppose the design question is
whether the new role grantor dependency tracking will have a superuser be
the recorded grantor instead of some owner. Given that roles don't
presently have an owner concept, consistency with existing permissions in
this manner would be trickier. Because of this, I would probably leave
role grantor tracking at the session_user level while database objects
continue to emanate from the object owner. The global vs database
differences seem like a sufficient theoretical justification for the
difference in implementation.

David J.