Message-ID: <b097aceeb9e089efa786ef252097a22a4fa04bdf.camel@cybertec.at>
Subject: Re: [EXT]: Re: BUG #18604: Regression in PostgreSQL 16.4: pg_dump
 Prevents Essential System Table Modifications
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Basha <basha@maxcontact.com>, Christophe Pettus <xof@thebuild.com>
Cc: PostgreSQL Bug List <pgsql-bugs@lists.postgresql.org>
Date: Fri, 06 Sep 2024 23:18:57 +0200
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>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
User-Agent: Evolution 3.52.4 (3.52.4-1.fc40) 
MIME-Version: 1.0
Archived-At: 
 <https://www.postgresql.org/message-id/b097aceeb9e089efa786ef252097a22a4fa04bdf.camel%40cybertec.at>
Precedence: bulk

On Fri, 2024-09-06 at 20:46 +0000, Basha wrote:
> Please find below the details as what system catalog modifications were d=
one and why.
>=20
> We provide our customers with access to their respective representative d=
atabases
> (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.
>=20
> For example, for customers such as:
> Abc
> Def
> Xyz
>=20
> the below user-roles:
>=20
> Abc_usr for the Abc database
> Def_usr for the Def database
> Xyz_usr for the Xyz database
>=20
> These roles are configured to have 'connect' privileges solely to their r=
espective
> databases, ensuring isolation. For instance, only Abc_usr can connect to =
the Abc
> database, and this applies similarly to other users and databases.
>=20
> To enhance security and prevent customers from viewing other database nam=
es in the
> system, we made modifications to the PostgreSQL system tables and created=
 custom
> views that restrict the visibility of databases for each user.
>=20
> Below are the changes
>=20
> Step1 :
> Set the config allow_system_table_mods =3D on
>=20
> Step 2:
> ALTER TABLE pg_catalog.pg_database RENAME TO pg_database_catalog;
>=20
> Step3:
>=20
> 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 =3D 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