On Sun, Mar 6, 2022 at 11:53 AM Tom Lane <t...@sss.pgh.pa.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