pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #18383: creation of public schema is not consistent
5+ messages / 4 participants
[nested] [flat]
* BUG #18383: creation of public schema is not consistent
@ 2024-03-07 17:58 PG Bug reporting form <noreply@postgresql.org>
2024-03-07 19:47 ` Re: BUG #18383: creation of public schema is not consistent Sam Darwin <samuel.d.darwin@gmail.com>
2024-03-07 20:11 ` Re: BUG #18383: creation of public schema is not consistent Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 5+ messages in thread
From: PG Bug reporting form @ 2024-03-07 17:58 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: samuel.d.darwin@gmail.com
The following bug has been logged on the website:
Bug reference: 18383
Logged by: Sam Darwin
Email address: samuel.d.darwin@gmail.com
PostgreSQL version: 15.2
Operating system: Ubuntu 22.04
Description:
Hi,
This is somewhere in-between a feature request and a bug.
When you create a database, the public schema has a description "standard
public schema", by default.
If you drop and re-create the public schema, it has an empty description.
These two activities are pretty similar. Creating a database. Creating a
public schema. But the results are different. You might imagine, that's
not particularly important. It doesn't affect anything. However, the
absence of that description "standard public schema" can cause errors during
a backup and restore procedures. Why? Because re-setting the schema
description during a restoration, requires that you must be the "owner" of
the schema. It's not enough to have full permissions. The restoring user
account might have all permissions, except not be the "owner" of the public
schema, and that will cause the restore to have an error.
What is a solution? Either always set the description of the public schema
to ""standard public schema", or never set the description of the public
schema to "standard public schema". Or allow restoration of the
"description" from a backup.
Or, leave it as is, so that a person encountering the error must do a lot of
investigation. :-) Ok.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: BUG #18383: creation of public schema is not consistent
2024-03-07 17:58 BUG #18383: creation of public schema is not consistent PG Bug reporting form <noreply@postgresql.org>
@ 2024-03-07 19:47 ` Sam Darwin <samuel.d.darwin@gmail.com>
1 sibling, 0 replies; 5+ messages in thread
From: Sam Darwin @ 2024-03-07 19:47 UTC (permalink / raw)
To: samuel.d.darwin@gmail.com; pgsql-bugs@lists.postgresql.org
To add a little more about this topic, after you understand the issue it's
not overly difficult. Hindsight is 20/20. When you first encounter it,
there is an error with these words: description schema public standard, and
those are all very generic words. It doesn't click in your mind what that
string of common, general words is really referring to. It's like seeing
the words "system" or "matrix" or something. They could mean anything.
Therefore another solution to the problem is to enhance the error message.
For example: "You have hit schema description problem 5938. See this link
for an explanation about 5938". Add a hyperlink to an article, specifically
about this issue.
On Thu, Mar 7, 2024 at 10:59 AM PG Bug reporting form <
noreply@postgresql.org> wrote:
> The following bug has been logged on the website:
>
> Bug reference: 18383
> Logged by: Sam Darwin
> Email address: samuel.d.darwin@gmail.com
> PostgreSQL version: 15.2
> Operating system: Ubuntu 22.04
> Description:
>
> Hi,
>
> This is somewhere in-between a feature request and a bug.
>
> When you create a database, the public schema has a description "standard
> public schema", by default.
>
> If you drop and re-create the public schema, it has an empty description.
>
> These two activities are pretty similar. Creating a database. Creating a
> public schema. But the results are different. You might imagine, that's
> not particularly important. It doesn't affect anything. However, the
> absence of that description "standard public schema" can cause errors
> during
> a backup and restore procedures. Why? Because re-setting the schema
> description during a restoration, requires that you must be the "owner" of
> the schema. It's not enough to have full permissions. The restoring user
> account might have all permissions, except not be the "owner" of the public
> schema, and that will cause the restore to have an error.
>
> What is a solution? Either always set the description of the public schema
> to ""standard public schema", or never set the description of the public
> schema to "standard public schema". Or allow restoration of the
> "description" from a backup.
> Or, leave it as is, so that a person encountering the error must do a lot
> of
> investigation. :-) Ok.
>
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: BUG #18383: creation of public schema is not consistent
2024-03-07 17:58 BUG #18383: creation of public schema is not consistent PG Bug reporting form <noreply@postgresql.org>
@ 2024-03-07 20:11 ` Tom Lane <tgl@sss.pgh.pa.us>
2024-03-08 01:48 ` Re: BUG #18383: creation of public schema is not consistent Sam Darwin <samuel.d.darwin@gmail.com>
1 sibling, 1 reply; 5+ messages in thread
From: Tom Lane @ 2024-03-07 20:11 UTC (permalink / raw)
To: samuel.d.darwin@gmail.com; +Cc: pgsql-bugs@lists.postgresql.org
PG Bug reporting form <noreply@postgresql.org> writes:
> When you create a database, the public schema has a description "standard
> public schema", by default.
> If you drop and re-create the public schema, it has an empty description.
> These two activities are pretty similar. Creating a database. Creating a
> public schema. But the results are different.
Well, yeah. The one you created is not the "standard" public schema;
it might happen to have the same name, but claiming it is the same
object seems very confused. It won't necessarily have the same
ownership or permissions, and it definitely won't have the same
comment unless you add that.
> You might imagine, that's
> not particularly important. It doesn't affect anything. However, the
> absence of that description "standard public schema" can cause errors during
> a backup and restore procedures. Why? Because re-setting the schema
> description during a restoration, requires that you must be the "owner" of
> the schema.
As far as I know, pg_dump deals with this issue for the standard
public schema. For user-created objects, it's up to you to be sure
that the restoring user has sufficient permissions. This is not
different for schemas than for any other objects.
I'm not really seeing a bug here, and you haven't provided sufficient
detail about your actual problem to act on it.
regards, tom lane
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: BUG #18383: creation of public schema is not consistent
2024-03-07 17:58 BUG #18383: creation of public schema is not consistent PG Bug reporting form <noreply@postgresql.org>
2024-03-07 20:11 ` Re: BUG #18383: creation of public schema is not consistent Tom Lane <tgl@sss.pgh.pa.us>
@ 2024-03-08 01:48 ` Sam Darwin <samuel.d.darwin@gmail.com>
2024-03-08 07:31 ` Re: BUG #18383: creation of public schema is not consistent Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 1 reply; 5+ messages in thread
From: Sam Darwin @ 2024-03-08 01:48 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-bugs@lists.postgresql.org
> > I'm not really seeing a bug here, and you haven't provided sufficient
> detail about your actual problem to act on it.
>
Just tested again. It seems the directionality matters, as follows:
If the dumpfile says "standard public schema", while the new DB has no
schema description. OK.
If the dumpfile has an empty schema description "", while the new DB is
using the default "standard public schema" . ERROR.
pg_restore: error: could not execute query: ERROR: must be owner of schema
public
Command was: COMMENT ON SCHEMA public IS '';
There is a phenomenon "the curse of knowledge". It means, that error looks
perfectly clear to you, but for somebody who didn't know a COMMENT is a
DESCRIPTION, and that a schema has a COMMENT (I never adjusted those), and
why public is '' , and why a GRANT ALL didn't provide permissions, and why
a COMMENT was missing (not at all intentionally) because of a forgotten
action many months earlier. One is simply attempting to restore a database.
But now that I have also been cursed by the knowledge I will agree there is
no bug. Nothing needs to be done. Well, maybe, consider if terse error
messages could be more verbose.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: BUG #18383: creation of public schema is not consistent
2024-03-07 17:58 BUG #18383: creation of public schema is not consistent PG Bug reporting form <noreply@postgresql.org>
2024-03-07 20:11 ` Re: BUG #18383: creation of public schema is not consistent Tom Lane <tgl@sss.pgh.pa.us>
2024-03-08 01:48 ` Re: BUG #18383: creation of public schema is not consistent Sam Darwin <samuel.d.darwin@gmail.com>
@ 2024-03-08 07:31 ` Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 0 replies; 5+ messages in thread
From: Laurenz Albe @ 2024-03-08 07:31 UTC (permalink / raw)
To: Sam Darwin <samuel.d.darwin@gmail.com>; Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-bugs@lists.postgresql.org
On Thu, 2024-03-07 at 18:48 -0700, Sam Darwin wrote:
> pg_restore: error: could not execute query: ERROR: must be owner of schema public
> Command was: COMMENT ON SCHEMA public IS '';
>
> Well, maybe, consider if terse error messages could be more verbose.
As long as you know that restoring a dump executes SQL statements, what
is missing? You get the statement that was executed and the error
message. Right, you have to know what the owner of an object is, but
you cannot expect the error message to discourse on that.
Restoring a dump when you are not a superuser is always difficult.
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2024-03-08 07:31 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-03-07 17:58 BUG #18383: creation of public schema is not consistent PG Bug reporting form <noreply@postgresql.org>
2024-03-07 19:47 ` Sam Darwin <samuel.d.darwin@gmail.com>
2024-03-07 20:11 ` Tom Lane <tgl@sss.pgh.pa.us>
2024-03-08 01:48 ` Sam Darwin <samuel.d.darwin@gmail.com>
2024-03-08 07:31 ` Laurenz Albe <laurenz.albe@cybertec.at>
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