Re: role self-revocation

On Sun, Mar 6, 2022 at 11:53 AM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Really?
>
> regression=# grant admin to joe;
> GRANT ROLE
> regression=# grant admin to sally;
> GRANT ROLE
> regression=# \c - joe
> You are now connected to database "regression" as user "joe".
> regression=> revoke admin from sally;
> ERROR: must have admin option on role "admin"
> regression=> set role admin;
> SET
> regression=> revoke admin from sally;
> ERROR: must have admin option on role "admin"

Oops. I stand corrected.

> I think there is an issue here around exactly what the admin option
> means, but if it doesn't grant you the ability to remove grants
> made by other people, it's pretty hard to see what it's for.

Hmm. I think the real issue is what David Johnson calls the session
user exception. I hadn't quite understood how that played into this.
According to the documentation: "If WITH ADMIN OPTION is specified,
the member can in turn grant membership in the role to others, and
revoke membership in the role as well. Without the admin option,
ordinary users cannot do that. 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."

Is there some use case for the behavior described in that last
sentence? If that exception is the only case in which an unprivileged
user can revoke a grant made by someone else, then getting rid of it
seems pretty appealing from where I sit. I can't speak to the
standards compliance end of things, but it doesn't intrinsically seem
bothersome that having "WITH ADMIN OPTION" on a role lets you control
who has membership in said role. And certainly it's not bothersome
that the superuser can change whatever they want. The problem here is
just that a user with NO special privileges on any role, including
their own, can make changes that more privileged users might not like.

--
Robert Haas
EDB: http://www.enterprisedb.com