From: Tom Lane <tgl@sss.pgh.pa.us>
To: "David G. Johnston" <david.g.johnston@gmail.com>
cc: Robert Haas <robertmhaas@gmail.com>,
        Mark Dilger <mark.dilger@enterprisedb.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: <CAKFQuwZymJkK1yp_KqxypNFOWK7GXaP1iRe6JD2qygeMubkdxw@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> <CA+Tgmob!
 9+PLuhP2Bqfr9rde9_C+xoSxj6HyKm9s6nQ7syfJKPw@mail.gmail.com> <548671.1646861460@sss.pgh.pa.us> <CAKFQuwZymJkK1yp_KqxypNFOWK7GXaP1iRe6JD2qygeMubkdxw@mail.gmail.com>
Comments: In-reply-to "David G. Johnston" <david.g.johnston@gmail.com>
	message dated "Wed, 09 Mar 2022 15:00:51 -0700"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <554133.1646865318.1@sss.pgh.pa.us>
Date: Wed, 09 Mar 2022 17:35:18 -0500
Message-ID: <554134.1646865318@sss.pgh.pa.us>
Archived-At: <https://www.postgresql.org/message-id/554134.1646865318%40sss.pgh.pa.us>
Precedence: bulk

"David G. Johnston" <david.g.johnston@gmail.com> writes:
> So CREATE ROLE will assign ownership of AND membership in the newly created
> role to the session_user

I would NOT have it automatically assign membership in the new role,
even though the SQL spec says so.  We've not done that historically
and it doesn't seem desirable.  In particular, it's *really* not
desirable for a user (role with LOGIN).

> I'm fine with this.  It does introduce an OWNER concept to roles and so at
> minimum we need to add:
> ALTER ROLE foo OWNER TO { new_owner | CURRENT_ROLE | CURRENT_USER |
> SESSION_USER }

Agreed.

			regards, tom lane