agora inbox for pgsql-admin@postgresql.org
help / color / mirror / Atom feedFrom: Tom Lane <tgl@sss.pgh.pa.us>
To: Ron Johnson <ronljohnsonjr@gmail.com>
Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Subject: Re: Strange "permission denied" errors on pg_restore
Date: Sat, 29 Jun 2024 10:54:05 -0400
Message-ID: <2207734.1719672845@sss.pgh.pa.us> (raw)
In-Reply-To: <CANzqJaBDNN9r=uK3jKtmGCCo2k55fJXK9n6CAhL5WN8pa_0pLQ@mail.gmail.com>
References: <CANzqJaBrzXfebHJaDLRWenr4WRgvRssTgEQR_O20N0sSv9MHLw@mail.gmail.com>
<792db007dcc03570743afee23aa0da830207f0cf.camel@cybertec.at>
<CANzqJaAVrQyJ_4ASm0PkmUQ7+U8PHKMTU4NAGFzM0w4FN5tDrQ@mail.gmail.com>
<42d837098e471906e23ed5ef345a1172145c76da.camel@cybertec.at>
<CANzqJaBDNN9r=uK3jKtmGCCo2k55fJXK9n6CAhL5WN8pa_0pLQ@mail.gmail.com>
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
view thread (11+ messages) latest in thread
Message-ID: <2207734.1719672845@sss.pgh.pa.us>
Permalink: ../2207734.1719672845@sss.pgh.pa.us/
Also on: postgresql.org/message-id/2207734.1719672845@sss.pgh.pa.us
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: tgl@sss.pgh.pa.us, ronljohnsonjr@gmail.com, pgsql-admin@lists.postgresql.org
Subject: Re: Strange "permission denied" errors on pg_restore
In-Reply-To: <2207734.1719672845@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox