Re: Renamed pg_catalog schema

Thanks Tom for the quick response and it was really helpful.

On Wed, Jun 29, 2022 at 11:31 AM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Praneel Devisetty <devisettypraneel(at)gmail(dot)com> writes:
> > pg_catalog schema has been accidentally renamed in a PG database.
>
> "Accidentally"? Anyone who was doing that kind of fooling around in a
> valuable database really ought not be trusted with admin privileges.
>
> > Renaming it back to pg_catalog is failing with error:
> > ERROR: unacceptable schema name "pg_catalog"
> > DETAIL: The prefix "pg_" is reserved for system schemas.
> > Is there any way to bypass this?
>
> regression=# create database breakme;
> CREATE DATABASE
> regression=# \c breakme
> You are now connected to database "breakme" as user "postgres".
> breakme=# alter schema pg_catalog rename to broken;
> ALTER SCHEMA
> breakme=# \d
> ERROR: relation "pg_catalog.pg_class" does not exist
> LINE 5: FROM pg_catalog.pg_class c
> ^
>
> ... oops, it's pretty broken alright ...
>
> breakme=# alter schema broken rename to pg_catalog;
> ERROR: unacceptable schema name "pg_catalog"
> DETAIL: The prefix "pg_" is reserved for system schemas.
>
> ... as you said. But:
>
> breakme=# set allow_system_table_mods = 1;
> SET
> breakme=# alter schema broken rename to pg_catalog;
> ALTER SCHEMA
> breakme=# \d
> Did not find any relations.
>
> Needless to say, running with allow_system_table_mods on opens
> the door to even more dangerous changes, some of which you will
> absolutely not be able to recover from. (For example,
> "delete from pg_class" is about as bad as "rm -rf /" ...)
>
> Again, whoever broke this for you needs a bit of re-education before
> they are trusted with superuser privileges in any DB you care about.
>
> regards, tom lane
>