Thread: Strange "permission denied" errors on pg_restore

Strange "permission denied" errors on pg_restore

From

Ron Johnson

Date:

28 June, 16:34:08

PG 16.3, of a PG 16 pg_dump of a PG 15.7 database.

Here's a sample of the errors from the "pg_restore -v" log, while attached are samples from the postgresql.log file:

pg_restore: while PROCESSING TOC:
pg_restore: from TOC entry 847489; 0 1814433 TABLE DATA batch_rp4_y2022m08 fis_tap
pg_restore: error: COPY failed for table "batch_rp4_y2022m08": ERROR: permission denied for schema tapschema
LINE 1: SELECT 1 FROM ONLY "tapschema"."lockbox" x WHERE "lockbox_id...
^
QUERY: SELECT 1 FROM ONLY "tapschema"."lockbox" x WHERE "lockbox_id" OPERATOR(pg_catalog.=) $1 FOR KEY SHARE OF x
pg_restore: while PROCESSING TOC:
pg_restore: from TOC entry 847492; 0 1814598 TABLE DATA batch_rp4_y2022m05 fis_tap
pg_restore: error: COPY failed for table "batch_rp4_y2022m05": ERROR: permission denied for schema tapschema
LINE 1: SELECT 1 FROM ONLY "tapschema"."lockbox" x WHERE "lockbox_id...
^
QUERY: SELECT 1 FROM ONLY "tapschema"."lockbox" x WHERE "lockbox_id" OPERATOR(pg_catalog.=) $1 FOR KEY SHARE OF x
pg_restore: while PROCESSING TOC:
pg_restore: from TOC entry 847491; 0 1814543 TABLE DATA batch_rp4_y2022m06 fis_tap
pg_restore: error: COPY failed for table "batch_rp4_y2022m06": ERROR: permission denied for schema tapschema
LINE 1: SELECT 1 FROM ONLY "tapschema"."lockbox" x WHERE "lockbox_id...
^
QUERY: SELECT 1 FROM ONLY "tapschema"."lockbox" x WHERE "lockbox_id" OPERATOR(pg_catalog.=) $1 FOR KEY SHARE OF x
pg_restore: finished item 847489 TABLE DATA batch_rp4_y2022m08

The only errors were on COPY statements. The tables and indices were all created, and attached to their parent tables.

Individually restoring the tables from the backup succeeded without error.

There's a FK relationship from batch.lockbox_id to lockbox.lockbox_id, but why should that matter, since FK constraints are created after tables are loaded?

But that gets me thinking... table "batch" is doubly-partitioned: first on the "rp" number, and then the batch_rpX tables are partitioned by the process_date. Might the FK on the batch_rp4 table have already been created?

Attachment

restore_errors.txt

Re: Strange "permission denied" errors on pg_restore

From

Laurenz Albe

Date:

29 June, 07:31:01

On Fri, 2024-06-28 at 09:34 -0400, Ron Johnson wrote:
> pg_restore: error: COPY failed for table "batch_rp4_y2022m08": ERROR:  permission denied for schema tapschema

- Was the database you restored into totally empty?

- What was the exact "pg_restore" command line?

- What was the exact command line to take the dump?

Yours,
Laurenz Albe

Re: Strange "permission denied" errors on pg_restore

From

Ron Johnson

Date:

29 June, 08:04:22

On Sat, Jun 29, 2024 at 12:31 AM Laurenz Albe <laurenz.albe@cybertec.at> wrote:

On Fri, 2024-06-28 at 09:34 -0400, Ron Johnson wrote:
> pg_restore: error: COPY failed for table "batch_rp4_y2022m08": ERROR: permission denied for schema tapschema

- Was the database you restored into totally empty?

- What was the exact "pg_restore" command line?

pg_restore -v --clean --create -Fd -j6 --dbname=postgres tap &> tap_restore.log

- What was the exact command line to take the dump?

declare -i Threads=$((`nproc`*3/4))

declare -i Zlvl=6

DB=tap

pg_dump -j ${Threads} -Z${ZLvl} -v -C -Fd --file=$DB $DB 2> ${DB}_pgdump.log

Re: Strange "permission denied" errors on pg_restore

