BUG #18604: Regression in PostgreSQL 16.4: pg_dump Prevents Essential System Table Modifications

The following bug has been logged on the website:

Bug reference: 18604
Logged by: Aladin Basha
Email address: basha(at)maxcontact(dot)com
PostgreSQL version: 16.4
Operating system: Ubuntu 22.04.4 LTS
Description:

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.

Our multi-tenant architecture relies on modifying system tables to ensure
database isolation for each customer. Each customer is assigned a dedicated
database, and by modifying system tables, we restrict their visibility of
other databases. This is a critical component of our security model, where
each customer only sees their own database within tools like pgAdmin, which
query the pg_database table.

However, after the PostgreSQL 16.4 update, pg_dump now fails due to these
system table modifications, leaving us in a precarious position where
essential backups cannot be performed. This restriction appears to apply
globally, without any option to bypass or disable it, even when the system
table modification flag is set.

Expected Behavior:
The security feature should allow system table modifications when
allow_system_table_mods is enabled.

The restriction on pg_dump should be optional or configurable, particularly
for environments where system table modifications are an intentional and
controlled part of the setup.
Actual Behavior:

pg_dump refuses to work when system table modifications are in place, even
when allow_system_table_mods is enabled.
Impact:

Our multi-tenant environment, which relies heavily on system table
modifications for tenant isolation, is unable to use pg_dump to perform
backups, thus compromising data safety.

This issue severely disrupts the workflow of environments where customer
isolation through system table modification is critical, leaving us with no
viable alternative for backups or data visibility management.

We believe that the new security feature should allow for more flexibility,
especially in controlled environments where allow_system_table_mods is
explicitly enabled. It should either provide an option to override this
restriction in pg_dump, or respect the configuration settings that allow
system table modifications.

PostgreSQL Version: PostgreSQL 16.4

Steps to Reproduce:
Enable allow_system_table_mods = true.
Modify system tables (e.g., pg_database) to restrict visibility in a
multi-tenant setup.
Attempt to run pg_dump for backup purposes.
pg_dump fails due to the recent security restriction.

Suggested Resolution:
Introduce a configurable option in pg_dump to allow it to work with modified
system tables when allow_system_table_mods is enabled.

Adjust the security enhancement to respect environments where system table
modifications are a necessary feature, or provide a safe, configurable
bypass for such use cases.

Thank you for your attention to this matter. We look forward to a resolution
that ensures both security and flexibility for diverse PostgreSQL use cases.

PG Bug reporting form <noreply(at)postgresql(dot)org> writes:
> Steps to Reproduce:
> Enable allow_system_table_mods = true.
> Modify system tables (e.g., pg_database) to restrict visibility in a
> multi-tenant setup.
> Attempt to run pg_dump for backup purposes.
> pg_dump fails due to the recent security restriction.

Without any details about what you did to the system catalogs,
this complaint is quite un-actionable.

I will note that we do not consider random manual changes to
system catalogs to be a supported feature.

regards, tom lane

> On Sep 6, 2024, at 09:28, PG Bug reporting form <noreply(at)postgresql(dot)org> wrote:
> However, after the PostgreSQL 16.4 update, pg_dump now fails due to these
> system table modifications, leaving us in a precarious position where
> essential backups cannot be performed.

It should be noted that this change does not prevent PITR backups, so it is still entirely possible to back up your installation.

Thank you for your reply. However, it will not provide us option to backup and restore specific database.
Seems, this change is breaking the original feature. And should be considered to provide options.
Thanks,

Sent from Outlook for Android<https://aka.ms/AAb9ysg>
________________________________
From: Christophe Pettus <xof(at)thebuild(dot)com>
Sent: Friday, September 6, 2024 8:07:35 PM
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:
> However, after the PostgreSQL 16.4 update, pg_dump now fails due to these
> system table modifications, leaving us in a precarious position where
> essential backups cannot be performed.

It should be noted that this change does not prevent PITR backups, so it is still entirely possible to back up your installation.
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.

The original option to allow system table is to make changes to the system tables. This change in 16.4 is removing that feature. When allow system table is set to on, this new feature should still allow and should not affect anything.
Regards,
Basha

