Re: permission denied for large object 200936761

Hi!

>I have imported data from other clusters and executed lot of different
sql commands.  I have used grant, revoke, reassign commands to change
privileges for other users and have deleted and added users.

>
> I don't suppose this was done in a structured way that could be gone
> back over?

Exact command sequence cannot restored.

I have script to normalize rights for user. It removes all rights first
and set desired rights afterwards.

This was executed lot of times, it is used for years. Also there were
manual user rights adjustments using sql commands in cases there this
universal script cannot used. There are approx. 300 postgres roles in
cluster, users are changing in every week.

Previous dumpuser backup which suceeds was at January 4th,  9 MB in
custom format. There are nightly backups of databases is cluster.

There is also hot standby, base backups in every sunday using
pg_basebackup and WAL archiving.  WAL archiving and hot standby was
broken in previous week (I increased max_connections=400 in main server
but forget to increase this in standby server, WAL archiving is also
from hot standby server).

>
>> You could try some of the functions here:
>>
>> https://www.postgresql.org/docs/12/lo-funcs.html
>>
>> to see if you can figure it out.
>>
>> There is only one function , lo_get() in this page which returns
>> data. I tried
>
> Actually there is a second lo_export() at bottom of page. It needs
> superuser privilege and access to the server file system.

Tried in server using psql

select lo_export(200936761,'large1');
select lo_export(200936762,'large2');
select lo_export(200936767,'large3');

result files have zero size.

>What happens if you query:
>
> https://www.postgresql.org/docs/12/catalog-pg-largeobject.html
>
> as a superuser?
>
> Do you see anything in the data field?

select * from pg_largeobject

running as superuser returs empty table with 3 columns:

loid, pageno and data

Andrus.