pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedEvidence of Ownership Issues During Restoration of Extension Member Objects
5+ messages / 4 participants
[nested] [flat]
* Evidence of Ownership Issues During Restoration of Extension Member Objects
@ 2025-01-28 05:29 AKASH <akashbhujbal7051@gmail.com>
2025-01-28 05:47 ` Re: Evidence of Ownership Issues During Restoration of Extension Member Objects Christophe Pettus <xof@thebuild.com>
2025-01-28 05:56 ` Re: Evidence of Ownership Issues During Restoration of Extension Member Objects David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 2 replies; 5+ messages in thread
From: AKASH @ 2025-01-28 05:29 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org
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
>
Attachments:
pg_dump_issue.png (image/png, 123.4K) download
view image
* Re: Evidence of Ownership Issues During Restoration of Extension Member Objects
2025-01-28 05:29 Evidence of Ownership Issues During Restoration of Extension Member Objects AKASH <akashbhujbal7051@gmail.com>
@ 2025-01-28 05:47 ` Christophe Pettus <xof@thebuild.com>
1 sibling, 0 replies; 5+ messages in thread
From: Christophe Pettus @ 2025-01-28 05:47 UTC (permalink / raw)
To: AKASH <akashbhujbal7051@gmail.com>; +Cc: pgsql-bugs@lists.postgresql.org
> 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.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Evidence of Ownership Issues During Restoration of Extension Member Objects
2025-01-28 05:29 Evidence of Ownership Issues During Restoration of Extension Member Objects AKASH <akashbhujbal7051@gmail.com>
@ 2025-01-28 05:56 ` David G. Johnston <david.g.johnston@gmail.com>
2025-01-28 06:33 ` Re: Evidence of Ownership Issues During Restoration of Extension Member Objects Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 5+ messages in thread
From: David G. Johnston @ 2025-01-28 05:56 UTC (permalink / raw)
To: AKASH <akashbhujbal7051@gmail.com>; +Cc: pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
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.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Evidence of Ownership Issues During Restoration of Extension Member Objects
2025-01-28 05:29 Evidence of Ownership Issues During Restoration of Extension Member Objects AKASH <akashbhujbal7051@gmail.com>
2025-01-28 05:56 ` Re: Evidence of Ownership Issues During Restoration of Extension Member Objects David G. Johnston <david.g.johnston@gmail.com>
@ 2025-01-28 06:33 ` Tom Lane <tgl@sss.pgh.pa.us>
2025-01-28 06:46 ` Re: Evidence of Ownership Issues During Restoration of Extension Member Objects AKASH <akashbhujbal7051@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: Tom Lane @ 2025-01-28 06:33 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: AKASH <akashbhujbal7051@gmail.com>; pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
"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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Evidence of Ownership Issues During Restoration of Extension Member Objects
2025-01-28 05:29 Evidence of Ownership Issues During Restoration of Extension Member Objects AKASH <akashbhujbal7051@gmail.com>
2025-01-28 05:56 ` Re: Evidence of Ownership Issues During Restoration of Extension Member Objects David G. Johnston <david.g.johnston@gmail.com>
2025-01-28 06:33 ` Re: Evidence of Ownership Issues During Restoration of Extension Member Objects Tom Lane <tgl@sss.pgh.pa.us>
@ 2025-01-28 06:46 ` AKASH <akashbhujbal7051@gmail.com>
0 siblings, 0 replies; 5+ messages in thread
From: AKASH @ 2025-01-28 06:46 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: David G. Johnston <david.g.johnston@gmail.com>; pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
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
>
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2025-01-28 06:46 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-01-28 05:29 Evidence of Ownership Issues During Restoration of Extension Member Objects AKASH <akashbhujbal7051@gmail.com>
2025-01-28 05:47 ` Christophe Pettus <xof@thebuild.com>
2025-01-28 05:56 ` David G. Johnston <david.g.johnston@gmail.com>
2025-01-28 06:33 ` Tom Lane <tgl@sss.pgh.pa.us>
2025-01-28 06:46 ` AKASH <akashbhujbal7051@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox