Evidence of Ownership Issues During Restoration of Extension Member Objects

Dear Tom Lane,

Thank you for your detailed response. I understand your points about how
PostgreSQL handles extension members, particularly the fact that manual
modifications to extension member objects are unsupported. However, there
are specific use cases where ownership changes or mismatches during
restoration can result in operational failures. I would like to provide a
reproducible example and highlight scenarios where this behavior causes
issues.This issue needs to be corrected because preserving proper ownership
during dump/restore operations is critical for environments with strict
role-based access controls.

Steps to Reproduce the Issue:

   1. Create a database, install the pg_trgm extension, create a role (
   app_user), and change ownership of the similarity function to app_user.
   2. Export the database pg_dump to create a dump file.
   3. Restore the dump into a target environment where it app_user does not
   exist.
   4. Ownership of the similarity function reverts to the restoring user.
   5. Applications dependent on app_user permissions fail with permission
   errors.

Applications relying onapp_user specific permissions or business logic fail
after restoration because the ownership reverts to the restoring user. For
example, an application using specific permissionsapp_user to execute
similarity fails due to missing privileges in the target environment.

This behavior highlights inconsistencies during restoration and the need
for better handling of ownership metadata.

Use Cases Affected:
Text Similarity Search: E-commerce apps using app_user secure text
similarity queries fail post-restoration.
Audit Logs Access: Financial systems with role-based access for audit
queries face compliance and operational issues.
Healthcare Data Updates: Ownership mismatches prevent updates to patient
data, causing delays in healthcare operations.
Multi-Tenant SaaS Platforms: Missing tenant roles block access to isolated
tenant data, affecting SaaS functionality.

While PostgreSQL does not natively track extension member ownership in
dumps, the lack of support for preserving ownership metadata can lead to
operational challenges in role-specific environments. Addressing this
limitation—either through improved documentation or tooling
adjustments—would greatly benefit users managing complex permission models.

Please let me know if additional evidence or clarification is needed.
Best regards,
Akash Bhujbal

>