Subject: restrict_nonsystem_relation_kind led to regression (kinda)

From: Magnus Holmgren <[email-masked]>
Date: Mon, 23 Sep 2024 15:38:43 +0000

Hello,

We've set allow_system_table_mods = on so that we could rename 
pg_database and in its place put a custom view that only lists the
databases the current user has CONNECT privileges to. This is because
1) we allow customers direct (read only) access to their databases, but
2) we don't want them to see the other customers, and 3) restricting
access to pg_database altogether leads to the GUIs the customers use
spamming error messages because they expect pg_database to be readable,
and that makes the customers (or their consultants) annoyed.

A problem arose after the fix for CVE-2024-7348, because certain
queries that pg_dump runs use pg_database, and those are now blocked,
so pg_dump fails. Well, actually, it's just subscriptions that are the
problem when it comes to pg_dump: pg_dump --no-subscriptions works in
our case. However, pg_dumpall runs a different query that also uses
pg_database and that I don't think is possible to avoid.

I realise that if you use allow_system_table_mods, you're kinda on your
own, but it exists after all, and this security fix seems to make it
less usable, if not unusable.

Could views owned by postgres and/or in the pg_catalog namespace be
considered system relations, even if customized? There's no way to
suppress the use of restrict_nonsystem_relation_kind if you know that
there are no untrusted users with object creation privileges, is there?

Alternatively, do you have any other suggestions as to how to solve the
original problem (we'd like to avoid renaming the databases so they
don't reveal the customer names)?

-- 
Greetings,
Magnus Holmgren

Milient Software | www.milientsoftware.com

---

From: Alvaro Herrera <[email-masked]>
Date: Mon, 23 Sep 2024 17:50:49 +0200

Hi Magnus,

> and that makes the customers (or their consultants) annoyed.

Your use case and problem seem to match bug report #18604 almost
exactly:
https://postgr.es/m/[email-masked]

I suggest to read that discussion, as it contains useful information.
As I understand, you're only really safe (not just theatrically safe) by
giving each customer a separate Postgres instance.

Regards

-- 
Álvaro Herrera         PostgreSQL Developer  —  https://www.EnterpriseDB.com/