RE: [EXT]: Re: BUG #18604: Regression in PostgreSQL 16.4: pg_dump Prevents Essential System Table Modifications - Mailing list pgsql-bugs

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
PostgreSQLarchitecture. Each customer is assigned their own dedicated database, and for each database, a corresponding
roleis 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
PostgreSQLsystem 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@thebuild.com>
Sent: 06 September 2024 20:38
To: Basha <basha@maxcontact.com>
Cc: PostgreSQL Bug List <pgsql-bugs@lists.postgresql.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@thebuild.com. Learn why this is important at
https://aka.ms/LearnAboutSenderIdentification] 

> On Sep 6, 2024, at 09:28, PG Bug reporting form <noreply@postgresql.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
nowsomething that previously worked no longer does" is not really going to be considered a bug.  It's better that a way
isfound to support your requirements without modifying system catalogs. 
MaxContact is a trading style of Trivoni Software Limited. Registration Number: England 09816677. Registered Office:
CityView House, 5 Union Street, Ardwick, Manchester M12 4JD. This e-mail and any files transmitted with it are
confidentialand intended solely for the use of the individual or entity to whom it is addressed. Any views or options
presentedare solely those of the author and do not necessarily represent those of Trivoni Software Limited. Internet
communicationsare not secure and therefore Trivoni Software Limited does not accept legal responsibility for the
contentsof this message. If you are not the intended recipient, you are hereby notified that you have received this
e-mailin 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
fromalterations of the contents of this message by a third party or as a result of any VIRUS being passed on. Any
pricingdetails or other offers delivered via e-mail are not binding. If appropriate, an official purchase order
quotationconfirming pricing and bearing an authorisation signature will be provided via Docusign on request. If you
havereceived this e-mail in error, please notify the sender immediately and delete the e-mail without taking any copies
orforwarding it elsewhere.