Horton

Re: fixing CREATEROLE

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

View thread View on postgr.es
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>, "pgsql-hackers@postgresql.org" <pgsql-hackers@postgresql.org>
Date: 2022-11-23T21:40:44Z
Lists: pgsql-hackers 
"David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Wed, Nov 23, 2022 at 2:18 PM Robert Haas <robertmhaas@gmail.com> wrote:
>> Either way, I'm not quite sure what the benefit of converting these
>> things to predefined roles is.

> Specifically, you gain inheritance/set and "admin option" for free.

Right: the practical issue with CREATEROLE/CREATEDB is that you need
some mechanism for managing who can grant those privileges.  The
current answer isn't very flexible, which has been complained of
repeatedly.  If they become predefined roles then we get a lot of
already-built-out infrastructure to solve that, instead of having to
write even more single-purpose logic.  I think it's a sensible future
path, but said lack of flexibility hasn't yet spurred anyone to do it.

			regards, tom lane

Commits

  1. Add new GUC createrole_self_grant.

  2. Restrict the privileges of CREATEROLE users.

  3. Pass down current user ID to AddRoleMems and DelRoleMems.

  4. Refactor permissions-checking for role grants.

  5. Improve documentation of the CREATEROLE attibute.

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