Re: role self-revocation

On Mon, Mar 7, 2022 at 11:04 AM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> > Is there some use case for the behavior described in that last
> > sentence?
>
> Good question. You might try figuring out when that text was added
> and then see if there's relevant discussion in the archives.

Apparently the permission used to be broader, and commit
fea164a72a7bfd50d77ba5fb418d357f8f2bb7d0 (February 2014, Noah,
CVE-2014-0060) restricted it by requiring that (a) the user had to be
the logged-in user, rather than an identity assumed via SET ROLE (so
maybe my bogus example from before would have worked in 2013) and (b)
that we're not in a security-restricted operation at the time.

Interestingly, it appears to me that the behavior wasn't documented
prior to that commit. The previous text read simply:

If <literal>WITH ADMIN OPTION</literal> 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.

That doesn't give any hint that self-administration is a special case.

I reviewed the (private) discussion of this vulnerability on the
pgsql-security mailing list where various approaches were considered.
I think it's safe to share a few broad details about that conversation
publicly now, since it was many years ago and the fix has long since
been published. There was discussion of making this
self-administration behavior something that could be turned off, but
such a change was deemed too large for the back-branches. There was no
discussion that I could find about removing the behavior altogether.
It was noted that having a special case for this was different than
granting WITH ADMIN OPTION because WITH ADMIN OPTION is inherited and
being logged in as a certain user is not.

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?

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