From: | Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at> |
---|---|
To: | Basha <basha(at)maxcontact(dot)com>, Christophe Pettus <xof(at)thebuild(dot)com> |
Cc: | PostgreSQL Bug List <pgsql-bugs(at)lists(dot)postgresql(dot)org> |
Subject: | Re: [EXT]: Re: BUG #18604: Regression in PostgreSQL 16.4: pg_dump Prevents Essential System Table Modifications |
Date: | 2024-09-06 21:18:57 |
Message-ID: | b097aceeb9e089efa786ef252097a22a4fa04bdf.camel@cybertec.at |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Christophe Pettus | 2024-09-06 21:24:36 | Re: [EXT]: Re: BUG #18604: Regression in PostgreSQL 16.4: pg_dump Prevents Essential System Table Modifications |
Previous Message | Basha | 2024-09-06 20:46:45 | RE: [EXT]: Re: BUG #18604: Regression in PostgreSQL 16.4: pg_dump Prevents Essential System Table Modifications |