PostgreSQL: Permission denied for large object

Question

When migrating from my development to staging environment, I encountered the following error during a hibernate sql call to the PostgreSQL database on the staging server.

There is no error in the development environment. I am assuming it is a privileges issue on the satging db. However, I have never seen this error and I do not know where to look. Can anyone advise?

Caused by: java.io.IOException: org.postgresql.util.PSQLException: ERROR: permission denied for large object 109138 at org.postgresql.largeobject.BlobInputStream.read(BlobInputStream.java:123) at java.io.InputStream.read(InputStream.java:170) at sun.nio.cs.StreamDecoder.readBytes(StreamDecoder.java:284) at sun.nio.cs.StreamDecoder.implRead(StreamDecoder.java:326) at sun.nio.cs.StreamDecoder.read(StreamDecoder.java:178) at java.io.InputStreamReader.read(InputStreamReader.java:184) at org.hibernate.type.descriptor.java.DataHelper.extractString(DataHelper.java:71)

Answer 1

Since PostgreSQL 9.0, large objects have permissions (column lomacl of table pg_largeobject_metadata). By default, nobody except the owner (column lomowner) has any permissions for a large object.

So it seems that either it is a version migration problem (e.g., you didn't use pg_dump from the newer version to create the dump), or you are trying to access the large objects as a different user in your staging environment.

Answer 2

In postgresql.conf change lo_compat_privileges to on. This worked for me