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

AKASH <akashbhujbal7051@gmail.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

> On Jan 27, 2025, at 21:29, AKASH <akashbhujbal7051@gmail.com> wrote:
>     • Create a database, install the pg_trgm extension, create a role (app_user), and change ownership of the
similarityfunction to app_user. 
>     • Export the database pg_dump to create a dump file.
>     • Restore the dump into a target environment where it app_user does not exist.
>     • Ownership of the similarity function reverts to the restoring user.
>     • Applications dependent on app_user permissions fail with permission errors.

It's not clear to me exactly why you need to have different parts of an extension owned by different roles.  I believe
thecorrect analogy is that a table is owned by a single role, but that does not prevent it from being accessed and used
byother roles that have appropriate permissions.  Similarly, there's no need for individual components of extensions to
havedifferent owners.  You can grant the right permissions to the roles that need access to the extension without
havingto grant them ownership.  If you want more fine-grained access control, where some roles can use some components
ofan extension and others can't, it's best to wrap the extension functions or views in user-defined wrappers, and then
grantpermissions appropriately onto those. 

In your particular example, that's not a PostgreSQL bug, but a bug in your process.  You need to make sure that the
targetsystem has the correct roles before restoring into it.  PostgreSQL doesn't (and I feel safe to say never will)
createroles on the fly, and dumping and restoring global objects like roles is a separate step from a
pg_dump/pg_restore.

Addressing this limitation—either through improved documentation or tooling adjustments—would greatly benefit users managing complex permission models.

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

            regards, tom lane