I'm trying to back up a database in Postgresql, however I'm getting the following error:
erro ao ler objeto grande 118287: ERROR: permission denied for large object 118287
I looked up the error and found the following information
I have performed the indicated commands but the error persists, has anyone had it yet and found a solution?
Since version 9.0 , each large object in PostgreSQL has its OID, as well as tables, indexes, etc., and should not be treated as a simple table record pg_largeobject " and therefore not being automatically readable by the user who owns this table. You can list 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 the large object specified in the question, use:
GRANT SELECT ON LARGE OBJECT 118287 TO nome_do_usuario;
As your case implies reading the entire bank, I suggest backing it up with a user who is allowed to read all of the objects. A SUPERUSER being the simplest solution to keep, if you only have problems when executing backups.
Another alternative is to modify the behavior of the DATABASE in question by using the lo_compat_privileges option. So all% of the% of the bank will be readable and changeable by all users. However this means reducing the security of your data , something that should be weighed at the time of the decision by such alternative. To change the behavior of the database, use large objects with a superuser:
ALTER DATABASE nome_do_banco SET lo_compat_privileges TO true;
The best solution for use cases with a wide variety of large objects creators as response in DBA SE , you might want to define a ROLE in which all of them are included, and when creating a large object , explicitly define the ROLE used by the transaction to be the owner of the objects it creates. In this way, all users belonging to this ROLE can manipulate such large objects . To define which ROLE to use, just before creating the large object use the command:
SET ROLE nome_do_role;