Thread: BUG #18251: Incorrect DROP VIEW pg_catalog.* behavior

BUG #18251: Incorrect DROP VIEW pg_catalog.* behavior

From
PG Bug reporting form
Date:
The following bug has been logged on the website:

Bug reference:      18251
Logged by:          Vardan Pogosyan
Email address:      vardan.pogosyn@yandex.ru
PostgreSQL version: 15.5
Operating system:   Ubuntu 22.04
Description:

Hello!

When i try to drop and create system view, i get following behavior:

postgres=# DROP VIEW pg_catalog.pg_config;
DROP VIEW

postgres=# CREATE OR REPLACE VIEW pg_catalog.pg_config AS 
SELECT pg_config.name, pg_config.setting 
FROM pg_config() pg_config(name, setting);
ERROR:  permission denied to create "pg_catalog.pg_config"
DETAIL:  System catalog modifications are currently disallowed.

I'm not sure, but it's look strange. 
When i connect to template1, i can use droped views. But if drop view on
template1 problem repeats.

I find it on the PostgreSQL 16, 15, 14 (i didn't try on others).


Re: BUG #18251: Incorrect DROP VIEW pg_catalog.* behavior

From
Julien Rouhaud
Date:
Hi,

On Sun, Dec 17, 2023 at 09:44:33AM +0000, PG Bug reporting form wrote:
>
> When i try to drop and create system view, i get following behavior:
>
> postgres=# DROP VIEW pg_catalog.pg_config;
> DROP VIEW
>
> postgres=# CREATE OR REPLACE VIEW pg_catalog.pg_config AS
> SELECT pg_config.name, pg_config.setting
> FROM pg_config() pg_config(name, setting);
> ERROR:  permission denied to create "pg_catalog.pg_config"
> DETAIL:  System catalog modifications are currently disallowed.

That's somewhat expected.  System catalog modifications are only prevented
globally at the creation side, I guess mostly because we don't want user to
e.g. create an index or a trigger on a system catalog just to realize that it
won't be maintained, and things like that.

The fact that you can drop a system view is because you're using a superuser
connection, and there are so many things you can do as a superuser to mess up
the instance so there wouldn't be much value disallowing just this specific
case.
>
> I'm not sure, but it's look strange.
> When i connect to template1, i can use droped views. But if drop view on
> template1 problem repeats.

That's not surprising.  You always need to use a database to create a new one.
The only different between a template database and a normal database is the
permissions needed on the source database side.