From: Tom Lane <tgl@sss.pgh.pa.us>
To: Praneel Devisetty <devisettypraneel@gmail.com>
Cc: pgsql-admin@postgresql.org
Subject: Re: Renamed pg_catalog schema
Date: Wed, 29 Jun 2022 02:01:12 -0400
Message-ID: <2991348.1656482472@sss.pgh.pa.us> (raw)
In-Reply-To: <CAHnVB4CBay+YExPwnxb+y8qewbhVoH5MTB-ZPHfD_TF2mK3+tw@mail.gmail.com>
References: <CAHnVB4CBay+YExPwnxb+y8qewbhVoH5MTB-ZPHfD_TF2mK3+tw@mail.gmail.com>
Praneel Devisetty <devisettypraneel@gmail.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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: tgl@sss.pgh.pa.us, devisettypraneel@gmail.com
Subject: Re: Renamed pg_catalog schema
In-Reply-To: <2991348.1656482472@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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