Postgres – pg_dump Permission Denied for Large Object

postgresql

When doing pg_dump as a read-only user readonly, it reports an error:

pg_dump: could not open large object 19536: ERROR:  permission denied for large object 19536

Then I connect as the superuser postgres, but SELECT * FROM pg_largeobject returns nothing.

When I run SELECT * FROM pg_largeobject_metadata WHERE oid=19536, I get 

lomowner    lomacl
10

Then with SELECT rolname FROM pg_roles WHERE oid=10; I get postgres which is me (also the superuser).

How can I find this large object and grant SELECT or whatever read privilege to user readonly so it can do pg_dump?

Version: 11.4 (Ubuntu 11.4-1.pgdg18.04+1)

Best Answer

Unlike pg_largeobject, pg_largeobject_metadata can be read by all users and has one row per large object.

Try:

SELECT * FROM pg_largeobject_metadata WHERE oid = 19536;

The lomowner column holds the OID of the owner of the large object. If the account used for pg_dump is not superuser and not the owner of the large object and has not been granted rights to it, the failure to open it is expected.

Then I connect as the superuser postgres, but SELECT * FROM pg_largeobject returns nothing.

Having no row in pg_largeobject despite having large objects in pg_largeobject_metadata is possible if the large objects are all empty (their size is 0 byte).

Permissions on large objects can be granted individually with a command like:

 GRANT SELECT ON LARGE OBJECT 19536 TO readonly;

I don't think there is any way to grant permissions to all large objects, but alternatively the permissions checks on a database can be neutralized entirely by a superuser with this command:

 ALTER DATABASE test SET lo_compat_privileges TO on;

Finally if you don't need that empty large object, it can be removed in SQL with 

SELECT lo_unlink(19536);
Related Topic