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