Sent from Outlook for Android<https://aka.ms/AAb9ysg>
________________________________
From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Sent: Friday, September 6, 2024 7:49:07 PM
To: Basha <basha(at)maxcontact(dot)com>
Cc: pgsql-bugs(at)lists(dot)postgresql(dot)org <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 tgl(at)sss(dot)pgh(dot)pa(dot)us(dot) Learn why this is important at https://aka.ms/LearnAboutSenderIdentification ]

PG Bug reporting form <noreply(at)postgresql(dot)org> writes:
> Steps to Reproduce:
> Enable allow_system_table_mods = true.
> Modify system tables (e.g., pg_database) to restrict visibility in a
> multi-tenant setup.
> Attempt to run pg_dump for backup purposes.
> pg_dump fails due to the recent security restriction.

Without any details about what you did to the system catalogs,
this complaint is quite un-actionable.

I will note that we do not consider random manual changes to
system catalogs to be a supported feature.

regards, tom lane
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.

> 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.

Basha <Basha(at)maxcontact(dot)com> writes:
> The original option to allow system table is to make changes to the system tables. This change in 16.4 is removing that feature. When allow system table is set to on, this new feature should still allow and should not affect anything.

[ shrug... ] You're making an incredibly overheated, expansive
claim, many possible interpretations of which are obviously false.
You've provided zero detail that would let anyone figure out what your
actual concrete problem is --- and it sure isn't obvious, because
pg_dump doesn't have any direct connection to allow_system_table_mods,
nor does the security patch you're complaining about.

Perhaps you should read

https://wiki.postgresql.org/wiki/Guide_to_reporting_problems

But with only the information you've provided so far, this bug report
isn't going to go anywhere, because we can't even understand what
you are talking about.

regards, tom lane

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.

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

On Sep 6, 2024, at 13:46, Basha <Basha(at)maxcontact(dot)com> wrote:
> 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;

You've really stepped outside what is considered supported behavior here. That it worked at all was more accidental than a documented and supported feature. Shadowing system catalogs with views *is* going to break things, and that `allow_system_table_mods` has that potential is documented. I'm sure this is frustrating, but it's extremely unlikely that this will be considered a regression worth undoing a security fix for.

Thank you for your response and for acknowledging the complexities of our use case.

We fully appreciate the importance of the security fix and the need to protect against unauthorized code execution. However, this change has left us in a challenging position.

If shadowing system catalogs via views is not a recommended path, we would be grateful for guidance on alternative approaches to achieve the same result—restricting visibility of databases in a multi-tenant environment while maintaining essential operations like backups. Specifically, is there a supported way to enforce database isolation at the system catalog level, or is there a possibility of introducing a more granular control over pg_dump in such cases?

We remain open to adjusting our approach if a safer, supported solution exists.

Thanks,

Basha

