Re: Feature bug dumpall CREATE ROLE postgres

On Tue, Feb 27, 2024 at 3:12 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Jim Wilson <jim(at)wreath(dot)com> writes:
> > Including "CREATE ROLE postgres;" in the dumpall will cause the script to
> > throw an error.
>
> I believe the reason pg_dumpall does that is to avoid worse failures
> if the target installation has a different bootstrap superuser name
> than the source did. One error is easier to ignore than hundreds
> of 'em.
>
> We could avoid that problem if we wanted to invent and use CREATE
> OR REPLACE ROLE, but that would have downsides of its own, such as
> silently overwriting the properties of any roles that already exist
> in the target (IOW, the cases where you *want* to get that error).
>
> Maybe it'd work to invent C.O.R.R. but only use it for the bootstrap
> superuser, with plain CREATE ROLE for the rest. Haven't really
> thought through the consequences of that.
>
> regards, tom lane
>

Perhaps throw a warning instead of an exception if the user name matches
the current user that the CREATE ROLE command is operating under AND the
current user is also flagged SUPERUSER could work (ie limited C.R.O.R. like
functionality). I don't know if that fits with how postgreSQL code works.
My workaround in linux comments out the specific CR command:

pg_dumpall | sed -e 's/CREATE ROLE postgres/--CREATE ROLE postgres/'
>/mnt/temp_vol/dumpall.sql

It isn't very intuitive that the CREATE ROLE for the bootstrap SUPERUSER is
included in the script considering the primary use of the pg_dumpall
command. I suspect that eliminating it from pg_dumpall as in my work
around, or ignoring it during restore as mentioned above would be equally
(or more) intuitive.

Of course it comes down to "problems" with our own data, but in the case of
doing full restores during major version upgrades things go much smoother
to run the restore with ON_ERROR_STOP turned on.

Thanks,
Jim Wilson