Horton

Re: fixing CREATEROLE

Robert Haas <robertmhaas@gmail.com>

View thread View on postgr.es
From: Robert Haas <robertmhaas@gmail.com>
To: "David G. Johnston" <david.g.johnston@gmail.com>
Cc: Mark Dilger <mark.dilger@enterprisedb.com>, walther@technowledgy.de, Tom Lane <tgl@sss.pgh.pa.us>, "pgsql-hackers@postgresql.org" <pgsql-hackers@postgresql.org>
Date: 2022-12-02T14:47:25Z
Lists: pgsql-hackers

Attachments

On Mon, Nov 28, 2022 at 8:33 PM Robert Haas <robertmhaas@gmail.com> wrote:
> Hmm, that's an interesting alternative to what I actually implemented.
> Some people might like it better, because it puts the behavior fully
> under the control of the CREATEROLE user, which a number of you seem
> to favor.

Here's an updated patch set.

0001 adds more precise and extensive documentation for the current
(broken) state of affairs. I propose to back-patch this to all
supported branches. It also removes a <tip> suggesting that you should
use a CREATEDB & CREATEROLE role instead of a superuser, because that
is pretty pointless as things stand, and is too simplistic for the new
system that I'm proposing to put in place, too.

0002 and 0003 are refactoring, unchanged from v1.

0004 is the core fix to CREATEROLE. It has been updated from the
previous version with documentation and some bug fixes.

0005 adopts David's suggestion: instead of giving the superuser a way
to control the options on the implicit grant, give CREATEROLE users a
way to grant newly-created roles to themselves automatically. I made
this a GUC, which means that the person setting up the system could
configure a default in postgresql.conf, but a user who doesn't prefer
that default can also override it using ALTER ROLE .. SET or ~/.psqlrc
or whatever. This is simpler than what I had before, doesn't involve a
catalog change, makes it clear that the behavior is not
security-critical, and puts the decision fully in the hands of the
CREATEROLE user rather than being partly controlled by that user and
partly by the superuser. Hopefully that's an improvement.

Comments?

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

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.