From

Laurenz Albe

Date:

29 June, 08:13:21

On Sat, 2024-06-29 at 01:04 -0400, Ron Johnson wrote:
> On Sat, Jun 29, 2024 at 12:31 AM Laurenz Albe <laurenz.albe@cybertec.at> wrote:
> > On Fri, 2024-06-28 at 09:34 -0400, Ron Johnson wrote:
> > > pg_restore: error: COPY failed for table "batch_rp4_y2022m08": ERROR:  permission denied for schema tapschema
> >
> > - Was the database you restored into totally empty?
> >
> > - What was the exact "pg_restore" command line?
>
> pg_restore -v --clean --create -Fd -j6 --dbname=postgres tap &> tap_restore.log

So the database was clean (newly created).

You should perform the restore as a superuser or as a user that has all
the required permissions.  Restoring with a non-superuser can be tricky.

Yours,
Laurenz Albe

Re: Strange "permission denied" errors on pg_restore

From

Ron Johnson

Date:

29 June, 08:18:55

On Sat, Jun 29, 2024 at 1:13 AM Laurenz Albe <laurenz.albe@cybertec.at> wrote:

On Sat, 2024-06-29 at 01:04 -0400, Ron Johnson wrote:
> On Sat, Jun 29, 2024 at 12:31 AM Laurenz Albe <laurenz.albe@cybertec.at> wrote:
> > On Fri, 2024-06-28 at 09:34 -0400, Ron Johnson wrote:
> > > pg_restore: error: COPY failed for table "batch_rp4_y2022m08": ERROR: permission denied for schema tapschema
> >
> > - Was the database you restored into totally empty?
> >
> > - What was the exact "pg_restore" command line?
>
> pg_restore -v --clean --create -Fd -j6 --dbname=postgres tap &> tap_restore.log

So the database was clean (newly created).

You should perform the restore as a superuser or as a user that has all
the required permissions. Restoring with a non-superuser can be tricky.

I do everything database-related as user "postgres". Only "sudo yum" is run from my personal account.

Re: Strange "permission denied" errors on pg_restore

From

Tom Lane

Date:

29 June, 17:54:05

Ron Johnson <ronljohnsonjr@gmail.com> writes:
> On Sat, Jun 29, 2024 at 1:13 AM Laurenz Albe <laurenz.albe@cybertec.at>
> wrote:
>> You should perform the restore as a superuser or as a user that has all
>> the required permissions.  Restoring with a non-superuser can be tricky.

> I do everything database-related as user "postgres".  Only "sudo yum" is
> run from my personal account.

The failing query seems to be a foreign-key enforcement check that
happened to be triggered from COPY.  Those are run as the owner of
the table that is being checked.  So it appears that in

pg_restore: error: COPY failed for table "batch_rp4_y2022m08": ERROR: permission denied for schema tapschema
LINE 1: SELECT 1 FROM ONLY "tapschema"."lockbox" x WHERE "lockbox_id...
                           ^
QUERY:  SELECT 1 FROM ONLY "tapschema"."lockbox" x WHERE "lockbox_id" OPERATOR(pg_catalog.=) $1 FOR KEY SHARE OF x

the owner of table "lockbox" lacks usage permission on the containing
schema "tapschema".  That's a most bizarre situation and would have
caused the same sort of FK failures in the originating database as
well.  pg_dump can't really promise to restore databases containing
arbitrarily-broken permissions settings.

            regards, tom lane

Re: Strange "permission denied" errors on pg_restore

From

Ron Johnson

Date:

30 June, 01:12:01

On Sat, Jun 29, 2024 at 10:54 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:

Ron Johnson <ronljohnsonjr@gmail.com> writes:
> On Sat, Jun 29, 2024 at 1:13 AM Laurenz Albe <laurenz.albe@cybertec.at>
> wrote:
>> You should perform the restore as a superuser or as a user that has all
>> the required permissions. Restoring with a non-superuser can be tricky.

> I do everything database-related as user "postgres". Only "sudo yum" is
> run from my personal account.

The failing query seems to be a foreign-key enforcement check that
happened to be triggered from COPY. Those are run as the owner of
the table that is being checked.

