From: Tom Lane <tgl@sss.pgh.pa.us>
To: Robert Haas <robertmhaas@gmail.com>
cc: Mark Dilger <mark.dilger@enterprisedb.com>,
        "David G. Johnston" <david.g.johnston@gmail.com>,
        Stephen Frost <sfrost@snowman.net>,
        Joshua Brindle <joshua.brindle@crunchydata.com>,
        Andrew Dunstan <andrew@dunslane.net>,
        PostgreSQL-development <pgsql-hackers@postgresql.org>
Subject: Re: role self-revocation
In-reply-to: <CA+TgmobgeK0JraOwQVPqhSXcfBdFitXSomoebHMMMhmJ4gLonw@mail.gmail.com>
References: <CAGB+Vh4Qb4+5zrJ2pJio6rxy2+hnt2zhTmonxgzfF3wY4H6WRw@mail.gmail.com> <CA+Tgmob_rJtHdjMOqiOcGtk0vU6XXjOqQ0LSvVpUJjvdY341rw@mail.gmail.com> <20220228190923.GJ10577@tamriel.snowman.net> <CA+TgmobNfJvG6keNU6YcykJnntDm_-BxN_5JDacakusr5E0hEg@mail.gmail.com> <CAKFQuwb3=Zdp2-mEPWbTmktt3NLRNdprGi7=DqfG-pxpTQo43w@mail.gmail.com> <CA+TgmoY9SLMEpCcVFw9gs-9_ihK+3T7P0_4zkMDfXk8DfvheYA@mail.gmail.com> <5984.1646585629@sss.pgh.pa.us> <CA+TgmoaARcSQzzhGpX2z5C-xb3DAr0SHDMPHqhh1VHCRHFm0WA@mail.gmail.com> <195958.1646669050@sss.pgh.pa.us> <CA+TgmoZPUnhez=TxeUceJV2Xu2Gg35MwYzyC9QgdPDKsvxyUyg@mail.gmail.com> <208419.1646677694@sss.pgh.pa.us> <B0994E32-CD22-4AB2-A600-4A4B0B535F88@enterprisedb.com> <CA+TgmoY8wkrFXw3dPO+NDvqazutHF6x3UQcy-eAjLgxoj8xN=A@mail.gmail.com> <F9EB1DD2-5739-4954-AC16-372E289A55DD@enterprisedb.com> <260619.1646684169@sss.pgh.pa.us> <72EF0179-D0DD-490C-812B-D64BEC5C72D1@enterprisedb.com> <CA+TgmobgeK0JraOwQVPqhSXcfBdFitXSomoebHMMMhmJ4gLonw@mail.gmail.com>
Comments: In-reply-to Robert Haas <robertmhaas@gmail.com>
	message dated "Wed, 09 Mar 2022 15:51:10 -0500"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <546157.1646859700.1@sss.pgh.pa.us>
Date: Wed, 09 Mar 2022 16:01:40 -0500
Message-ID: <546158.1646859700@sss.pgh.pa.us>
Archived-At: <https://www.postgresql.org/message-id/546158.1646859700%40sss.pgh.pa.us>
Precedence: bulk

Robert Haas <robertmhaas@gmail.com> writes:
> On Mar 7, 2022, at 12:16 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> tgl> Having said that, one thing that I find fishy is that it's not clear
> tgl> where the admin privilege for a role originates.  After "CREATE ROLE
> tgl> alice", alice has no members, therefore none that have admin privilege,
> tgl> therefore the only way that the first member could be added is via
> tgl> superuser deus ex machina.  This does not seem clean.

> I agree with that, but I don't think it's a sufficient reason for
> keeping the self-admin exception, because the same problem exists for
> non-login roles. I don't even think it's the right idea conceptually
> to suppose that the power to administer a role originates from the
> role itself.

Actually, that's the same thing I was trying to say.  But if it doesn't
originate from the role itself, where does it originate from?

> In my opinion, the right to
> administer a role - regardless of whether or not it is a login role -
> most naturally vests in the role that created it, or something in that
> direction at least, if not that exact thing.

This seems like a reasonable answer to me too: the creating role has admin
option implicitly, and can then choose to grant that to other roles.
Obviously some work needs to be done to make that happen (and we should
see whether the SQL spec has some different idea).

			regards, tom lane