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