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-07T18:28:14Z
Lists: pgsql-hackers 
Robert Haas <robertmhaas@gmail.com> writes:
> It appears to me that the actual behavior of having is_admin_of_role()
> return true when member == role dates to
> f9fd1764615ed5d85fab703b0ffb0c323fe7dfd5 (Tom Lane, 2005). If I'm not
> reading this code wrong, prior to that commit, it seems to me that we
> only searched the roles that were members of that role, directly or
> indirectly, and you had to have admin_option on the last hop of the
> membership chain in order to get a "true" result. But that commit,
> among other changes, made member == role a special case, but the
> comment just says /* Fast path for simple case */ which makes it
> appear that it wasn't thought to be a behavior change at all, but it
> looks to me like it was. Am I confused?

Ugh, I think you are right.  It's been a long time of course, but it sure
looks like that was copied-and-pasted without recognizing that it was
wrong in this function because of the need to check the admin_option flag.
And then in the later security discussion we didn't realize that the
problematic behavior was a flat-out thinko, so we narrowed it as much as
we could instead of just taking it out.

Does anything interesting break if you do just take it out?

			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