Error while trying to perform postgresql database backup

Question

I am trying to perform a backup of a database in Postgresql, however I am getting the following error:

erro ao ler objeto grande 118287: ERROR: permission denied for large object 118287

I searched for the error and found the following information

I performed the indicated commands but the error persists, someone has already gone through it and found a solution?

Answer 1

Since version 9.0, each large Object no Postgresql has its OID, as well as tables, indexes etc., and should not be treated as a simple record of the table pg_largeobject and therefore not automatically readable by the user who owns this table. You can list the large Objects and their respective owners via psql with the command \lo_list:

nunks=# \lo_list
        Large objects
  ID   | Owner | Description 
-------+-------+-------------
 16820 | nunks | Smiley :D
(1 row)

To grant a user the reading rights of large Object specified in the question, use:

GRANT SELECT ON LARGE OBJECT 118287 TO nome_do_usuario;

As your case implies reading the entire database, I suggest backing up with a user who has permission to read all objects. A SUPERUSER being the simplest solution to maintain, in case you only have problems when running backups.

---

Another alternative is to modify the behavior of the DATABASE in question using the option lo_compat_privileges. So all the large objects database will be readable and changeable by all users. However, this means reducing the security of your data, something that must be weighed at the time of the decision by such alternative. To modify the behavior of the bank, use a ALTER DATABASE with a superuser:

ALTER DATABASE nome_do_banco SET lo_compat_privileges TO true;

---

The most suitable solution for use cases with a wide variety of users large Objects, in accordance with this response in the DBA SE, perhaps it is to define a ROLE in which they are all included and when creating a large Object, explicitly define the ROLE used by the transaction so that it is the effective owner of the objects it creates. In this way, all users belonging to this ROLE will be able to manipulate such large Objects. To define which ROLE will be used, just before creating the large Object use the command:

SET ROLE nome_do_role;