Обсуждение: BUG #18281: Superuser can rename the schema with the prefix "pg_" (Applies to all versions of postgresql)

The following bug has been logged on the website:

Bug reference:      18281
Logged by:          Maciej Szeliga
Email address:      szyjek9302@gmail.com
PostgreSQL version: 16.1
Operating system:   centos 8
Description:

Hi, 
I renamed the schema pg_toast 
moja=# alter schema pg_toast rename to x_pg_toast
moja-# ;
ALTER SCHEMA

and can't undo it because :

moja=# alter schema x_pg_toast rename to pg_toast;
ERROR: unacceptable schema name "pg_toast"
DETAIL: The prefix "pg_" is reserved for system schemas.

I think this is a serious problem that should be fixed by developers. The
superuser should not be able to rename system schemas.

Regards
Maciej

The following bug has been logged on the website:

Bug reference:      18281
Logged by:          Maciej Szeliga
Email address:      szyjek9302@gmail.com
PostgreSQL version: 16.1
Operating system:   centos 8
Description:       

Hi,
I renamed the schema pg_toast
moja=# alter schema pg_toast rename to x_pg_toast
moja-# ;
ALTER SCHEMA

and can't undo it because :

moja=# alter schema x_pg_toast rename to pg_toast;
ERROR: unacceptable schema name "pg_toast"
DETAIL: The prefix "pg_" is reserved for system schemas.

I think this is a serious problem that should be fixed by developers. The
superuser should not be able to rename system schemas.

"David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Wednesday, January 10, 2024, PG Bug reporting form <
> noreply@postgresql.org> wrote:
>> I renamed the schema pg_toast
>> moja=# alter schema pg_toast rename to x_pg_toast
>> moja-# ;
>> ALTER SCHEMA
>> and can't undo it because :
>> moja=# alter schema x_pg_toast rename to pg_toast;
>> ERROR: unacceptable schema name "pg_toast"
>> DETAIL: The prefix "pg_" is reserved for system schemas.
>> 
>> I think this is a serious problem that should be fixed by developers. The
>> superuser should not be able to rename system schemas.

> This isn’t a bug - there is very little effort spent on trying to prevent
> the superuser from doing stuff, even stuff that directly breaks their
> system.

Indeed.  Try something like "delete from pg_proc;" (not in a database
you want to keep!).  This is not significantly different from the fact
that root privilege in Unix allows you to do unrecoverable stuff like
"rm -rf /".  The cost/benefit ratio just isn't there for trying to
put training wheels on root, or on superusers.

> I do agree that there seems to be some room for improvement here, at least
> as far as recovering from the initial bad decision goes.

A manual update on pg_namespace.nspname would be enough for getting
out of this particular problem.

            regards, tom lane