Обсуждение: BUG #18383: creation of public schema is not consistent
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.
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.
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.
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
> 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 '';
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.
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.
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