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

PG Bug reporting form <noreply@postgresql.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@postgresql.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&gt;
________________________________
From: Christophe Pettus <xof@thebuild.com>
Sent: Friday, September 6, 2024 8:07:35 PM
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:
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&gt;
________________________________
From: Tom Lane <tgl@sss.pgh.pa.us>
Sent: Friday, September 6, 2024 7:49:07 PM
To: Basha <basha@maxcontact.com>
Cc: pgsql-bugs@lists.postgresql.org <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 tgl@sss.pgh.pa.us. Learn why this is important at https://aka.ms/LearnAboutSenderIdentification ]

PG Bug reporting form <noreply@postgresql.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@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 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@maxcontact.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@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 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@maxcontact.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@thebuild.com>
Sent: Friday, September 6, 2024 10:24:36 PM
To: Basha <Basha@maxcontact.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

On Sep 6, 2024, at 13:46, Basha <Basha@maxcontact.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@thebuild.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@maxcontact.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&gt;
________________________________
From: Tom Lane <tgl@sss.pgh.pa.us>
Sent: Saturday, September 7, 2024 1:24:59 AM
To: Christophe Pettus <xof@thebuild.com>
Cc: Basha <Basha@maxcontact.com>; 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

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

Christophe Pettus <xof@thebuild.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@thebuild.com>
Sent: Saturday, September 7, 2024 1:29:52 AM
To: Basha <Basha@maxcontact.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

On Sep 6, 2024, at 16:44, Basha <Basha@maxcontact.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@maxcontact.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@maxcontact.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@thebuild.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@sss.pgh.pa.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.