Horton

Re: role self-revocation

Tom Lane <tgl@sss.pgh.pa.us>

View thread View on postgr.es
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Robert Haas <robertmhaas@gmail.com>
Cc: "David G. Johnston" <david.g.johnston@gmail.com>, Stephen Frost <sfrost@snowman.net>, Joshua Brindle <joshua.brindle@crunchydata.com>, Mark Dilger <mark.dilger@enterprisedb.com>, Andrew Dunstan <andrew@dunslane.net>, PostgreSQL-development <pgsql-hackers@postgresql.org>
Date: 2022-03-06T16:53:49Z
Lists: pgsql-hackers 
Robert Haas <robertmhaas@gmail.com> writes:
> ... Suppose the superuser grants "admin" to both "joe" and "sally".
> Now "joe" can SET ROLE to "admin" and revoke it from "sally", and the
> superuser has no tool to prevent this.

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"

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.

			regards, tom lane

Commits

  1. Make role grant system more consistent with other privileges.

  2. Ensure that pg_auth_members.grantor is always valid.

  3. Remove the ability of a role to administer itself.

  4. Add tests of the CREATEROLE attribute

  5. Replace explicit PIN entries in pg_depend with an OID range test.

  6. Shore up ADMIN OPTION restrictions.

  7. Add pg_has_role() family of privilege inquiry functions modeled after the

  8. Align GRANT/REVOKE behavior more closely with the SQL spec, per discussion