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: Tom Lane <tgl@sss.pgh.pa.us>, Alvaro Herrera <alvherre@alvh.no-ip.org>, Mark Dilger <mark.dilger@enterprisedb.com>, walther@technowledgy.de, "pgsql-hackers@postgresql.org" <pgsql-hackers@postgresql.org>
Date: 2025-05-05T12:35:15Z
Lists: pgsql-hackers 
On Thu, May 1, 2025 at 2:22 PM Robert Haas <robertmhaas@gmail.com> wrote:
> > The other approach would be to do what we do for the role options and just specify everything explicitly in the dump.  The GUC is only a default specifier so let's not leave room for defaults in the dump file.
>
> +1 for considering that option, although I am not sure which way is better.

Actually, on further reflection, this doesn't work, right? I mean,
role properties are things where you mention them when creating or
altering the role, like SUPERUSER or NOSUPERUSER. So you can always
specify all of them and thereby avoid relying on defaults. But that
doesn't work here, because in this case we're talking about whether or
not a completely separate object, namely a GRANT, gets created or not.
createrole_self_grant causes that to happen, and there's nothing you
can say as part of the CREATE ROLE command itself to make it not
happen. So it seems like the only real fix is to do as Tom proposes:

# But don't we need to add
# createrole_self_grant to the set of GUCs that pg_dump[all]
# resets in the emitted SQL?

-- 
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.