Re: permission denied for large object 200936761

On 2/1/21 9:13 AM, Andrus wrote:
> Hi!
>
> >Well the user that runs the pg_dump needs to have permissions on the
> large objects. For more information see below.
>
> How to add permissions to non-superusers for this.?
>
> GRANT command
>
> GRANT { { SELECT | UPDATE } [, ...] | ALL [ PRIVILEGES ] }
> ON LARGE OBJECT/|loid|/ [, ...]
> TO/|role_specification|/ [, ...] [ WITH GRANT OPTION ]
>
> Requires large object id. How to grant backup privilege if there are no
> large objects in database ?

Your query below says there are and also returns the oid for them.

>
> Should *lo_compat_privileges
> *
>
> set in postgresql.conf  or is there better way ?

Changing lo_compat_privileges is an option but it is just moving the
problem down the road. The actual fix is to find out what the large
objects are there for, who owns them, are they still necessary?. Then
based on the answers make the changes needed to future proof further
interaction with them.

>
>>> -n public -n firma74
>>>
>>> command line options are used.
>>>
>>>>
>>>> You can query that to see what is there. I would not go about
>>>> deleting until you find what the large objects are for.
>>>>
>>> select * from pg_largeobject
>>>
>>> returns empty table.
>>

> select * from  pg_largeobject_metadata
>
> returns 3 rows:
>
> Oid                      Lomowner
>
>   200936761       30152
>
>   200936762      30152
>
>   200936767       30152
>
> How to find table and schema which is referenced by this ?

Large objects exist independent of those. The important part of the
above is lomowner. Use that oid to find the role that owns the objects here:

https://www.postgresql.org/docs/12/view-pg-roles.html

select rolname from pg_roles where oid = <lomowner>;

>
> Andrus.
>
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com