From: | Ron Johnson <ronljohnsonjr(at)gmail(dot)com> |
---|---|
To: | Pgsql-admin <pgsql-admin(at)lists(dot)postgresql(dot)org> |
Subject: | Re: Strange "permission denied" errors on pg_restore |
Date: | 2024-06-29 22:12:01 |
Message-ID: | CANzqJaC6U4wc9YimW_B-OGDRQeqgfm8Dem5WYy=a1fAd0mFqSw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin |
On Sat, Jun 29, 2024 at 10:54 AM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Ron Johnson <ronljohnsonjr(at)gmail(dot)com> writes:
> > On Sat, Jun 29, 2024 at 1:13 AM Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)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)
From | Date | Subject | |
---|---|---|---|
Next Message | Scott Ribe | 2024-06-29 22:30:15 | Re: Strange "permission denied" errors on pg_restore |
Previous Message | Achilleas Mantzios | 2024-06-29 21:10:36 | Re: Sudden spike in WAL |