Re: fixing CREATEROLE

On Tue, May 20, 2025 at 2:32 PM Robert Haas <robertmhaas(at)gmail(dot)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