I thought FK constraints were applied after tables were copied.

So it appears that in

pg_restore: error: COPY failed for table "batch_rp4_y2022m08": ERROR: permission denied for schema tapschema
LINE 1: SELECT 1 FROM ONLY "tapschema"."lockbox" x WHERE "lockbox_id...
^
QUERY: SELECT 1 FROM ONLY "tapschema"."lockbox" x WHERE "lockbox_id" OPERATOR(pg_catalog.=) $1 FOR KEY SHARE OF x

the owner of table "lockbox" lacks usage permission on the containing
schema "tapschema".

Turns out that user "postgres" owns "tapschema".

That's a most bizarre situation and would have
caused the same sort of FK failures in the originating database as
well.

Interesting. No one complained (but this is a pre-prod server, and people are busy with other projects).

pg_dump can't really promise to restore databases containing
arbitrarily-broken permissions settings.

There are TWELVE THOUSAND foreign key constraints in 'tapschema', and pg_restore only complained about 27 of them.

tap=# select contype, count(*)
from pg_constraint
where connamespace::regnamespace::text = 'tapschema'
group by contype;
contype | count
---------+-------
f | 11964
p | 450
(2 rows)

Re: Strange "permission denied" errors on pg_restore

From

Scott Ribe

Date:

30 June, 01:30:15

Could it be that someone accidentally changed ownership after data had been created?

Re: Strange "permission denied" errors on pg_restore

From

Ron Johnson

Date:

30 June, 01:34:52

On Sat, Jun 29, 2024 at 6:30 PM Scott Ribe <scott_ribe@elevated-dev.com> wrote:

Could it be that someone accidentally changed ownership after data had been created?

All tables (except one named "foo") are owned by the service account fis_tap.

postgres=# select oid
postgres=# from pg_roles
postgres-# where rolname='fis_tap';
oid
-------
16386
(1 row)

tap=# select relowner, count(*)
from pg_class
where relkind in ('r', 't', 'p')
and relnamespace::regnamespace::text = 'tapschema'
group by relowner;
relowner | count
----------+-------
10 | 1
16386 | 30980
(2 rows)

Re: Strange "permission denied" errors on pg_restore

From

Wasim Devale

Date:

30 June, 08:13:39

Can we change relowner to 10 ? After data is loaded in the database?

On Sun, 30 Jun, 2024, 4:05 am Ron Johnson, <ronljohnsonjr@gmail.com> wrote:

On Sat, Jun 29, 2024 at 6:30 PM Scott Ribe <scott_ribe@elevated-dev.com> wrote:
Could it be that someone accidentally changed ownership after data had been created?

All tables (except one named "foo") are owned by the service account fis_tap.

postgres=# select oid
postgres=# from pg_roles
postgres-# where rolname='fis_tap';
oid
-------
16386
(1 row)

tap=# select relowner, count(*)
from pg_class
where relkind in ('r', 't', 'p')
and relnamespace::regnamespace::text = 'tapschema'
group by relowner;
relowner | count
----------+-------
10 | 1
16386 | 30980
(2 rows)

Re: Strange "permission denied" errors on pg_restore

From

Ron Johnson

Date:

30 June, 09:48:07

We can, but the software vendor wants everything owned by the service account.

On Sun, Jun 30, 2024 at 1:13 AM Wasim Devale <wasimd60@gmail.com> wrote:

Can we change relowner to 10 ? After data is loaded in the database?
On Sun, 30 Jun, 2024, 4:05 am Ron Johnson, <ronljohnsonjr@gmail.com> wrote:
On Sat, Jun 29, 2024 at 6:30 PM Scott Ribe <scott_ribe@elevated-dev.com> wrote:
Could it be that someone accidentally changed ownership after data had been created?

All tables (except one named "foo") are owned by the service account fis_tap.

postgres=# select oid
postgres=# from pg_roles
postgres-# where rolname='fis_tap';
oid
-------
16386
(1 row)

tap=# select relowner, count(*)
from pg_class
where relkind in ('r', 't', 'p')
and relnamespace::regnamespace::text = 'tapschema'
group by relowner;
relowner | count
----------+-------
10 | 1
16386 | 30980
(2 rows)