RE: [EXT]: Re: BUG #18604: Regression in PostgreSQL 16.4: pg_dump Prevents Essential System Table Modifications

Hi ,

Please find below the details as what system catalog modifications were done and why.

We provide our customers with access to their respective representative databases (Rep DB) within a multi-tenant PostgreSQL architecture. Each customer is assigned their own dedicated database, and for each database, a corresponding role is created with the necessary permissions.

For example, for customers such as:
Abc
Def
Xyz

the below user-roles:

Abc_usr for the Abc database
Def_usr for the Def database
Xyz_usr for the Xyz database

These roles are configured to have 'connect' privileges solely to their respective databases, ensuring isolation. For instance, only Abc_usr can connect to the Abc database, and this applies similarly to other users and databases.

To enhance security and prevent customers from viewing other database names in the system, we made modifications to the PostgreSQL system tables and created custom views that restrict the visibility of databases for each user.

Below are the changes

Step1 :
Set the config allow_system_table_mods = on

Step 2:
ALTER TABLE pg_catalog.pg_database RENAME TO pg_database_catalog;

ALTER TABLE pg_catalog.pg_database_catalog
OWNER TO postgres;

Step3:

CREATE OR REPLACE VIEW pg_catalog.pg_database
AS
SELECT oid,
datname,
datdba,
encoding,
datlocprovider,
datistemplate,
datallowconn,
datconnlimit,
datfrozenxid,
datminmxid,
dattablespace,
datcollate,
datctype,
daticulocale,
daticurules,
datcollversion,
datacl,
1262::oid AS tableoid
FROM pg_database_catalog
WHERE 1 = 1 AND has_database_privilege(oid, 'connect'::text);

ALTER TABLE pg_catalog.pg_database
OWNER TO postgres;

Step 1 to 3, will restrict the visibility of the database to the user where they have connect permissions.

This works ok and have no issues.

Step 4:
When we run the pg_dump as below

pg_dump -Upostgres -dabc > /backup/abc.backup

It errors out as below
pg_dump: error: query failed: ERROR: access to non-system view "pg_database" is restricted
pg_dump: detail: Query was: SELECT s.tableoid, s.oid, s.subname,
s.subowner,
s.subconninfo, s.subslotname, s.subsynccommit,
s.subpublications,
s.subbinary,
s.substream,
s.subtwophasestate,
s.subdisableonerr,
s.subpasswordrequired,
s.subrunasowner,
s.suborigin
FROM pg_subscription s
WHERE s.subdbid = (SELECT oid FROM pg_database
WHERE datname = current_database())

Hope this provide the required information. We notice this started happening only after the upgrade done from " pg_dump (PostgreSQL) 16.3 (Ubuntu 16.3-1.pgdg22.04+1)" to " pg_dump (PostgreSQL) 16.4 (Ubuntu 16.4-1.pgdg22.04+1)" .

Thanks,
Basha

-----Original Message-----
From: Christophe Pettus <xof(at)thebuild(dot)com>
Sent: 06 September 2024 20:38
To: Basha <basha(at)maxcontact(dot)com>
Cc: PostgreSQL Bug List <pgsql-bugs(at)lists(dot)postgresql(dot)org>
Subject: [EXT]: Re: BUG #18604: Regression in PostgreSQL 16.4: pg_dump Prevents Essential System Table Modifications

[You don't often get email from xof(at)thebuild(dot)com(dot) Learn why this is important at https://aka.ms/LearnAboutSenderIdentification ]

> On Sep 6, 2024, at 09:28, PG Bug reporting form <noreply(at)postgresql(dot)org> wrote:
> In PostgreSQL 16.4, the recent security enhancement introduced by the
> commit "Prevent unauthorized code execution during pg_dump (Masahiko
> Sawada)" has introduced unintended side effects that severely impact
> multi-tenant database environments. Specifically, this change blocks
> modifications to system tables, even when allow_system_table_mods is explicitly enabled.

As Tom noted, you should describe what system catalog modifications you are making.

It should be noted that this admonition appears as the first sentence in the documentation on the page that includes `allow_system_table_mods`:

The following parameters are intended for developer testing, and should never be used on a production database.

"We adjusted a developer-only parameter on a production database, having been warned by the documentation not to, and now something that previously worked no longer does" is not really going to be considered a bug. It's better that a way is found to support your requirements without modifying system catalogs.
MaxContact is a trading style of Trivoni Software Limited. Registration Number: England 09816677. Registered Office: City View House, 5 Union Street, Ardwick, Manchester M12 4JD. This e-mail and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom it is addressed. Any views or options presented are solely those of the author and do not necessarily represent those of Trivoni Software Limited. Internet communications are not secure and therefore Trivoni Software Limited does not accept legal responsibility for the contents of this message. If you are not the intended recipient, you are hereby notified that you have received this e-mail in error and that any use, disclosure, dissemination, forwarding, printing, or copying of this e-mail is strictly prohibited. Trivoni Software Limited will not be liable for direct, special, indirect or consequential damage arising from alterations of the contents of this message by a third party or as a result of any VIRUS being passed on. Any pricing details or other offers delivered via e-mail are not binding. If appropriate, an official purchase order quotation confirming pricing and bearing an authorisation signature will be provided via Docusign on request. If you have received this e-mail in error, please notify the sender immediately and delete the e-mail without taking any copies or forwarding it elsewhere.