pg.ddx.io pgsql-general@postgresql.org mailing list archive
help / color / mirror / Atom feedPostgres 15 upgrades and template1 public schema
6+ messages / 3 participants
[nested] [flat]
* Postgres 15 upgrades and template1 public schema
@ 2022-10-19 20:36 Bruno Wolff III <bruno@wolff.to>
2022-10-19 21:30 ` Re: Postgres 15 upgrades and template1 public schema Thomas Kellerer <shammat@gmx.net>
0 siblings, 1 reply; 6+ messages in thread
From: Bruno Wolff III @ 2022-10-19 20:36 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org
I noticed when I did an upgrade from Postgres 14 to 15 that the public
schema in template1 was still owned by postgres instead of pg_database_owner.
I was expecting it to change because the release notes said that new
database clusters would have that. But shouldn't new clusters use what
is set in template1?
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Postgres 15 upgrades and template1 public schema
2022-10-19 20:36 Postgres 15 upgrades and template1 public schema Bruno Wolff III <bruno@wolff.to>
@ 2022-10-19 21:30 ` Thomas Kellerer <shammat@gmx.net>
2022-10-19 22:48 ` Re: Postgres 15 upgrades and template1 public schema Bruno Wolff III <bruno@wolff.to>
0 siblings, 1 reply; 6+ messages in thread
From: Thomas Kellerer @ 2022-10-19 21:30 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org
Bruno Wolff III schrieb am 19.10.2022 um 22:36:
> I noticed when I did an upgrade from Postgres 14 to 15 that the
> public schema in template1 was still owned by postgres instead of
> pg_database_owner. I was expecting it to change because the release
> notes said that new database clusters would have that. But shouldn't
> new clusters use what is set in template1?
This is explained in the release notes:
The change applies to new database clusters and to newly-created
databases in existing clusters.
Upgrading a cluster or restoring a database dump will preserve
public's existing permissions.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Postgres 15 upgrades and template1 public schema
2022-10-19 20:36 Postgres 15 upgrades and template1 public schema Bruno Wolff III <bruno@wolff.to>
2022-10-19 21:30 ` Re: Postgres 15 upgrades and template1 public schema Thomas Kellerer <shammat@gmx.net>
@ 2022-10-19 22:48 ` Bruno Wolff III <bruno@wolff.to>
2022-10-19 23:59 ` Re: Postgres 15 upgrades and template1 public schema Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 6+ messages in thread
From: Bruno Wolff III @ 2022-10-19 22:48 UTC (permalink / raw)
To: Thomas Kellerer <shammat@gmx.net>; +Cc: pgsql-general@lists.postgresql.org
On Wed, Oct 19, 2022 at 23:30:58 +0200,
Thomas Kellerer <shammat@gmx.net> wrote:
>
>Bruno Wolff III schrieb am 19.10.2022 um 22:36:
>>I noticed when I did an upgrade from Postgres 14 to 15 that the
>>public schema in template1 was still owned by postgres instead of
>>pg_database_owner. I was expecting it to change because the release
>>notes said that new database clusters would have that. But shouldn't
>>new clusters use what is set in template1?
>
>This is explained in the release notes:
>
> The change applies to new database clusters and to newly-created
> databases in existing clusters.
> Upgrading a cluster or restoring a database dump will preserve
> public's existing permissions.
How do new databases in pre-existing clusters get the new public schema
security if it doesn't come from template1?
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Postgres 15 upgrades and template1 public schema
2022-10-19 20:36 Postgres 15 upgrades and template1 public schema Bruno Wolff III <bruno@wolff.to>
2022-10-19 21:30 ` Re: Postgres 15 upgrades and template1 public schema Thomas Kellerer <shammat@gmx.net>
2022-10-19 22:48 ` Re: Postgres 15 upgrades and template1 public schema Bruno Wolff III <bruno@wolff.to>
@ 2022-10-19 23:59 ` Tom Lane <tgl@sss.pgh.pa.us>
2022-10-20 05:40 ` Re: Postgres 15 upgrades and template1 public schema Bruno Wolff III <bruno@wolff.to>
2022-11-05 12:18 ` Re: Postgres 15 upgrades and template1 public schema Bruno Wolff III <bruno@wolff.to>
0 siblings, 2 replies; 6+ messages in thread
From: Tom Lane @ 2022-10-19 23:59 UTC (permalink / raw)
To: Bruno Wolff III <bruno@wolff.to>; +Cc: Bruce Momjian <bruce@momjian.us>; Thomas Kellerer <shammat@gmx.net>; pgsql-general@lists.postgresql.org
Bruno Wolff III <bruno@wolff.to> writes:
> On Wed, Oct 19, 2022 at 23:30:58 +0200,
> Thomas Kellerer <shammat@gmx.net> wrote:
>> This is explained in the release notes:
>>
>> The change applies to new database clusters and to newly-created
>> databases in existing clusters.
>> Upgrading a cluster or restoring a database dump will preserve
>> public's existing permissions.
> How do new databases in pre-existing clusters get the new public schema
> security if it doesn't come from template1?
The release notes could probably use some tweaking here. It looks to
me like pg_dumpall (and hence pg_upgrade) will adjust the ownership and
permissions of template1's public schema to match what was in the old
installation, but it doesn't touch template0. Hence, whether a
"newly-created database in an existing cluster" has the old or new
properties of the public schema will depend on whether you clone it
from template1 or template0. That definitely needs explained, and
maybe we should recommend that DBAs consider manually changing
what's in template1.
regards, tom lane
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Postgres 15 upgrades and template1 public schema
2022-10-19 20:36 Postgres 15 upgrades and template1 public schema Bruno Wolff III <bruno@wolff.to>
2022-10-19 21:30 ` Re: Postgres 15 upgrades and template1 public schema Thomas Kellerer <shammat@gmx.net>
2022-10-19 22:48 ` Re: Postgres 15 upgrades and template1 public schema Bruno Wolff III <bruno@wolff.to>
2022-10-19 23:59 ` Re: Postgres 15 upgrades and template1 public schema Tom Lane <tgl@sss.pgh.pa.us>
@ 2022-10-20 05:40 ` Bruno Wolff III <bruno@wolff.to>
1 sibling, 0 replies; 6+ messages in thread
From: Bruno Wolff III @ 2022-10-20 05:40 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Bruce Momjian <bruce@momjian.us>; Thomas Kellerer <shammat@gmx.net>; pgsql-general@lists.postgresql.org
On Wed, Oct 19, 2022 at 19:59:52 -0400,
Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
>The release notes could probably use some tweaking here. It looks to
>me like pg_dumpall (and hence pg_upgrade) will adjust the ownership and
>permissions of template1's public schema to match what was in the old
>installation, but it doesn't touch template0. Hence, whether a
>"newly-created database in an existing cluster" has the old or new
>properties of the public schema will depend on whether you clone it
>from template1 or template0. That definitely needs explained, and
>maybe we should recommend that DBAs consider manually changing
>what's in template1.
This answers my question about what is actually happening.
I think expanding the release notes section on this a bit could be
helpful for other people.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Postgres 15 upgrades and template1 public schema
2022-10-19 20:36 Postgres 15 upgrades and template1 public schema Bruno Wolff III <bruno@wolff.to>
2022-10-19 21:30 ` Re: Postgres 15 upgrades and template1 public schema Thomas Kellerer <shammat@gmx.net>
2022-10-19 22:48 ` Re: Postgres 15 upgrades and template1 public schema Bruno Wolff III <bruno@wolff.to>
2022-10-19 23:59 ` Re: Postgres 15 upgrades and template1 public schema Tom Lane <tgl@sss.pgh.pa.us>
@ 2022-11-05 12:18 ` Bruno Wolff III <bruno@wolff.to>
1 sibling, 0 replies; 6+ messages in thread
From: Bruno Wolff III @ 2022-11-05 12:18 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Bruce Momjian <bruce@momjian.us>; Thomas Kellerer <shammat@gmx.net>; pgsql-general@lists.postgresql.org
On Wed, Oct 19, 2022 at 19:59:52 -0400,
Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
>The release notes could probably use some tweaking here. It looks to
>me like pg_dumpall (and hence pg_upgrade) will adjust the ownership and
>permissions of template1's public schema to match what was in the old
>installation, but it doesn't touch template0. Hence, whether a
>"newly-created database in an existing cluster" has the old or new
>properties of the public schema will depend on whether you clone it
>from template1 or template0. That definitely needs explained, and
>maybe we should recommend that DBAs consider manually changing
>what's in template1.
I didn't see any changes related to this in the first draft of the 15.1
release notes. Since I got the impression from the above that there
might be a change, I'm sending this as a reminder.
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2022-11-05 12:18 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2022-10-19 20:36 Postgres 15 upgrades and template1 public schema Bruno Wolff III <bruno@wolff.to>
2022-10-19 21:30 ` Thomas Kellerer <shammat@gmx.net>
2022-10-19 22:48 ` Bruno Wolff III <bruno@wolff.to>
2022-10-19 23:59 ` Tom Lane <tgl@sss.pgh.pa.us>
2022-10-20 05:40 ` Bruno Wolff III <bruno@wolff.to>
2022-11-05 12:18 ` Bruno Wolff III <bruno@wolff.to>
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