________________________________
From: Christophe Pettus <xof(at)thebuild(dot)com>
Sent: Friday, September 6, 2024 10:24:36 PM
To: Basha <Basha(at)maxcontact(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

On Sep 6, 2024, at 13:46, Basha <Basha(at)maxcontact(dot)com> wrote:
> 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;

You've really stepped outside what is considered supported behavior here. That it worked at all was more accidental than a documented and supported feature. Shadowing system catalogs with views *is* going to break things, and that `allow_system_table_mods` has that potential is documented. I'm sure this is frustrating, but it's extremely unlikely that this will be considered a regression worth undoing a security fix for.
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.

Christophe Pettus <xof(at)thebuild(dot)com> writes:
> You've really stepped outside what is considered supported behavior here. That it worked at all was more accidental than a documented and supported feature. Shadowing system catalogs with views *is* going to break things, and that `allow_system_table_mods` has that potential is documented. I'm sure this is frustrating, but it's extremely unlikely that this will be considered a regression worth undoing a security fix for.

Indeed. You might look into enforcing the restriction you want
by attaching an RLS policy to pg_database, instead of this hack.
Mind you, we are unlikely to consider that supported either if push
comes to shove. But it would at least dodge your immediate problem.

(I'll just note that this implementation is full of holes anyway: you
didn't mark the view as a security_barrier view, and that treatment of
tableoid is hardly transparent. And I do hope that your real recipe
includes revoking public read access on pg_database_catalog.)

regards, tom lane

> On Sep 6, 2024, at 16:44, Basha <Basha(at)maxcontact(dot)com> wrote:
> If shadowing system catalogs via views is not a recommended path, we would be grateful for guidance on alternative approaches to achieve the same result—restricting visibility of databases in a multi-tenant environment while maintaining essential operations like backups. Specifically, is there a supported way to enforce database isolation at the system catalog level, or is there a possibility of introducing a more granular control over pg_dump in such cases?

The closest analogy that I've seen in the field is what Heroku does (did?) for database-based multitenancy, which is to assign very random names to the databases, without revealing any client names or other human-readable data. That the databases existed was still visible in pg_database, but it leaked no substantial information to users connected to other databases.

The code, provided is to recreate the problem if required. The access and barriers are taken care. Thanks,

Sent from Outlook for Android<https://aka.ms/AAb9ysg>
________________________________
From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Sent: Saturday, September 7, 2024 1:24:59 AM
To: Christophe Pettus <xof(at)thebuild(dot)com>
Cc: Basha <Basha(at)maxcontact(dot)com>; 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

[You don't often get email from tgl(at)sss(dot)pgh(dot)pa(dot)us(dot) Learn why this is important at https://aka.ms/LearnAboutSenderIdentification ]

Christophe Pettus <xof(at)thebuild(dot)com> writes:
> You've really stepped outside what is considered supported behavior here. That it worked at all was more accidental than a documented and supported feature. Shadowing system catalogs with views *is* going to break things, and that `allow_system_table_mods` has that potential is documented. I'm sure this is frustrating, but it's extremely unlikely that this will be considered a regression worth undoing a security fix for.

Indeed. You might look into enforcing the restriction you want
by attaching an RLS policy to pg_database, instead of this hack.
Mind you, we are unlikely to consider that supported either if push
comes to shove. But it would at least dodge your immediate problem.

(I'll just note that this implementation is full of holes anyway: you
didn't mark the view as a security_barrier view, and that treatment of
tableoid is hardly transparent. And I do hope that your real recipe
includes revoking public read access on pg_database_catalog.)

regards, tom lane
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.

Thank you, will there be any possibility of introducing a more granular control over pg_dump in such cases? Which would really helpful.

Thank you,
Basha
________________________________
From: Christophe Pettus <xof(at)thebuild(dot)com>
Sent: Saturday, September 7, 2024 1:29:52 AM
To: Basha <Basha(at)maxcontact(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

> On Sep 6, 2024, at 16:44, Basha <Basha(at)maxcontact(dot)com> wrote:
> If shadowing system catalogs via views is not a recommended path, we would be grateful for guidance on alternative approaches to achieve the same result—restricting visibility of databases in a multi-tenant environment while maintaining essential operations like backups. Specifically, is there a supported way to enforce database isolation at the system catalog level, or is there a possibility of introducing a more granular control over pg_dump in such cases?

The closest analogy that I've seen in the field is what Heroku does (did?) for database-based multitenancy, which is to assign very random names to the databases, without revealing any client names or other human-readable data. That the databases existed was still visible in pg_database, but it leaked no substantial information to users connected to other databases.

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.

> On Sep 6, 2024, at 18:03, Basha <Basha(at)maxcontact(dot)com> wrote:
>
> Thank you, will there be any possibility of introducing a more granular control over pg_dump in such cases? Which would really helpful.

If by "such cases" you mean the databases using random names, they're just databases with no special characteristics, so pg_dump handles them now.

I mean the case where, changes to system tables made. pg_dump to do granular control over it.
Which means the pg_dump will work on the dev environments too, when there is a change to the system tables.

As renaming all the database gives us option to anonymize the name but will still be showing up all the database to the customer and will add bit of confusion to them in connecting to their db.

Thank you,
Basha

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.

> On Sep 6, 2024, at 18:40, Basha <Basha(at)maxcontact(dot)com> wrote:
>
> I mean the case where, changes to system tables made. pg_dump to do granular control over it.
> Which means the pg_dump will work on the dev environments too, when there is a change to the system tables.

Anything's possible, but just speaking for myself, that doesn't seem like an attractive area of development. I don't think it's possible to guarantee that pg_dump (or anything else) will always work with arbitrary system catalog modifications.

The real problem you're trying to solve that that users can discover the existence of databases that they can't connect to. That's much more imaginable, although I'm not sure how practical it would be. At minimum, the default behavior would have to be the same as it is now (or lots of things that work now would break), so introducing a new role privilege of "can see all databases" wouldn't be a great way forward.

Christophe Pettus <xof(at)thebuild(dot)com> writes:
> The real problem you're trying to solve that that users can discover the existence of databases that they can't connect to. That's much more imaginable, although I'm not sure how practical it would be. At minimum, the default behavior would have to be the same as it is now (or lots of things that work now would break), so introducing a new role privilege of "can see all databases" wouldn't be a great way forward.

Even more to the point: that doesn't really seem like a problem
worth putting development and ongoing maintenance effort into.
What does it matter if someone can see that database XYZ exists,
as long as they can't connect to it?

regards, tom lane

> On Sep 6, 2024, at 19:17, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> What does it matter if someone can see that database XYZ exists,
> as long as they can't connect to it?

I think in the OP's case, the database naming convention leaked information about customers, but using random DB names (while maybe not aesthetically pleasing) gets around that issue.

Thanks @Christophe Pettus!

The main reason, this has been working since PG10. Until the new update on pg_dump code. It would have been great if the specific area was fixed instead of completely not allowing to make change to the pg_database table.

As you mentioned, instead of having a role to view as database, having a role group with restricted permission(can view only db with connect permission) would be great to have.

Thanks again,
Basha
________________________________
From: Christophe Pettus <xof(at)thebuild(dot)com>
Sent: Saturday, September 7, 2024 2:48:57 AM
To: Basha <Basha(at)maxcontact(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

> On Sep 6, 2024, at 18:40, Basha <Basha(at)maxcontact(dot)com> wrote:
>
> I mean the case where, changes to system tables made. pg_dump to do granular control over it.
> Which means the pg_dump will work on the dev environments too, when there is a change to the system tables.

Anything's possible, but just speaking for myself, that doesn't seem like an attractive area of development. I don't think it's possible to guarantee that pg_dump (or anything else) will always work with arbitrary system catalog modifications.

The real problem you're trying to solve that that users can discover the existence of databases that they can't connect to. That's much more imaginable, although I'm not sure how practical it would be. At minimum, the default behavior would have to be the same as it is now (or lots of things that work now would break), so introducing a new role privilege of "can see all databases" wouldn't be a great way forward.
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.

On 9/6/24 22:19, Christophe Pettus wrote:
>> On Sep 6, 2024, at 19:17, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> What does it matter if someone can see that database XYZ exists,
>> as long as they can't connect to it?
> I think in the OP's case, the database naming convention leaked
> information about customers, but using random DB names (while maybe
> not aesthetically pleasing) gets around that issue.

Yeah, but there are other issues, e.g. leaked usernames of the other
customers too.

There is definitely demand for using PostgreSQL in multitenant use
cases, but there are multiple problems to solve before people with those
use cases will be happy.

I think Tom's suggestion regarding RLS on system catalogs was one
approach bandied about in the past, but unless I am mistaken we never
committed the patch to allow RLS on system catalogs (or did I miss that?)

--
Joe Conway
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com

On Saturday, September 7, 2024, Joe Conway <mail(at)joeconway(dot)com> wrote:
>
>
> Yeah, but there are other issues, e.g. leaked usernames of the other
> customers too.

Yeah…multi-tenant clusters are great so long as you aren’t giving out
logins to tenants. For tenants that need a login to the database they
really need their own cluster and OS-level resource management.

I wonder how hard a “cluster coordinator” binary would be to implement -
something that takes an overall configuration and some shared memory and
effectively allocates those among multiple clusters on the same host. You
can get some of this, like a total connection count, from a pooler.

That said, I’d need to go back and see the arguments for why we don’t just
filter the list of globals to whatever the logged in role is capable of
using. I wonder whether the concerns are with schema objects and globals
are just collateral damage.

David J.

Joe Conway <mail(at)joeconway(dot)com> writes:
> I think Tom's suggestion regarding RLS on system catalogs was one
> approach bandied about in the past, but unless I am mistaken we never
> committed the patch to allow RLS on system catalogs (or did I miss that?)

I experimented before proposing that, and we will let you create
RLS policies on system catalogs (if allow_system_table_mods = on).
Of course the system itself will ignore those, but I don't see
why they would not work for user-issued queries.

regards, tom lane

"David G. Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> writes:
> That said, I’d need to go back and see the arguments for why we don’t just
> filter the list of globals to whatever the logged in role is capable of
> using.

Filtering roles this way would require a whole lot more thought,
and I doubt you could get away with just hiding roles that the current
user can't become. As an example, it would be sad if \df+ stopped
showing any built-in functions because it failed to join their
pg_proc.proowner to pg_roles.

In the particular case of \df+, it looks like it'd actually continue
to work because it goes through pg_get_userbyid() which wouldn't pay
any attention to RLS anyway. ACL display is another obvious place
that would leak role names. So there's also a boatload of questions
around leakage of the supposedly hidden roles.

pg_database is not nearly as entwined with the local catalogs, so
I think what I suggested would be enough in practice for that.

regards, tom lane

As suggested, tried creating the below RLS policy on the pg_database table. But seems it does not check for the connect permission.

Below are the script executed as superuser

CREATE DATABASE testrls
WITH
OWNER = postgres
ENCODING = 'UTF8';

REVOKE ALL ON DATABASE testrls FROM public;

ALTER TABLE pg_catalog.pg_database ENABLE ROW LEVEL SECURITY;

CREATE POLICY connect_permission_policy
ON pg_catalog.pg_database
FOR SELECT
USING (has_database_privilege(current_user, datname, 'CONNECT'));

Connected as user1 (user does not have connect permission) and executed the below query

SELECT (has_database_privilege(current_user, datname, 'CONNECT')), datname FROM pg_catalog.pg_database;

But the result still returns back with the database 'testrls'.

Results as below

"has_database_privilege","datname"
True,"template1"
True,"template0"
True,"postgres"
False,"testrls"

Could you please check the above queries and let me know if anything needs to be changed in order to make it work

Thanks,
Bash

-----Original Message-----
From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Sent: 07 September 2024 16:29
To: Joe Conway <mail(at)joeconway(dot)com>
Cc: Christophe Pettus <xof(at)thebuild(dot)com>; Basha <Basha(at)maxcontact(dot)com>; 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

Joe Conway <mail(at)joeconway(dot)com> writes:
> I think Tom's suggestion regarding RLS on system catalogs was one
> approach bandied about in the past, but unless I am mistaken we never
> committed the patch to allow RLS on system catalogs (or did I miss
> that?)

I experimented before proposing that, and we will let you create RLS policies on system catalogs (if allow_system_table_mods = on).
Of course the system itself will ignore those, but I don't see why they would not work for user-issued queries.

regards, tom lane
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.

Basha <Basha(at)maxcontact(dot)com> writes:
> As suggested, tried creating the below RLS policy on the pg_database table. But seems it does not check for the connect permission.

[ pokes around... ] Ah indeed, because check_enable_rls has a
hard-wired assumption that no built-in table has any RLS:

/* Nothing to do for built-in relations */
if (relid < (Oid) FirstNormalObjectId)
return RLS_NONE;

I wonder if we should remove that? It's only saving one syscache
lookup (and if we were hot about that, most of the callers have
an open Relation that could be passed). I did check that this
policy works as-expected if that test is deleted.

Still, making such a change would amount to actively supporting RLS
on catalogs, rather than just a laissez-faire "you can use it if it
works" approach. I'm not convinced we want to go there, because for
a lot of the catalogs there are going to be additional access pathways
such as I mentioned for roles. I do not want to buy into those things
becoming arguable security bugs. And I *definitely* don't want to try
to close off system accesses to catalogs based on RLS.

regards, tom lane

> On Sep 7, 2024, at 10:16, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> Still, making such a change would amount to actively supporting RLS
> on catalogs, rather than just a laissez-faire "you can use it if it
> works" approach.

I don't want to get into analysis paralysis on this, but I think it makes more sense to have proactive multi-tenancy features, rather than trying to press the existing infrastructure into service for it. This means it's a couple of major versions out at a minimum, which is annoying for existing users who want multi-tenancy based on databases. But companies like Heroku have been making it (somewhat imperfectly) work for over a decade now, so it's not impossible.

The fix in PG16.4, entirely prevents changes to pg_database, Is there any possibility of a more targeted approach. Specifically, I'd like to know if there's an option to modify this fix so that it applies only to specific areas or actions, rather than enforcing a complete restriction.

The suggestion to add RLS to the system catalog table would be great solution in order to find a solution. Currently we are in a stranded position on this issue.

Thank you very much for your support and guidance.

-----Original Message-----
From: Christophe Pettus <xof(at)thebuild(dot)com>
Sent: 07 September 2024 18:29
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Basha <Basha(at)maxcontact(dot)com>; Joe Conway <mail(at)joeconway(dot)com>; 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

> On Sep 7, 2024, at 10:16, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> Still, making such a change would amount to actively supporting RLS on
> catalogs, rather than just a laissez-faire "you can use it if it
> works" approach.

I don't want to get into analysis paralysis on this, but I think it makes more sense to have proactive multi-tenancy features, rather than trying to press the existing infrastructure into service for it. This means it's a couple of major versions out at a minimum, which is annoying for existing users who want multi-tenancy based on databases. But companies like Heroku have been making it (somewhat imperfectly) work for over a decade now, so it's not impossible.
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.

Basha <Basha(at)maxcontact(dot)com> writes:
> The fix in PG16.4, entirely prevents changes to pg_database, Is
> there any possibility of a more targeted approach.

If we allow pg_database to be replaced by a view, we have the same
hazard that the security patch means to fix: namely that the view
might be hostile, or even just that it might innocently lie to us
resulting in incorrect pg_dump output. I'm uninterested in poking
a hole in that security defense.

I don't think we've ended the discussion on whether to remove the
check that's preventing using RLS instead. But even if we choose
to do that, you're still depending on something that's not
supported and might break again in future. To be absolutely
clear: *nothing* you might use allow_system_table_mods to do
is considered supported for production purposes, and we will not
apologize for breaking it.

You really ought to think about how badly do you need to hide the
existence of other databases. It seems like a mighty low-priority
requirement from here, especially if you can't also hide the existence
of other roles.

regards, tom lane

Thank you for considering the RLS. Which would really help in solving.

All Iam looking is some kind of solution to the issue.

With regards to the roles , I have added the below into the view pg_catalog.pg_roles.

WHERE pg_has_role(CURRENT_USER, pg_authid.oid, 'member'::text) OR (pg_authid.rolname = ANY (ARRAY['postgres'::name);

This way, it will only show the roles they are member of. This makes sure it doesn't show other db roles.

Thank you,
Basha

Sent from Outlook for Android<https://aka.ms/AAb9ysg>
________________________________
From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Sent: Saturday, September 7, 2024 7:21:33 PM
To: Basha <Basha(at)maxcontact(dot)com>
Cc: Christophe Pettus <xof(at)thebuild(dot)com>; Joe Conway <mail(at)joeconway(dot)com>; 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

Basha <Basha(at)maxcontact(dot)com> writes:
> The fix in PG16.4, entirely prevents changes to pg_database, Is
> there any possibility of a more targeted approach.

If we allow pg_database to be replaced by a view, we have the same
hazard that the security patch means to fix: namely that the view
might be hostile, or even just that it might innocently lie to us
resulting in incorrect pg_dump output. I'm uninterested in poking
a hole in that security defense.

I don't think we've ended the discussion on whether to remove the
check that's preventing using RLS instead. But even if we choose
to do that, you're still depending on something that's not
supported and might break again in future. To be absolutely
clear: *nothing* you might use allow_system_table_mods to do
is considered supported for production purposes, and we will not
apologize for breaking it.

You really ought to think about how badly do you need to hide the
existence of other databases. It seems like a mighty low-priority
requirement from here, especially if you can't also hide the existence
of other roles.

regards, tom lane
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.

Basha <Basha(at)maxcontact(dot)com> writes:
> With regards to the roles , I have added the below into the view pg_catalog.pg_roles.

> WHERE pg_has_role(CURRENT_USER, pg_authid.oid, 'member'::text) OR (pg_authid.rolname = ANY (ARRAY['postgres'::name);

> This way, it will only show the roles they are member of. This makes sure it doesn't show other db roles.

You might consider the implications of this:

regression=> do $$ begin
for uid in 1..1000000 loop
if uid::regrole::text != uid::text then
raise notice 'uid % is %', uid, uid::regrole;
end if;
end loop;
end $$;
NOTICE: uid 10 is postgres
NOTICE: uid 3373 is pg_monitor
NOTICE: uid 3374 is pg_read_all_settings
NOTICE: uid 3375 is pg_read_all_stats
NOTICE: uid 3377 is pg_stat_scan_tables
NOTICE: uid 4200 is pg_signal_backend
NOTICE: uid 4544 is pg_checkpoint
...

On my workstation, it takes about half a second to scan the first
million possible user OIDs, so a complete scan of the OID space
would take only half an hour. In practice you'd seldom need to
scan the whole space.

regards, tom lane