From: | "Radoulov, Dimitre" <cichomitiko(at)gmail(dot)com> |
---|---|
To: | pgsql-admin(at)postgresql(dot)org |
Subject: | vacuumlo Permission Discrepancy Between Prod and Dev on AWS Aurora PostgreSQL |
Date: | 2025-06-04 12:30:45 |
Message-ID: | CAGJBphQn0eTqA0UaHEx7GnymcRRuhTtv6MLkAAvdEJqnrF4UMA@mail.gmail.com |
Views: | Whole Thread | Raw Message | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin |
We have several AWS Aurora PostgreSQL 11.21 clusters. Our goal is to remove
large objects (LOBs) stored in the pg_largeobject table. The application
code has already been refactored to stop using LOBs.
To test the cleanup process, we cloned the production database and ran
vacuumlo. Since the LOBs are associated in a custom way, all of them were
treated as orphans and successfully removed — which is exactly what we
wanted.
Here’s where it gets tricky: in the development environment, running the
same vacuumlo command fails with the following error:
Removing lo 19481964
vacuumlo: error: failed to remove lo 19481964: ERROR: must be owner of
large object 19481964
Removal from database "hecosdb" failed at object 0 of 1000.
In both the production and development environments, the LOBs are owned by
the same three application users. The user running vacuumlo is not the
owner of the LOBs in either environment. Also, there are no superuser
privileges available on AWS RDS.
Despite this, vacuumlo works fine in production using the RDS master user,
but fails in development with the same user due to permission issues.
As a workaround, we're currently running an ALTER LARGE OBJECT ... OWNER TO
username loop to reassign ownership. However, this process is extremely
slow, as we can only execute the changes one by one due to resource
constraints.
Why does vacuumlo succeed in production but fail in development, even
though the setup appears similar?
Thank you in advance.
Best regards
Dimitre
From | Date | Subject | |
---|---|---|---|
Next Message | Sbob | 2025-06-05 14:01:02 | Failed to download metadata for repo 'pgdg-common' |
Previous Message | Laurenz Albe | 2025-06-03 13:36:04 | Re: Question about permissions in the Schema |