Thread

1. Issue with pg_dump Including Ownership Metadata for pg_trgm Extension in PostgreSQL 17

   AKASH <akashbhujbal7051@gmail.com> — 2024-12-04T09:42:21Z

   Dear PostgreSQL Bug Team,
   
   I am encountering an issue in PostgreSQL 17 with pg_dump when working with
   the pg_trgm extension. Specifically, when using pg_dump to back up a
   database containing the pg_trgm extension, ownership metadata for extension
   member objects is included in the dump, even when the --no-owner flag is
   used.
   
   This leads to conflicts during restores in environments where ownership
   constraints differ, disrupting migrations and automated workflows. It is
   particularly problematic when ownership has been changed after the
   extension was initially created (via CREATE EXTENSION).
   
   Steps to reproduce:
   
      1.
   
      Install the pg_trgm extension.
      2.
   
      Change the ownership of the objects created by the extension.
      3.
   
      Run pg_dump with the --no-owner flag.
      4.
   
      Restore the dump in a different environment where ownership constraints
      differ.
   
   Expected behavior: Ownership metadata should not be included in the dump
   when the --no-owner flag is specified, especially for objects created by
   the pg_trgm extension.
   
   Actual behavior: Ownership metadata is still included in the dump, causing
   issues when restoring to different environments.
   
   This issue has been persistent across multiple versions of PostgreSQL and
   significantly impacts automated deployment and migration workflows.
   
   I would appreciate any assistance in resolving this issue, or any guidance
   on how to work around this limitation in the meantime.
   
   Thank you for your attention to this matter.
   Best regards,
   Akash Bhujbal
   

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

   AKASH <akashbhujbal7051@gmail.com> — 2024-12-04T09:43:04Z

   On Wed, Dec 4, 2024 at 3:12 PM AKASH <akashbhujbal7051@gmail.com> wrote:
   
   > Dear PostgreSQL Bug Team,
   >
   > I am encountering an issue in PostgreSQL 17 with pg_dump when working
   > with the pg_trgm extension. Specifically, when using pg_dump to back up a
   > database containing the pg_trgm extension, ownership metadata for
   > extension member objects is included in the dump, even when the --no-owner
   > flag is used.
   >
   > This leads to conflicts during restores in environments where ownership
   > constraints differ, disrupting migrations and automated workflows. It is
   > particularly problematic when ownership has been changed after the
   > extension was initially created (via CREATE EXTENSION).
   >
   > Steps to reproduce:
   >
   >    1.
   >
   >    Install the pg_trgm extension.
   >    2.
   >
   >    Change the ownership of the objects created by the extension.
   >    3.
   >
   >    Run pg_dump with the --no-owner flag.
   >    4.
   >
   >    Restore the dump in a different environment where ownership
   >    constraints differ.
   >
   > Expected behavior: Ownership metadata should not be included in the dump
   > when the --no-owner flag is specified, especially for objects created by
   > the pg_trgm extension.
   >
   > Actual behavior: Ownership metadata is still included in the dump,
   > causing issues when restoring to different environments.
   >
   > This issue has been persistent across multiple versions of PostgreSQL and
   > significantly impacts automated deployment and migration workflows.
   >
   > I would appreciate any assistance in resolving this issue, or any guidance
   > on how to work around this limitation in the meantime.
   >
   > Thank you for your attention to this matter.
   > Best regards,
   > Akash Bhujbal
   >
   

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

   Tom Lane <tgl@sss.pgh.pa.us> — 2024-12-04T16:37:38Z

   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
   
   
   
   

4. Evidence of Ownership Issues During Restoration of Extension Member Objects

   AKASH <akashbhujbal7051@gmail.com> — 2025-01-28T05:29:11Z

   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
   
   >
   

5. Re: Evidence of Ownership Issues During Restoration of Extension Member Objects

   Christophe Pettus <xof@thebuild.com> — 2025-01-28T05:47:15Z

   
   > 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 similarity function 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 the correct analogy is that a table is owned by a single role, but that does not prevent it from being accessed and used by other roles that have appropriate permissions.  Similarly, there's no need for individual components of extensions to have different owners.  You can grant the right permissions to the roles that need access to the extension without having to grant them ownership.  If you want more fine-grained access control, where some roles can use some components of an extension and others can't, it's best to wrap the extension functions or views in user-defined wrappers, and then grant permissions 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 target system has the correct roles before restoring into it.  PostgreSQL doesn't (and I feel safe to say never will) create roles on the fly, and dumping and restoring global objects like roles is a separate step from a pg_dump/pg_restore.
   
   
   

6. Re: Evidence of Ownership Issues During Restoration of Extension Member Objects

   David G. Johnston <david.g.johnston@gmail.com> — 2025-01-28T05:56:32Z

   On Monday, January 27, 2025, AKASH <akashbhujbal7051@gmail.com> wrote:
   
   > Addressing this limitation—either through improved documentation or
   > tooling adjustments—would greatly benefit users managing complex permission
   > models.
   >
   
   That most likely means taking Tom’s email of restrictions and adding them
   to the documentation somewhere.  We don’t really have a good way to modify
   tooling to prohibit superusers from doing stuff like this that breaks their
   system.  But this should not be impacting production use cases if proper
   testing of backup/restore is happening.
   
   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”.  An extension like pg_trgm has
   little to no security implications necessitating complex permission setup,
   AFAICS.
   
   David J.
   

7. Re: Evidence of Ownership Issues During Restoration of Extension Member Objects

   Tom Lane <tgl@sss.pgh.pa.us> — 2025-01-28T06:33:50Z

   "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
   
   
   
   

8. Re: Evidence of Ownership Issues During Restoration of Extension Member Objects

   AKASH <akashbhujbal7051@gmail.com> — 2025-01-28T06:46:58Z

   Thanks for the explanation.
   
   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.
   >
   >                         regards, tom lane
   >