permission denied for large object 200936761

On 2/1/21 6:43 AM, Andrus wrote:

Hi!

Database does not contain large objects.

pg_dump starts to throw error

ERROR:  permission denied for large object 200936761

Did you do the pg_dump as a superuser?

More below.

Tried

select * from "200936761"

but it returned "relation does not exist"

How to fix this ? How to find which table causes this error ?

Large objects are stored in a system catalog(table):

https://www.postgresql.org/docs/12/catalog-pg-largeobject.html

You can query that to see what is there. I would not go about deleting
until you find what the large objects are for.

How to find and delete all large objects in database ? Maybe it is
created accidently .

Using

 PostgreSQL 12.2 (Debian 12.2-2.pgdg100+1) on x86_64-pc-linux-gnu,
compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit

Andrus.

--
Adrian Klaver
adrian.klaver@aklaver.com

Hi!

Database does not contain large objects.

pg_dump starts to throw error

ERROR:  permission denied for large object 200936761

Did you do the pg_dump as a superuser?

No.

pg_dump needs to be invoked by non-superuser also. It backs up two
schemas, public and firma74 .

-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.

Database has approx 50 schemas and many thousands of tables.

Andrus.

On 2/1/21 8:32 AM, Andrus wrote:

Hi!

Database does not contain large objects.

pg_dump starts to throw error

ERROR:  permission denied for large object 200936761

Did you do the pg_dump as a superuser?

No.

pg_dump needs to be invoked by non-superuser also. It backs up two
schemas, public and firma74 .

Well the user that runs the pg_dump needs to have permissions on the
large objects. For more information see below.

-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.

I haven't used large objects in a while. Forgot that they now have
permissions associated with them. Try:

https://www.postgresql.org/docs/12/catalog-pg-largeobject-metadata.html

instead.

Database has approx 50 schemas and many thousands of tables.

Andrus.

--
Adrian Klaver
adrian.klaver@aklaver.com

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 ?

Should *lo_compat_privileges
*

set in postgresql.conf  or is there better way ?

-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.

I haven't used large objects in a while. Forgot that they now have
permissions associated with them. Try:

https://www.postgresql.org/docs/12/catalog-pg-largeobject-metadata.html

instead.

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 ?

Andrus.

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.klaver@aklaver.com

Hi!

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:

/select rolname from pg_roles where oid = 30152 ; //
/

returns my role ,  clusteradmin . I have superuser rights:

CREATE ROLE clusteradmin WITH
  LOGIN
  SUPERUSER
  INHERIT
  NOCREATEDB
  NOCREATEROLE
  NOREPLICATION:

GRANT db1_owner, db2_owner, ... to  clusteradmin;

It havent created any large objects.

How to use this information to fix the issue ?

Andrus.

On 2/1/21 9:55 AM, Andrus wrote:

Hi!

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:

/select rolname from pg_roles where oid = 30152 ; //
/

returns my role ,  clusteradmin . I have superuser rights:

CREATE ROLE clusteradmin WITH
  LOGIN
  SUPERUSER
  INHERIT
  NOCREATEDB
  NOCREATEROLE
  NOREPLICATION:

GRANT db1_owner, db2_owner, ... to  clusteradmin;

Not sure what the above is supposed to be doing?

It havent created any large objects.

What hasn't created large objects?

How to use this information to fix the issue ?

Do the pg_dump as user clusteradmin.

Andrus.

--
Adrian Klaver
adrian.klaver@aklaver.com

Hi!

 >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:

/select rolname from pg_roles where oid = 30152 ; //

Not sure what the above is supposed to be doing?

I showed the user definition

It havent created any large objects.

What hasn't created large objects?

I

How to use this information to fix the issue ?

Do the pg_dump as user clusteradmin.

This works. However I need to allow non-supoeruser to create backup
also. How to do this ?

Andrus.

On Mon, 2021-02-01 at 18:32 +0200, Andrus wrote:

Database does not contain large objects.

pg_dump starts to throw error

ERROR: permission denied for large object 200936761

Did you do the pg_dump as a superuser?

No.

pg_dump needs to be invoked by non-superuser also. It backs up two schemas, public and firma74 .

Obviously large objects *are* used.

You have to grant the database use permissions with

GRANT SELECT ON LARGE OBJECT 200936761 TO dumpuser;

Alternatively, use the -B option of pg_dump to skip dumping
large objects.

Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com

On 2/1/21 12:07 PM, Andrus wrote:

Hi!

 >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:

/select rolname from pg_roles where oid = 30152 ; //

Not sure what the above is supposed to be doing?

I showed the user definition

It havent created any large objects.

What hasn't created large objects?

I

How to use this information to fix the issue ?

Do the pg_dump as user clusteradmin.

This works. However I need to allow non-supoeruser to create backup
also. How to do this ?

Short term grant the dump user permissions on the large objects.

Long term figure out what they are and if they are needed or not.

Andrus.

--
Adrian Klaver
adrian.klaver@aklaver.com

Hi!

Long term figure out what they are and if they are needed or not.

Non-superuser backup worked earlier. It looks like large objects
suddenly appeared in database:

