pg_largeobject access on heroku

Question

I'm trying to clean up my postgres database on heroku where some large objects have gotten out of control and I want to remove large objects which aren't used anymore.

On my dev machine, I can do:

select distinct loid from pg_largeobject 
Where loid Not In (select id from table )

then i run:

SELECT lo_unlink(loid)

on each of those IDs.

But on heroku, i can't run any selects on pg_largeobject, as even

select * from pg_largeobject limit 1;

gives me an error:

ERROR:  permission denied for relation pg_largeobject

Any suggestions how to work around this, or actually even why we don't have read access on pg_largeobject in heroku?

Answer 1

Since PostgreSQL 9.0, a non-superuser can't access pg_largeobject. This is documented in the release notes:

Add the ability to control large object (BLOB) permissions with GRANT/REVOKE (KaiGai Kohei)

Formerly, any database user could read or modify any large object. Read and write permissions can now be granted and revoked per large object, and the ownership of large objects is tracked.

If it works on your development instance, it's either because it's version 8.4 or lower, or because you're logged in as a superuser.

If you can't log in as a superuser on heroku, I guess you could dump the remote database with pg_dump, then reload it locally, identify the leaked OIDs as a local superuser, put them in a script with the lo_unlink commands, and finally play this script against the heroku instance.

Update:

Based on how the psql \dl command queries the database, it appears that pg_catalog.pg_largeobject_metadata can be used to retrieve the OIDs and ownership of all large objects, through this query:

SELECT oid as "ID",
  pg_catalog.pg_get_userbyid(lomowner) as "Owner",
  pg_catalog.obj_description(oid, 'pg_largeobject') as "Description"
  FROM pg_catalog.pg_largeobject_metadata   ORDER BY oid

So your initial query finding the leaked large objects could be changed for non-superusers with 9.0+ into:

select oid from pg_largeobject_metadata
 Where oid Not In (select id from table )

and if necessary, a condition on lomowner could be added to filter on the large objects owned by a specific user.