pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #19097: System catalog modifications are allowed by alter
5+ messages / 5 participants
[nested] [flat]
* BUG #19097: System catalog modifications are allowed by alter
@ 2025-10-29 04:41 PG Bug reporting form <noreply@postgresql.org>
0 siblings, 3 replies; 5+ messages in thread
From: PG Bug reporting form @ 2025-10-29 04:41 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: rekgrpth@gmail.com
The following bug has been logged on the website:
Bug reference: 19097
Logged by: RekGRpth
Email address: rekgrpth@gmail.com
PostgreSQL version: 18.0
Operating system: docker alpine
Description:
```sql
create table pg_catalog.t (i int);
ERROR: permission denied to create "pg_catalog.t"
DETAIL: System catalog modifications are currently disallowed.
create table t (i int);
CREATE TABLE
alter table t set schema pg_catalog;
ALTER TABLE
\dS+ t
Table "pg_catalog.t"
Column | Type | Collation | Nullable | Default | Storage | Compression |
Stats target | Description
--------+---------+-----------+----------+---------+---------+-------------+--------------+-------------
i | integer | | | | plain | |
|
Replica Identity: NOTHING
Access method: heap
drop table pg_catalog.t;
DROP TABLE
```
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: BUG #19097: System catalog modifications are allowed by alter
@ 2025-10-29 11:31 Kirill Reshke <reshkekirill@gmail.com>
parent: PG Bug reporting form <noreply@postgresql.org>
2 siblings, 1 reply; 5+ messages in thread
From: Kirill Reshke @ 2025-10-29 11:31 UTC (permalink / raw)
To: rekgrpth@gmail.com; pgsql-bugs@lists.postgresql.org
On Wed, 29 Oct 2025 at 16:02, PG Bug reporting form
<noreply@postgresql.org> wrote:
>
> The following bug has been logged on the website:
>
> Bug reference: 19097
> Logged by: RekGRpth
> Email address: rekgrpth@gmail.com
> PostgreSQL version: 18.0
> Operating system: docker alpine
> Description:
>
> ```sql
> create table pg_catalog.t (i int);
> ERROR: permission denied to create "pg_catalog.t"
> DETAIL: System catalog modifications are currently disallowed.
>
> create table t (i int);
> CREATE TABLE
>
> alter table t set schema pg_catalog;
> ALTER TABLE
>
> \dS+ t
> Table "pg_catalog.t"
> Column | Type | Collation | Nullable | Default | Storage | Compression |
> Stats target | Description
> --------+---------+-----------+----------+---------+---------+-------------+--------------+-------------
> i | integer | | | | plain | |
> |
> Replica Identity: NOTHING
> Access method: heap
>
> drop table pg_catalog.t;
> DROP TABLE
> ```
>
reproduced here on v13, v16, v18 and HEAD. Looks like we need to teach
ExecAlterObjectSchemaStmt to respect allow_system_table_mods GUC.
PFA doing this by adding checks to CheckSetNamespace
--
Best regards,
Kirill Reshke
Attachments:
[application/octet-stream] v1-0001-Disallow-alter-schema-to-catalog-schema-without-p.patch (1.2K, ../../CALdSSPge_F_R5H6B6n5vi4TpMnPucF4gj2_V4qEfZEAnLD0s6A@mail.gmail.com/2-v1-0001-Disallow-alter-schema-to-catalog-schema-without-p.patch)
download | inline diff:
From 2765212be270601b88ad555c3f23973f76ae92e8 Mon Sep 17 00:00:00 2001
From: reshke <reshke@double.cloud>
Date: Wed, 29 Oct 2025 11:29:59 +0000
Subject: [PATCH v1] Disallow alter schema to catalog schema without proper GUC
set
---
src/backend/catalog/namespace.c | 11 +++++++++++
1 file changed, 11 insertions(+)
diff --git a/src/backend/catalog/namespace.c b/src/backend/catalog/namespace.c
index d23474da4fb..03992230e3a 100644
--- a/src/backend/catalog/namespace.c
+++ b/src/backend/catalog/namespace.c
@@ -3534,6 +3534,17 @@ CheckSetNamespace(Oid oldNspOid, Oid nspOid)
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
errmsg("cannot move objects into or out of temporary schemas")));
+
+ if (!allowSystemTableMods &&
+ ((IsCatalogNamespace(nspOid)) ||
+ IsToastNamespace(nspOid)) &&
+ IsNormalProcessingMode())
+ ereport(ERROR,
+ (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+ errmsg("permission denied to alter schema to \"%s\"",
+ get_namespace_name(nspOid)),
+ errdetail("System catalog modifications are currently disallowed.")));
+
/* same for TOAST schema */
if (nspOid == PG_TOAST_NAMESPACE || oldNspOid == PG_TOAST_NAMESPACE)
ereport(ERROR,
--
2.43.0
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: BUG #19097: System catalog modifications are allowed by alter
@ 2025-10-29 11:32 Dilip Kumar <dilipbalaut@gmail.com>
parent: PG Bug reporting form <noreply@postgresql.org>
2 siblings, 0 replies; 5+ messages in thread
From: Dilip Kumar @ 2025-10-29 11:32 UTC (permalink / raw)
To: rekgrpth@gmail.com; pgsql-bugs@lists.postgresql.org
On Wed, Oct 29, 2025 at 4:32 PM PG Bug reporting form
<noreply@postgresql.org> wrote:
>
> The following bug has been logged on the website:
>
> Bug reference: 19097
> Logged by: RekGRpth
> Email address: rekgrpth@gmail.com
> PostgreSQL version: 18.0
> Operating system: docker alpine
> Description:
>
> ```sql
> create table pg_catalog.t (i int);
> ERROR: permission denied to create "pg_catalog.t"
> DETAIL: System catalog modifications are currently disallowed.
>
> create table t (i int);
> CREATE TABLE
>
> alter table t set schema pg_catalog;
> ALTER TABLE
>
> \dS+ t
> Table "pg_catalog.t"
> Column | Type | Collation | Nullable | Default | Storage | Compression |
> Stats target | Description
> --------+---------+-----------+----------+---------+---------+-------------+--------------+-------------
> i | integer | | | | plain | |
> |
> Replica Identity: NOTHING
> Access method: heap
>
> drop table pg_catalog.t;
> DROP TABLE
Seems like this is intentionally kept like this, here is what comments
in heap_create says. Not sure what's the exact reason for not
allowing it to be created directly and it allowed to move it to
pg_catalog.
/*
* Don't allow creating relations in pg_catalog directly, even though it
* is allowed to move user defined relations there. Semantics with search
* paths including pg_catalog are too confusing for now.
*
* But allow creating indexes on relations in pg_catalog even if
* allow_system_table_mods = off, upper layers already guarantee it's on a
* user defined relation, not a system one.
*/
if (!allow_system_table_mods &&
((IsCatalogNamespace(relnamespace) && relkind != RELKIND_INDEX) ||
IsToastNamespace(relnamespace)) &&
IsNormalProcessingMode())
ereport(ERROR,
(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
errmsg("permission denied to create \"%s.%s\"",
get_namespace_name(relnamespace), relname),
errdetail("System catalog modifications are currently disallowed.")));
--
Regards,
Dilip Kumar
Google
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: BUG #19097: System catalog modifications are allowed by alter
@ 2025-10-29 12:09 Laurenz Albe <laurenz.albe@cybertec.at>
parent: PG Bug reporting form <noreply@postgresql.org>
2 siblings, 0 replies; 5+ messages in thread
From: Laurenz Albe @ 2025-10-29 12:09 UTC (permalink / raw)
To: rekgrpth@gmail.com; pgsql-bugs@lists.postgresql.org
On Wed, 2025-10-29 at 04:41 +0000, PG Bug reporting form wrote:
>
> PostgreSQL version: 18.0
>
> ```sql
> create table pg_catalog.t (i int);
> ERROR: permission denied to create "pg_catalog.t"
> DETAIL: System catalog modifications are currently disallowed.
>
> create table t (i int);
> CREATE TABLE
>
> alter table t set schema pg_catalog;
> ALTER TABLE
>
> \dS+ t
> Table "pg_catalog.t"
> Column | Type | Collation | Nullable | Default | Storage | Compression |
> Stats target | Description
> --------+---------+-----------+----------+---------+---------+-------------+--------------+-------------
> i | integer | | | | plain | |
> >
> Replica Identity: NOTHING
> Access method: heap
>
> drop table pg_catalog.t;
> DROP TABLE
> ```
That is confusing, but it seems intentional. See the following comment in
src/backend/catalog/heap.c:
/*
* Don't allow creating relations in pg_catalog directly, even though it
* is allowed to move user defined relations there. Semantics with search
* paths including pg_catalog are too confusing for now.
*
* But allow creating indexes on relations in pg_catalog even if
* allow_system_table_mods = off, upper layers already guarantee it's on a
* user defined relation, not a system one.
*/
This was deliberately added by commit 8e18d04d4daf.
I couldn't find the pertinent discussion in the archives.
I don't think it is necessary to document that...
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: BUG #19097: System catalog modifications are allowed by alter
@ 2025-10-29 14:17 Tom Lane <tgl@sss.pgh.pa.us>
parent: Kirill Reshke <reshkekirill@gmail.com>
0 siblings, 0 replies; 5+ messages in thread
From: Tom Lane @ 2025-10-29 14:17 UTC (permalink / raw)
To: Kirill Reshke <reshkekirill@gmail.com>; +Cc: rekgrpth@gmail.com; pgsql-bugs@lists.postgresql.org
Kirill Reshke <reshkekirill@gmail.com> writes:
> reproduced here on v13, v16, v18 and HEAD. Looks like we need to teach
> ExecAlterObjectSchemaStmt to respect allow_system_table_mods GUC.
allow_system_table_mods has never been intended to be bulletproof,
only to slow down careless superusers. I'm not especially concerned
about whether there are paths it doesn't cover.
regards, tom lane
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2025-10-29 14:17 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-10-29 04:41 BUG #19097: System catalog modifications are allowed by alter PG Bug reporting form <noreply@postgresql.org>
2025-10-29 11:31 ` Kirill Reshke <reshkekirill@gmail.com>
2025-10-29 14:17 ` Tom Lane <tgl@sss.pgh.pa.us>
2025-10-29 11:32 ` Dilip Kumar <dilipbalaut@gmail.com>
2025-10-29 12:09 ` Laurenz Albe <laurenz.albe@cybertec.at>
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