Horton

Re: fixing CREATEROLE

Greg Sabino Mullane <htamfids@gmail.com>

View thread View on postgr.es
From: Greg Sabino Mullane <htamfids@gmail.com>
To: Robert Haas <robertmhaas@gmail.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>, 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-05-21T01:27:11Z
Lists: pgsql-hackers 
On Tue, May 20, 2025 at 2:32 PM Robert Haas <robertmhaas@gmail.com> wrote:

> trying to create a role that already exists. At this point, my head is
> already kind of exploding, because I thought we were pretty careful to
> try to make it so that pg_dump output can be restored without error even
> in the face of pre-existing objects like the public schema and
> the plpgsql language, but apparently we haven't applied the same principle
> to pg_dumpall.[1]
>

This has always been my understanding, even if we are not explicitly
stating it anywhere. pg_dump -> no errors. pg_dumpall -> always at least
one error :)

But if, as you posit above, we were to try running the output of pg_dumpall
> through psql as a non-superuser, the problem is a whole lot
> worse.


I'm of the camp that pg_dumpall should almost always be run as superuser.
That said, I find myself using pg_dumpall less and less with every year,
and cannot think of the last time I advised a client to use it (other than
a pg_dumpall --globals and ignore the errors as a poor-man's role
duplication system. Even that is getting rarer, as we generally don't want
the same passwords)

Cheers,
Greg

--
Crunchy Data - https://www.crunchydata.com
Enterprise Postgres Software Products & Tech Support

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.