From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Basha <basha@maxcontact.com>
To: Christophe Pettus <xof@thebuild.com>
Cc: PostgreSQL Bug List <pgsql-bugs@lists.postgresql.org>
Subject: Re: [EXT]: Re: BUG #18604: Regression in PostgreSQL 16.4: pg_dump Prevents Essential System Table Modifications
Date: Fri, 06 Sep 2024 23:18:57 +0200
Message-ID: <b097aceeb9e089efa786ef252097a22a4fa04bdf.camel@cybertec.at> (raw)
In-Reply-To: <GV1P194MB2356E0520E83B40CE57D4D2FD89E2@GV1P194MB2356.EURP194.PROD.OUTLOOK.COM>
References: <18604-04d64b68e981ced6@postgresql.org>
<3B462E2E-88B6-43B9-943F-90E883D33357@thebuild.com>
<GV1P194MB2356E0520E83B40CE57D4D2FD89E2@GV1P194MB2356.EURP194.PROD.OUTLOOK.COM>
On Fri, 2024-09-06 at 20:46 +0000, Basha wrote:
> 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;
>
> 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);
Such modifications are not supported.
I don't see why we should cater for that.
Yours,
Laurenz Albe
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: laurenz.albe@cybertec.at, basha@maxcontact.com, xof@thebuild.com, pgsql-bugs@lists.postgresql.org
Subject: Re: [EXT]: Re: BUG #18604: Regression in PostgreSQL 16.4: pg_dump Prevents Essential System Table Modifications
In-Reply-To: <b097aceeb9e089efa786ef252097a22a4fa04bdf.camel@cybertec.at>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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