BUG #18281: Superuser can rename the schema with the prefix "pg_" (Applies to all versions of postgresql)

On Wednesday, January 10, 2024, PG Bug reporting form <
noreply@postgresql.org> wrote:

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.

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.

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. Seems like there
should be a way, maybe with the alter catalogs GUC, to bypass the error.

David J.

"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