Horton

Re: fixing CREATEROLE

David G. Johnston <david.g.johnston@gmail.com>

View thread View on postgr.es
From: "David G. Johnston" <david.g.johnston@gmail.com>
To: Robert Haas <robertmhaas@gmail.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>, Mark Dilger <mark.dilger@enterprisedb.com>, "pgsql-hackers@postgresql.org" <pgsql-hackers@postgresql.org>
Date: 2022-11-23T21:19:20Z
Lists: pgsql-hackers 
On Wed, Nov 23, 2022 at 2:01 PM Robert Haas <robertmhaas@gmail.com> wrote:


> In the latter case there are two, one with
>
> grantor=bootstrap_supeuser/admin_option=true/set_option=false/inherit_option=false
> and a second with
> grantor=alice/admin_option=false/set_option=true/inherit_option=true.
>

This, IMO, is preferable.  And I'd probably typically want to hide the
first grant from the user in typical cases - it is an implementation detail.

We have to grant the creating role membership in the created role, with
admin option, as a form of bookkeeping.

If the creating role really wants to be a member of the created role for
other reasons that should be done explicitly and granted by the creating
role.

This patch series need not be concerned about how easy or difficult it is
to get the additional grant entry into the database.  The ability to refine
the permissions in the data model is there so there should be no complaints
that "it is impossible to set up this combination of permissions".  We've
provided a detailed model and commands to alter it - the users can build
their scripts to glue those things together.

David J.

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.