Re: [EXT]: Re: BUG #18604: Regression in PostgreSQL 16.4: pg_dump Prevents Essential System Table Modifications

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.