On Tue, Jan 28, 2025 at 12:03 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
"David G. Johnston" <david.g.johnston@gmail.com> writes: > As an aside, none of the use cases are framed to directly motivate why you > would take the actions described, so we are still just left seeing > unnecessary attempts to make unsupported changes to the system permissions > setup and responding with “don’t do that”.
The short answer here is that dump/restore is not intended to reproduce any manual changes you might've made to the contents of an extension --- with the sole exception, since around v9.6, that it will try to reproduce permissions changes (NOT ownership changes) for objects contained in the extension. TBH, I think that part was a misfeature we shouldn't have accepted, because it breaks the abstraction that an extension is a black box whose contents are described by on-disk script files.
The use-case driving that definition is that dump/restore will install whatever version of the extension is considered current on the target system, which might well be different from what the source system had (which indeed might not be available on the target). So the more the dump script assumes about what is in the extension, the more likely it is to fail.
You might want to look into pg_upgrade, which has a different charter: it tries to reproduce the extension contents exactly.
Anyway, we are not going to accept the described scenario as a bug. As I already said, manual alteration of the contents of an extension is unsupported --- and *in particular* that means we will not promise that dump/restore does anything useful with such cases.
There are certainly things I don't like about what dump/restore does with extension object ownership: it doesn't really attempt at all to duplicate the extension's original ownership. But anything we might do to fix that would make it even less likely that manual alteration of individual member objects' ownership would be preserved.
