Feature bug dumpall CREATE ROLE postgres
I understand there are workarounds, but generally the software
automatically creates a postgres role during initialization.
Including "CREATE ROLE postgres;" in the dumpall will cause the script to
throw an error. This came to light while doing PostgreSQL upgrades. I'm
finding that turning on ON_ERROR_STOP makes sense to ensure that there
isn't some problem in our data or stored procedures that goes unnoticed
during upgrades. The fact that a "CREATE ROLE postgres" statement is
created in every dumpall means loading the script into a new server means
an error is thrown and execution stops.
Jim Wilson <jim@wreath.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
On Tue, Feb 27, 2024 at 1:12 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
Jim Wilson <jim@wreath.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.
Why are we catering to the 0.0001 percent here?
Put this in the dumpall file instead of a create role command:
pseudo-code
DO $$
if 10::oid::regrole != 'postgres' then
raise error 'bootstrap superuser is named postgres in source database'
raise error 'either rename your bootstrap superuser to postgres or create a
new superuser named postgres'
end if;
$$;
Or, any chance we can just write:
alter table tbl owner to 10::oid;
and not care what specific label the bootstrap superuser is using?
More simply I suppose is to have an option on pg_dumpall to omit the
bootstrap superuser create role command to at least avoid having people
have to modify the dump file after the fact.
David J.
On Tue, Feb 27, 2024 at 3:12 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
Jim Wilson <jim@wreath.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
On 2024-02-27 Tu 15:12, Tom Lane wrote:
Jim Wilson <jim@wreath.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.
What if we just skip creating the role if it's the bootstrap superuser
in the source and has the same name as the bootstrap superuser in the
destination, using some DO processing?
cheers
andrew
--
Andrew Dunstan
EDB: https://www.enterprisedb.com
Andrew Dunstan <andrew@dunslane.net> writes:
What if we just skip creating the role if it's the bootstrap superuser
in the source and has the same name as the bootstrap superuser in the
destination, using some DO processing?
Hmm ... pg_dump has never used any DO blocks in its output, and
I'm not sure it's a great idea to start. Seems like the kind of
decision we could regret down the road, given the possible need
for pg_restore to parse the output.
regards, tom lane
On 2024-02-28 We 17:36, Tom Lane wrote:
Andrew Dunstan <andrew@dunslane.net> writes:
What if we just skip creating the role if it's the bootstrap superuser
in the source and has the same name as the bootstrap superuser in the
destination, using some DO processing?Hmm ... pg_dump has never used any DO blocks in its output, and
I'm not sure it's a great idea to start. Seems like the kind of
decision we could regret down the road, given the possible need
for pg_restore to parse the output.
This is only for pg_dumpall. pg_restore shouldn't care.
cheers
andrew
--
Andrew Dunstan
EDB: https://www.enterprisedb.com
Andrew Dunstan <andrew@dunslane.net> writes:
On 2024-02-28 We 17:36, Tom Lane wrote:
Hmm ... pg_dump has never used any DO blocks in its output, and
I'm not sure it's a great idea to start. Seems like the kind of
decision we could regret down the road, given the possible need
for pg_restore to parse the output.
This is only for pg_dumpall. pg_restore shouldn't care.
Yeah, fair point. I've long thought that we should get pg_dumpall
to emit something more structured than "big SQL script", but I'm
not holding my breath for that to happen.
Still, I'm a bit leery of the idea. Isn't plpgsql supposed to be
optional/droppable? I guess we could tell people who don't want it
that they can't drop it until after restoring their old data,
but still ...
regards, tom lane
On 2024-02-28 We 17:53, Tom Lane wrote:
Andrew Dunstan <andrew@dunslane.net> writes:
On 2024-02-28 We 17:36, Tom Lane wrote:
Hmm ... pg_dump has never used any DO blocks in its output, and
I'm not sure it's a great idea to start. Seems like the kind of
decision we could regret down the road, given the possible need
for pg_restore to parse the output.This is only for pg_dumpall. pg_restore shouldn't care.
Yeah, fair point. I've long thought that we should get pg_dumpall
to emit something more structured than "big SQL script", but I'm
not holding my breath for that to happen.Still, I'm a bit leery of the idea. Isn't plpgsql supposed to be
optional/droppable? I guess we could tell people who don't want it
that they can't drop it until after restoring their old data,
but still ...
Based on no real evidence my feeling is that the incidence of that
problem is likely to be several orders of magnitude smaller than the
incidence of the problem complained of by the OP.
cheers
andrew
--
Andrew Dunstan
EDB: https://www.enterprisedb.com