Re: Strange "permission denied" errors on pg_restore

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