Re: permission denied for large object 200936761 - Mailing list pgsql-general

From Andrus
Subject Re: permission denied for large object 200936761
Date
Msg-id 53911d95-5aa9-f574-aa87-8d87badcf1ad@hot.ee
Whole thread Raw
In response to Re: permission denied for large object 200936761  (Adrian Klaver <adrian.klaver@aklaver.com>)
List pgsql-general
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.

pgsql-general by date:

Previous
From: Andrus
Date:
Subject: Re: permission denied for large object 200936761
Next
From: Andrus
Date:
Subject: Re: permission denied for large object 200936761