Re: Issue with pg_dump Including Ownership Metadata for pg_trgm Extension in PostgreSQL 17

AKASH <akashbhujbal7051(at)gmail(dot)com> writes:
> Steps to reproduce:

> 1. Install the pg_trgm extension.
> 2. Change the ownership of the objects created by the extension.

We don't support *any* hand modification of extension member objects,
other than grant/revoke operations. Having said that, pg_dump doesn't
record ownership of extension members, because it doesn't record
extension members. What it emits is CREATE EXTENSION, plus possibly
GRANT/REVOKE if any member objects' permissions have changed since
the original CREATE EXTENSION.

> 4. Restore the dump in a different environment where ownership constraints
> differ.

What is happening there is not what you claim. What is happening
is that the CREATE EXTENSION is run by the restoring user, who
becomes the owner of the extension and its contained objects.

In the original conception of extensions, this didn't matter a lot:
the owner pretty much had to be a superuser, and since all superusers
are interchangeable from a permissions standpoint, it didn't matter
if it was a different superuser in the restored database.

Since we invented "trusted" extensions, the owner of the extension
itself could be a non-superuser. pg_dump fails to reproduce that
(with or without --no-owner), and that's a deficiency we should fix
but haven't yet. However, the individual objects in such an extension
are still owned by a superuser for security reasons we needn't get
into here. That being the case, it still doesn't matter too much
which superuser that is.

> Actual behavior: Ownership metadata is still included in the dump, causing
> issues when restoring to different environments.

If you can provide any evidence that that actually happens, I'd
be interested to see it. I just re-tested the point and what
I see in the dump has nothing about extension member objects.
There is

--
-- Name: pg_trgm; Type: EXTENSION; Schema: -; Owner: -
--

CREATE EXTENSION IF NOT EXISTS pg_trgm WITH SCHEMA public;

and nothing else.

> I would appreciate any assistance in resolving this issue, or any guidance
> on how to work around this limitation in the meantime.

Changing extension member objects directly (not through an extension
update script) is not supported and is unlikely ever to be supported.
The reason is that you lose any ability to re-create the state of the
extension when you make changes that aren't memorialized in an update
script. The sole exception to that is that we do track manual
granting/revoking of permissions ... and that's proven to be enough
of a mess that nobody is likely to consider extending it to any
other object properties.

Rethink whatever it is you're doing that leads to wanting to do that.

regards, tom lane