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: Robert Haas <robertmhaas@gmail.com>
Cc: Alvaro Herrera <alvherre@alvh.no-ip.org>, "David G. Johnston" <david.g.johnston@gmail.com>, Mark Dilger <mark.dilger@enterprisedb.com>, walther@technowledgy.de, "pgsql-hackers@postgresql.org" <pgsql-hackers@postgresql.org>
Date: 2025-04-30T20:29:13Z
Lists: pgsql-hackers 
necro-ing an old thread ...

Robert Haas <robertmhaas@gmail.com> writes:
> [ v4-0002-Add-new-GUC-createrole_self_grant.patch ]

I confess to not having paid close enough attention when
these patches went in, or I would have complained about
createrole_self_grant.  It changes the user-visible behavior
of SQL commands, specifically CREATE ROLE.  We have learned
over and over again that GUCs that do that are generally
a bad idea.

Two years later, it's perhaps too late to take it out again.
However, I'd at least like to complain about the fact that
it breaks pg_dumpall, which is surely not expecting anything
but the default behavior.  If for any reason the restore is
run under a non-default setting of createrole_self_grant,
there's a potential of creating role grants that were not
there in the source database.  Admittedly the damage is
probably limited by the fact that it only applies if the
restoring user has CREATEROLE but not SUPERUSER, which
I imagine is a rare case.  But don't we need to add
createrole_self_grant to the set of GUCs that pg_dump[all]
resets in the emitted SQL?

			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.