select * from  pg_largeobject_metadata

Oid                      Lomowner

200936761       30152
200936762      30152
200936767       30152

How to figure out what are large object with oids   200936761, 200936762
and 200936767 ?

Pd_dump throws error on first of them: 200936761

Andrus.

On 2/1/21 1:28 PM, Andrus wrote:

Hi!

Long term figure out what they are and if they are needed or not.

Non-superuser backup worked earlier. It looks like large objects
suddenly appeared in database:

select * from  pg_largeobject_metadata

Oid                      Lomowner

200936761       30152
200936762      30152
200936767       30152

How to figure out what are large object with oids   200936761, 200936762
and 200936767 ?

I misspoke earlier about large objects not being tied to a schema.table.
They can be as a column of type oid.

To see if they are try :

SELECT
relname,
attname
FROM
pg_attribute AS pa
JOIN pg_class AS pc ON pa.attrelid = pc.oid
WHERE
atttypid = 'oid'::regtype
AND relnamespace = 'public'::regnamespace
AND attnum > 0;

Where relnamespace is the schema you are interested in.

Pd_dump throws error on first of them: 200936761

Andrus.

--
Adrian Klaver
adrian.klaver@aklaver.com

I misspoke earlier about large objects not being tied to a

schema.table. They can be as a column of type oid.

To see if they are try :

SELECT
    relname,
    attname
FROM
    pg_attribute AS pa
    JOIN pg_class AS pc ON pa.attrelid = pc.oid
WHERE
    atttypid = 'oid'::regtype
    AND relnamespace = 'public'::regnamespace
    AND attnum > 0;

Where relnamespace is the schema you are interested in.

pg_dump which throws error is called with -n public -n firma74 parameters

I tried

SELECT
    relname,
    attname
FROM
    pg_attribute AS pa
    JOIN pg_class AS pc ON pa.attrelid = pc.oid
WHERE
    atttypid = 'oid'::regtype
    AND relnamespace in ( 'public'::regnamespace, 'firma74'::regnamespace )
    AND attnum > 0;

It returs 0 rows.

Andrus.

Hi!

Obviously large objects *are* used.

You have to grant the database use permissions with

GRANT SELECT ON LARGE OBJECT 200936761 TO dumpuser;

Alternatively, use the -B option of pg_dump to skip dumping
large objects.

I added -B option and changed postgresql.conf  to
lo_compat_privileges=on   as temporary fix.

dumpuser has created backups of same two schemas for years without
issues. The issue starts to occur today.

Application does not create large objects. It uses bytea columns instead.

How to figure out what is this large object ?

Andrus.

On 2/1/21 2:20 PM, Andrus wrote:

Hi!

Obviously large objects *are* used.

You have to grant the database use permissions with

GRANT SELECT ON LARGE OBJECT 200936761 TO dumpuser;

Alternatively, use the -B option of pg_dump to skip dumping
large objects.

I added -B option and changed postgresql.conf  to
lo_compat_privileges=on   as temporary fix.

dumpuser has created backups of same two schemas for years without
issues. The issue starts to occur today.

What code changed between the last backup and today?

Application does not create large objects. It uses bytea columns instead.

How to figure out what is this large object ?

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.

Andrus.

--
Adrian Klaver
adrian.klaver@aklaver.com

Hi!

What code changed between the last backup and today?

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.

Cluster contains 25 databases. There are 50 users executing SELECT,
INSERT, UPDATE, DELETE, CREATE, DROP  and similar commands.

dumpuser has rights only to one database and two schemas (public and
firma74 ).

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

select * from lo_get(200936761);

select * from lo_get(200936762);

select * from lo_get(200936767);

Those queries returned one row containing one zero-length column lo_get.

Andrus.

On 2/1/21 3:07 PM, Andrus wrote:

Hi!

What code changed between the last backup and today?

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?

Cluster contains 25 databases. There are 50 users executing SELECT,
INSERT, UPDATE, DELETE, CREATE, DROP  and similar commands.

Yeah, but you are only concerned with one database and two schemas.

dumpuser has rights only to one database and two schemas (public and
firma74 ).

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.

select * from lo_get(200936761);

select * from lo_get(200936762);

select * from lo_get(200936767);

Those queries returned one row containing one zero-length column lo_get.

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?

Andrus.

--
Adrian Klaver
adrian.klaver@aklaver.com

On Tue, 2021-02-02 at 00:20 +0200, Andrus wrote:

Obviously large objects *are* used.

How to figure out what is this large object ?

You can extract it with

\lo_export 200936761 'somefile'

in psql and examine the file.

Ask the people who use that database!

Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com

Hi!

You can extract it with

\lo_export 200936761 'somefile'

in psql and examine the file.

Ask the people who use that database!

Tried

root@c202-76:~# ./pgsqlkaiv.sh

psql (12.2 (Debian 12.2-2.pgdg100+1))
Type "help" for help.

sba=#  \lo_export 200936761 'large200936761'
lo_export
sba=# \q

root@c202-76:~# ls -l large*
-rw-r--r-- 1 root root 0 veebr  2 10:45 large200936761

result file size is 0 .

Andrus.