pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedFeature bug dumpall CREATE ROLE postgres
9+ messages / 4 participants
[nested] [flat]
* Feature bug dumpall CREATE ROLE postgres
@ 2024-02-27 17:26 Jim Wilson <jim@wreath.com>
2024-02-27 20:12 ` Re: Feature bug dumpall CREATE ROLE postgres Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 9+ messages in thread
From: Jim Wilson @ 2024-02-27 17:26 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org
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.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Feature bug dumpall CREATE ROLE postgres
2024-02-27 17:26 Feature bug dumpall CREATE ROLE postgres Jim Wilson <jim@wreath.com>
@ 2024-02-27 20:12 ` Tom Lane <tgl@sss.pgh.pa.us>
2024-02-27 21:33 ` Re: Feature bug dumpall CREATE ROLE postgres David G. Johnston <david.g.johnston@gmail.com>
2024-02-28 17:25 ` Re: Feature bug dumpall CREATE ROLE postgres Jim Wilson <jim@wreath.com>
2024-02-28 22:31 ` Re: Feature bug dumpall CREATE ROLE postgres Andrew Dunstan <andrew@dunslane.net>
0 siblings, 3 replies; 9+ messages in thread
From: Tom Lane @ 2024-02-27 20:12 UTC (permalink / raw)
To: Jim Wilson <jim@wreath.com>; +Cc: pgsql-bugs@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Feature bug dumpall CREATE ROLE postgres
2024-02-27 17:26 Feature bug dumpall CREATE ROLE postgres Jim Wilson <jim@wreath.com>
2024-02-27 20:12 ` Re: Feature bug dumpall CREATE ROLE postgres Tom Lane <tgl@sss.pgh.pa.us>
@ 2024-02-27 21:33 ` David G. Johnston <david.g.johnston@gmail.com>
2 siblings, 0 replies; 9+ messages in thread
From: David G. Johnston @ 2024-02-27 21:33 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Jim Wilson <jim@wreath.com>; pgsql-bugs@lists.postgresql.org
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.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Feature bug dumpall CREATE ROLE postgres
2024-02-27 17:26 Feature bug dumpall CREATE ROLE postgres Jim Wilson <jim@wreath.com>
2024-02-27 20:12 ` Re: Feature bug dumpall CREATE ROLE postgres Tom Lane <tgl@sss.pgh.pa.us>
@ 2024-02-28 17:25 ` Jim Wilson <jim@wreath.com>
2 siblings, 0 replies; 9+ messages in thread
From: Jim Wilson @ 2024-02-28 17:25 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-bugs@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Feature bug dumpall CREATE ROLE postgres
2024-02-27 17:26 Feature bug dumpall CREATE ROLE postgres Jim Wilson <jim@wreath.com>
2024-02-27 20:12 ` Re: Feature bug dumpall CREATE ROLE postgres Tom Lane <tgl@sss.pgh.pa.us>
@ 2024-02-28 22:31 ` Andrew Dunstan <andrew@dunslane.net>
2024-02-28 22:36 ` Re: Feature bug dumpall CREATE ROLE postgres Tom Lane <tgl@sss.pgh.pa.us>
2 siblings, 1 reply; 9+ messages in thread
From: Andrew Dunstan @ 2024-02-28 22:31 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; Jim Wilson <jim@wreath.com>; +Cc: pgsql-bugs@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Feature bug dumpall CREATE ROLE postgres
2024-02-27 17:26 Feature bug dumpall CREATE ROLE postgres Jim Wilson <jim@wreath.com>
2024-02-27 20:12 ` Re: Feature bug dumpall CREATE ROLE postgres Tom Lane <tgl@sss.pgh.pa.us>
2024-02-28 22:31 ` Re: Feature bug dumpall CREATE ROLE postgres Andrew Dunstan <andrew@dunslane.net>
@ 2024-02-28 22:36 ` Tom Lane <tgl@sss.pgh.pa.us>
2024-02-28 22:47 ` Re: Feature bug dumpall CREATE ROLE postgres Andrew Dunstan <andrew@dunslane.net>
0 siblings, 1 reply; 9+ messages in thread
From: Tom Lane @ 2024-02-28 22:36 UTC (permalink / raw)
To: Andrew Dunstan <andrew@dunslane.net>; +Cc: Jim Wilson <jim@wreath.com>; pgsql-bugs@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Feature bug dumpall CREATE ROLE postgres
2024-02-27 17:26 Feature bug dumpall CREATE ROLE postgres Jim Wilson <jim@wreath.com>
2024-02-27 20:12 ` Re: Feature bug dumpall CREATE ROLE postgres Tom Lane <tgl@sss.pgh.pa.us>
2024-02-28 22:31 ` Re: Feature bug dumpall CREATE ROLE postgres Andrew Dunstan <andrew@dunslane.net>
2024-02-28 22:36 ` Re: Feature bug dumpall CREATE ROLE postgres Tom Lane <tgl@sss.pgh.pa.us>
@ 2024-02-28 22:47 ` Andrew Dunstan <andrew@dunslane.net>
2024-02-28 22:53 ` Re: Feature bug dumpall CREATE ROLE postgres Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 9+ messages in thread
From: Andrew Dunstan @ 2024-02-28 22:47 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Jim Wilson <jim@wreath.com>; pgsql-bugs@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Feature bug dumpall CREATE ROLE postgres
2024-02-27 17:26 Feature bug dumpall CREATE ROLE postgres Jim Wilson <jim@wreath.com>
2024-02-27 20:12 ` Re: Feature bug dumpall CREATE ROLE postgres Tom Lane <tgl@sss.pgh.pa.us>
2024-02-28 22:31 ` Re: Feature bug dumpall CREATE ROLE postgres Andrew Dunstan <andrew@dunslane.net>
2024-02-28 22:36 ` Re: Feature bug dumpall CREATE ROLE postgres Tom Lane <tgl@sss.pgh.pa.us>
2024-02-28 22:47 ` Re: Feature bug dumpall CREATE ROLE postgres Andrew Dunstan <andrew@dunslane.net>
@ 2024-02-28 22:53 ` Tom Lane <tgl@sss.pgh.pa.us>
2024-02-29 04:47 ` Re: Feature bug dumpall CREATE ROLE postgres Andrew Dunstan <andrew@dunslane.net>
0 siblings, 1 reply; 9+ messages in thread
From: Tom Lane @ 2024-02-28 22:53 UTC (permalink / raw)
To: Andrew Dunstan <andrew@dunslane.net>; +Cc: Jim Wilson <jim@wreath.com>; pgsql-bugs@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Feature bug dumpall CREATE ROLE postgres
2024-02-27 17:26 Feature bug dumpall CREATE ROLE postgres Jim Wilson <jim@wreath.com>
2024-02-27 20:12 ` Re: Feature bug dumpall CREATE ROLE postgres Tom Lane <tgl@sss.pgh.pa.us>
2024-02-28 22:31 ` Re: Feature bug dumpall CREATE ROLE postgres Andrew Dunstan <andrew@dunslane.net>
2024-02-28 22:36 ` Re: Feature bug dumpall CREATE ROLE postgres Tom Lane <tgl@sss.pgh.pa.us>
2024-02-28 22:47 ` Re: Feature bug dumpall CREATE ROLE postgres Andrew Dunstan <andrew@dunslane.net>
2024-02-28 22:53 ` Re: Feature bug dumpall CREATE ROLE postgres Tom Lane <tgl@sss.pgh.pa.us>
@ 2024-02-29 04:47 ` Andrew Dunstan <andrew@dunslane.net>
0 siblings, 0 replies; 9+ messages in thread
From: Andrew Dunstan @ 2024-02-29 04:47 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Jim Wilson <jim@wreath.com>; pgsql-bugs@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2024-02-29 04:47 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-02-27 17:26 Feature bug dumpall CREATE ROLE postgres Jim Wilson <jim@wreath.com>
2024-02-27 20:12 ` Tom Lane <tgl@sss.pgh.pa.us>
2024-02-27 21:33 ` David G. Johnston <david.g.johnston@gmail.com>
2024-02-28 17:25 ` Jim Wilson <jim@wreath.com>
2024-02-28 22:31 ` Andrew Dunstan <andrew@dunslane.net>
2024-02-28 22:36 ` Tom Lane <tgl@sss.pgh.pa.us>
2024-02-28 22:47 ` Andrew Dunstan <andrew@dunslane.net>
2024-02-28 22:53 ` Tom Lane <tgl@sss.pgh.pa.us>
2024-02-29 04:47 ` Andrew Dunstan <andrew@dunslane.net>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox