Re: role self-revocation - Mailing list pgsql-hackers

Robert Haas <robertmhaas@gmail.com> writes:
> 1. What should be the exact rule for whether A can remove a grant made
> by B? Is it has_privs_of_role()? is_member_of_role()? Something else?

No strong opinion here, but I'd lean slightly to the more restrictive
option.

> 2. What happens if the same GRANT is enacted by multiple users? For
> example, suppose peon does "GRANT peon to boss" and then the superuser
> does the same thing afterwards, or vice versa? One design would be to
> try to track those as two separate grants, but I'm not sure if we want
> to add that much complexity, since that's not how we do it now and it
> would, for example, implicate the choice of PK on the pg_auth_members
> table.

As you note later, we *do* track such grants separately in ordinary
ACLs, and I believe this is clearly required by the SQL spec.
It says (for privileges on objects):

    Each privilege is represented by a privilege descriptor.
    A privilege descriptor contains:
    — The identification of the object on which the privilege is granted.
    — The <authorization identifier> of the grantor of the privilege.
      ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
    — The <authorization identifier> of the grantee of the privilege.
    — Identification of the action that the privilege allows.
    — An indication of whether or not the privilege is grantable.
    — An indication of whether or not the privilege has the WITH HIERARCHY OPTION specified.

Further down (4.42.3 in SQL:2021), the granting of roles is described,
and that says:

    Each role authorization is described by a role authorization descriptor.
    A role authorization descriptor includes:
    — The role name of the role.
    — The authorization identifier of the grantor.
      ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
    — The authorization identifier of the grantee.
    — An indication of whether or not the role authorization is grantable.

If we are not tracking the grantors of role authorizations,
then we are doing it wrong and we ought to fix that.

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

> 4. Should we apply this rule to other types of grants, rather than
> just to role membership?

I am not sure about the reasoning behind the existing rule that
superuser-granted privileges are recorded as being granted by the
object owner.  It does feel more like a wart than something we want.
It might have been a hack to deal with the lack of GRANTED BY
options in GRANT/REVOKE back in the day.

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.

> Please note that it is not really my intention to try to shove
> anything into v15 here.

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.

            regards, tom lane