Thread: pg_restore 12 "permission denied for schema" errors

RDS Postgresql 12.11

Backed up a database from one RDS instance, and now am trying to restore it 
to a new instance.  (If you're wondering why, it's to restore in an instance 
with less disk space.)

Here are the commands:

export PGHOST=${RDSENV}.xxxxxxxxxxxx.us-east-1.rds.amazonaws.com
cd /migrate/TASK001793786/2022-10-19b
DB=sides
pg_restore -v --create --clean --jobs=`nproc` -Fd --dbname=template1 $DB

Attached are log file extracts showing the errors.  The errors are only on 
partitioned child tables, the list of which is also attached.  However, some 
(but not many)

Some of the erroring tables are owned by sides_owner and some are owned by 
"stransuser".

"\z" on a child with errors looks exactly like a child without errors.

What could be the problem?

-- 
Angular momentum makes the world go 'round.

Hi Ron,

RDS Postgresql 12.11

Backed up a database from one RDS instance, and now am trying to restore it to a new instance.  (If you're wondering why, it's to restore in an instance with less disk space.)

Here are the commands:

export PGHOST=${RDSENV}.xxxxxxxxxxxx.us-east-1.rds.amazonaws.com
cd /migrate/TASK001793786/2022-10-19b
DB=sides
pg_restore -v --create --clean --jobs=`nproc` -Fd --dbname=template1 $DB

What database do you want to restore your data into? As far as I know your pg_restore command would import the data into template1, right? pg_restore's manpage says:

> […]
>      -d dbname
>      --dbname=dbname
>          Connect to database dbname and restore directly into the database. The dbname can be a connection string. If so, connection string parameters will override any conflicting command line options.
> […]                                 ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

And I'm wondering, if it's what you want. Possibly your errors could come from there…

Cheers,
Frank.

Frank Gard <frank.von.postgresql.org@familie-gard.de> writes:
> Am 20.10.22 um 02:58 schrieb Ron:
>> pg_restore -v --create --clean --jobs=`nproc` -Fd --dbname=template1 $DB

> What database do you want to restore your data into? As far as I know your pg_restore command would import the data
intotemplate1, right? 

No --- my ears went up about that too, but he's using --create.
So template1 is just the springboard for the initial connection
in which CREATE DATABASE will be issued.

My guess is that there's something inconsistent between source
and target installations about roles and role-privilege grants.
We don't have enough details to do more than guess, though.

            regards, tom lane

Hi Tom, Am 20.10.22 um 07:12 schrieb Tom Lane:

Frank Gard <frank.von.postgresql.org@familie-gard.de> writes:

Am 20.10.22 um 02:58 schrieb Ron:

pg_restore -v --create --clean --jobs=`nproc` -Fd --dbname=template1 $DB

What database do you want to restore your data into? As far as I know your pg_restore command would import the data into template1, right?

No --- my ears went up about that too, but he's using --create.
So template1 is just the springboard for the initial connection
in which CREATE DATABASE will be issued.

Frank.

On 10/20/22 00:12, Tom Lane wrote:
> Frank Gard <frank.von.postgresql.org@familie-gard.de> writes:
>> Am 20.10.22 um 02:58 schrieb Ron:
>>> pg_restore -v --create --clean --jobs=`nproc` -Fd --dbname=template1 $DB
>> What database do you want to restore your data into? As far as I know your pg_restore command would import the data
intotemplate1, right?
 
> No --- my ears went up about that too, but he's using --create.
> So template1 is just the springboard for the initial connection
> in which CREATE DATABASE will be issued.
>
> My guess is that there's something inconsistent between source
> and target installations about roles and role-privilege grants.
> We don't have enough details to do more than guess, though.

I ran "pg_dumpall --globals-only --no-role-passwords" on the source 
instance, and applied it to the new instance before doing the pg_restore.  
If I hadn't done that, pg_restore would have thrown errors on all the GRANT 
and ALTER TABLE ... OWNER TO statements embedded in the backup.

-- 
Angular momentum makes the world go 'round.

On 10/20/22 06:20, Ron wrote:
> On 10/20/22 00:12, Tom Lane wrote:
>> Frank Gard <frank.von.postgresql.org@familie-gard.de> writes:
>>> Am 20.10.22 um 02:58 schrieb Ron:
>>>> pg_restore -v --create --clean --jobs=`nproc` -Fd --dbname=template1 
>>>> $DB
>>> What database do you want to restore your data into? As far as I know 
>>> your pg_restore command would import the data into template1, right?
>> No --- my ears went up about that too, but he's using --create.
>> So template1 is just the springboard for the initial connection
>> in which CREATE DATABASE will be issued.
>>
>> My guess is that there's something inconsistent between source
>> and target installations about roles and role-privilege grants.
>> We don't have enough details to do more than guess, though.
> 
> I ran "pg_dumpall --globals-only --no-role-passwords" on the source 
> instance, and applied it to the new instance before doing the 
> pg_restore. If I hadn't done that, pg_restore would have thrown errors 
> on all the GRANT and ALTER TABLE ... OWNER TO statements embedded in the 
> backup.
> 

Some questions:

1) The backup was from a Postgres 12.x database using a version 12 or 
higher instance of pg_backup?

2) What does nproc --jobs=`nproc` resolve to?

3) What if you run without --jobs?

4) What user are you running the pg_restore as?

5) Why the --no-role-passwords in the pg_dump?

-- 
Adrian Klaver
adrian.klaver@aklaver.com

On 10/20/22 10:02, Adrian Klaver wrote:
> On 10/20/22 06:20, Ron wrote:
>> On 10/20/22 00:12, Tom Lane wrote:
>>> Frank Gard <frank.von.postgresql.org@familie-gard.de> writes:
>>>> Am 20.10.22 um 02:58 schrieb Ron:
>>>>> pg_restore -v --create --clean --jobs=`nproc` -Fd --dbname=template1 $DB
>>>> What database do you want to restore your data into? As far as I know 
>>>> your pg_restore command would import the data into template1, right?
>>> No --- my ears went up about that too, but he's using --create.
>>> So template1 is just the springboard for the initial connection
>>> in which CREATE DATABASE will be issued.
>>>
>>> My guess is that there's something inconsistent between source
>>> and target installations about roles and role-privilege grants.
>>> We don't have enough details to do more than guess, though.
>>
>> I ran "pg_dumpall --globals-only --no-role-passwords" on the source 
>> instance, and applied it to the new instance before doing the pg_restore. 
>> If I hadn't done that, pg_restore would have thrown errors on all the 
>> GRANT and ALTER TABLE ... OWNER TO statements embedded in the backup.
>>
>
> Some questions:
>
> 1) The backup was from a Postgres 12.x database using a version 12 or 
> higher instance of pg_backup?

The data is in an RDS Postgresql 12.11 instance.

pg_dump on the "client" Linux VM is v12.12 or 12.11 from RHEL8 packages. 
(Don't remember the exact version ATM.)

> 2) What does nproc --jobs=`nproc` resolve to?

Two CPUs on the VM.

> 3) What if you run without --jobs?

I can test that, but will be under serious time constraints during the 
maintenance window.  Heavily parallel dump and restore are imperative.

> 4) What user are you running the pg_restore as?

PGUSER=postgres

> 5) Why the --no-role-passwords in the pg_dump?

That's a requirement for AWS RDS.  User postgres does not have privs to dump 
the password hash from pg_authid.

-- 
Angular momentum makes the world go 'round.

On 10/20/22 10:02, Adrian Klaver wrote:
> On 10/20/22 06:20, Ron wrote:
>> On 10/20/22 00:12, Tom Lane wrote:
>>> Frank Gard <frank.von.postgresql.org@familie-gard.de> writes:
>>>> Am 20.10.22 um 02:58 schrieb Ron:
>>>>> pg_restore -v --create --clean --jobs=`nproc` -Fd --dbname=template1 $DB
>>>> What database do you want to restore your data into? As far as I know 
>>>> your pg_restore command would import the data into template1, right?
>>> No --- my ears went up about that too, but he's using --create.
>>> So template1 is just the springboard for the initial connection
>>> in which CREATE DATABASE will be issued.
>>>
>>> My guess is that there's something inconsistent between source
>>> and target installations about roles and role-privilege grants.
>>> We don't have enough details to do more than guess, though.
>>
>> I ran "pg_dumpall --globals-only --no-role-passwords" on the source 
>> instance, and applied it to the new instance before doing the pg_restore. 
>> If I hadn't done that, pg_restore would have thrown errors on all the 
>> GRANT and ALTER TABLE ... OWNER TO statements embedded in the backup.
>>
>
> Some questions:
>
> 1) The backup was from a Postgres 12.x database using a version 12 or 
> higher instance of pg_backup?

pg_dump and pg_restore are 12.11 from RHEL8.

> 3) What if you run without --jobs?

It runs without error.  Add "--jobs=2" and the errors appear.

-- 
Angular momentum makes the world go 'round.

On 10/20/22 14:34, Ron wrote:
> On 10/20/22 10:02, Adrian Klaver wrote:
>> On 10/20/22 06:20, Ron wrote:
>>> On 10/20/22 00:12, Tom Lane wrote:

>>> I ran "pg_dumpall --globals-only --no-role-passwords" on the source 
>>> instance, and applied it to the new instance before doing the 
>>> pg_restore. If I hadn't done that, pg_restore would have thrown 
>>> errors on all the GRANT and ALTER TABLE ... OWNER TO statements 
>>> embedded in the backup.
>>>
>>
>> Some questions:
>>
>> 1) The backup was from a Postgres 12.x database using a version 12 or 
>> higher instance of pg_backup?
> 
> pg_dump and pg_restore are 12.11 from RHEL8.
> 
>> 3) What if you run without --jobs?
> 
> It runs without error.  Add "--jobs=2" and the errors appear.
> 

Hmm, that is beyond me.

1) I did notice that the pg_restore errors all where; ERROR:  permission 
denied for schema strans

2) They all occurred during CREATE INDEX or COPY, which would be the 
part where --jobs kicks in.

To me it looks like  out of order execution where the jobs starting on 
their tasks before the main task got done granting permissions. I just 
have no idea how that could happen.

-- 
Adrian Klaver
adrian.klaver@aklaver.com

On 10/22/22 11:20, Adrian Klaver wrote:
> On 10/20/22 14:34, Ron wrote:
>> On 10/20/22 10:02, Adrian Klaver wrote:
>>> On 10/20/22 06:20, Ron wrote:
>>>> On 10/20/22 00:12, Tom Lane wrote:
>
>>>> I ran "pg_dumpall --globals-only --no-role-passwords" on the source 
>>>> instance, and applied it to the new instance before doing the 
>>>> pg_restore. If I hadn't done that, pg_restore would have thrown errors 
>>>> on all the GRANT and ALTER TABLE ... OWNER TO statements embedded in 
>>>> the backup.
>>>>
>>>
>>> Some questions:
>>>
>>> 1) The backup was from a Postgres 12.x database using a version 12 or 
>>> higher instance of pg_backup?
>>
>> pg_dump and pg_restore are 12.11 from RHEL8.
>>
>>> 3) What if you run without --jobs?
>>
>> It runs without error.  Add "--jobs=2" and the errors appear.
>>
>
> Hmm, that is beyond me.
>
> 1) I did notice that the pg_restore errors all where; ERROR: permission 
> denied for schema strans
>
> 2) They all occurred during CREATE INDEX or COPY, which would be the part 
> where --jobs kicks in.
>
> To me it looks like  out of order execution where the jobs starting on 
> their tasks before the main task got done granting permissions. I just 
> have no idea how that could happen.

I was afraid you were going to say that.

The work-around is to:
pg_dump $SRCDB --schema-only | grep -e '^\(GRANT|REVOKE\)' > all_GRANT.sql
pg_dump $SRCDB --schema-only | grep OWNER > all_OWNER.sql
pg_restore --jobs=X --no-owner $NEWDB
psql $NEWDB -f all_OWNER.sql
psql $NEWDB -f all_GRANT.sql

This is, of course, why we need to test the backup/restore process.

-- 
Angular momentum makes the world go 'round.

On 10/22/22 09:41, Ron wrote:
> On 10/22/22 11:20, Adrian Klaver wrote:
>> On 10/20/22 14:34, Ron wrote:
>>> On 10/20/22 10:02, Adrian Klaver wrote:
>>>> On 10/20/22 06:20, Ron wrote:
>>>>> On 10/20/22 00:12, Tom Lane wrote:
>>

> I was afraid you were going to say that.
> 
> The work-around is to:
> pg_dump $SRCDB --schema-only | grep -e '^\(GRANT|REVOKE\)' > all_GRANT.sql
> pg_dump $SRCDB --schema-only | grep OWNER > all_OWNER.sql
> pg_restore --jobs=X --no-owner $NEWDB

The above and below have me confused.

What is $NEWDB?

In above it seems to be a file and below a database name.


> psql $NEWDB -f all_OWNER.sql
> psql $NEWDB -f all_GRANT.sql
> 
> This is, of course, why we need to test the backup/restore process.
> 

-- 
Adrian Klaver
adrian.klaver@aklaver.com

On 10/22/22 12:00, Adrian Klaver wrote:
> On 10/22/22 09:41, Ron wrote:
>> On 10/22/22 11:20, Adrian Klaver wrote:
>>> On 10/20/22 14:34, Ron wrote:
>>>> On 10/20/22 10:02, Adrian Klaver wrote:
>>>>> On 10/20/22 06:20, Ron wrote:
>>>>>> On 10/20/22 00:12, Tom Lane wrote:
>>>
>
>> I was afraid you were going to say that.
>>
>> The work-around is to:
>> pg_dump $SRCDB --schema-only | grep -e '^\(GRANT|REVOKE\)' > all_GRANT.sql
>> pg_dump $SRCDB --schema-only | grep OWNER > all_OWNER.sql
>> pg_restore --jobs=X --no-owner $NEWDB
>
> The above and below have me confused.
>
> What is $NEWDB?
>
> In above it seems to be a file and below a database name.

Consider it pseudo-code.

>
>> psql $NEWDB -f all_OWNER.sql
>> psql $NEWDB -f all_GRANT.sql
>>
>> This is, of course, why we need to test the backup/restore process.
>>
>

-- 
Angular momentum makes the world go 'round.

On 10/22/22 14:02, Ron wrote:
> On 10/22/22 12:00, Adrian Klaver wrote:
>> On 10/22/22 09:41, Ron wrote:
>>> On 10/22/22 11:20, Adrian Klaver wrote:
>>>> On 10/20/22 14:34, Ron wrote:
>>>>> On 10/20/22 10:02, Adrian Klaver wrote:
>>>>>> On 10/20/22 06:20, Ron wrote:
>>>>>>> On 10/20/22 00:12, Tom Lane wrote:
>>>>
>>
>>> I was afraid you were going to say that.
>>>
>>> The work-around is to:
>>> pg_dump $SRCDB --schema-only | grep -e '^\(GRANT|REVOKE\)' > 
>>> all_GRANT.sql
>>> pg_dump $SRCDB --schema-only | grep OWNER > all_OWNER.sql
>>> pg_restore --jobs=X --no-owner $NEWDB
>>
>> The above and below have me confused.
>>
>> What is $NEWDB?
>>
>> In above it seems to be a file and below a database name.
> 
> Consider it pseudo-code.

To pseudo for me.

What file exactly is:

pg_restore --jobs=X --no-owner $NEWDB

restoring?

And how was that file created?

Knowing this might help get at why the more straight forward method does 
not work.

> 
>>
>>> psql $NEWDB -f all_OWNER.sql
>>> psql $NEWDB -f all_GRANT.sql
>>>
>>> This is, of course, why we need to test the backup/restore process.
>>>
>>
> 

-- 
Adrian Klaver
adrian.klaver@aklaver.com

On 10/22/22 16:29, Adrian Klaver wrote:
> On 10/22/22 14:02, Ron wrote:
>> On 10/22/22 12:00, Adrian Klaver wrote:
>>> On 10/22/22 09:41, Ron wrote:
>>>> On 10/22/22 11:20, Adrian Klaver wrote:
>>>>> On 10/20/22 14:34, Ron wrote:
>>>>>> On 10/20/22 10:02, Adrian Klaver wrote:
>>>>>>> On 10/20/22 06:20, Ron wrote:
>>>>>>>> On 10/20/22 00:12, Tom Lane wrote:
>>>>>
>>>
>>>> I was afraid you were going to say that.
>>>>
>>>> The work-around is to:
>>>> pg_dump $SRCDB --schema-only | grep -e '^\(GRANT|REVOKE\)' > all_GRANT.sql
>>>> pg_dump $SRCDB --schema-only | grep OWNER > all_OWNER.sql
>>>> pg_restore --jobs=X --no-owner $NEWDB
>>>
>>> The above and below have me confused.
>>>
>>> What is $NEWDB?
>>>
>>> In above it seems to be a file and below a database name.
>>
>> Consider it pseudo-code.
>
> To pseudo for me.
>
> What file exactly is:
>
> pg_restore --jobs=X --no-owner $NEWDB
>
> restoring?
>
> And how was that file created?
>
> Knowing this might help get at why the more straight forward method does 
> not work.

This is what I ran to restore the database:
export PGHOST=${RDSENV}.xxxxxxxxxxxx.us-east-1.rds.amazonaws.com
cd /migrate/TASK001793786/2022-10-19b
NEWDB=sides
pg_restore -v --create --clean --no-owner --jobs=`nproc` -Fd 
--dbname=template1 $NEWDB
psql $NEWDB -f all_OWNER.sql
psql $NEWDB -f all_GRANT.sql

The name of the database is "sides", and there's a directorynamed "sides" 
under /migrate/TASK001793786/2022-10-19b.

>>
>>>
>>>> psql $NEWDB -f all_OWNER.sql
>>>> psql $NEWDB -f all_GRANT.sql
>>>>
>>>> This is, of course, why we need to test the backup/restore process.
>>>>
>>>
>>
>

-- 
Angular momentum makes the world go 'round.

On 10/22/22 14:45, Ron wrote:
> On 10/22/22 16:29, Adrian Klaver wrote:

>> To pseudo for me.
>>
>> What file exactly is:
>>
>> pg_restore --jobs=X --no-owner $NEWDB
>>
>> restoring?
>>
>> And how was that file created?
>>
>> Knowing this might help get at why the more straight forward method 
>> does not work.
> 
> This is what I ran to restore the database:
> export PGHOST=${RDSENV}.xxxxxxxxxxxx.us-east-1.rds.amazonaws.com
> cd /migrate/TASK001793786/2022-10-19b
> NEWDB=sides
> pg_restore -v --create --clean --no-owner --jobs=`nproc` -Fd 
> --dbname=template1 $NEWDB
> psql $NEWDB -f all_OWNER.sql
> psql $NEWDB -f all_GRANT.sql
> 
> The name of the database is "sides", and there's a directorynamed 
> "sides" under /migrate/TASK001793786/2022-10-19b.

Aah, I forgot about the -Fd, now it makes more sense.

To get past the --jobs induced error required the addition of --no-owner 
and then adding owners and grants after the main restore.

What was the pg_dump command that produced 
/migrate/TASK001793786/2022-10-19b/sides ?

> 
>>>
>>>>
>>>>> psql $NEWDB -f all_OWNER.sql
>>>>> psql $NEWDB -f all_GRANT.sql
>>>>>
>>>>> This is, of course, why we need to test the backup/restore process.
>>>>>
>>>>
>>>
>>
> 

-- 
Adrian Klaver
adrian.klaver@aklaver.com

On 10/22/22 17:06, Adrian Klaver wrote:
> On 10/22/22 14:45, Ron wrote:
>> On 10/22/22 16:29, Adrian Klaver wrote:
>
>>> To pseudo for me.
>>>
>>> What file exactly is:
>>>
>>> pg_restore --jobs=X --no-owner $NEWDB
>>>
>>> restoring?
>>>
>>> And how was that file created?
>>>
>>> Knowing this might help get at why the more straight forward method does 
>>> not work.
>>
>> This is what I ran to restore the database:
>> export PGHOST=${RDSENV}.xxxxxxxxxxxx.us-east-1.rds.amazonaws.com
>> cd /migrate/TASK001793786/2022-10-19b
>> NEWDB=sides
>> pg_restore -v --create --clean --no-owner --jobs=`nproc` -Fd 
>> --dbname=template1 $NEWDB
>> psql $NEWDB -f all_OWNER.sql
>> psql $NEWDB -f all_GRANT.sql
>>
>> The name of the database is "sides", and there's a directorynamed "sides" 
>> under /migrate/TASK001793786/2022-10-19b.
>
> Aah, I forgot about the -Fd, now it makes more sense.
>
> To get past the --jobs induced error required the addition of --no-owner 
> and then adding owners and grants after the main restore.
>
> What was the pg_dump command that produced 
> /migrate/TASK001793786/2022-10-19b/sides ?

PGHOST=mumble.xxxxxxxxxxxx.us-east-1.rds.amazonaws.com
PGUSER=postgres
cd /migrate/TASK001793786/`date +%F`
DB=sides
pg_dump -d $DB -j4 -Z0 -v -Fd --file=$DB 2> ${DB}_pgdump.log

-- 
Angular momentum makes the world go 'round.

Ron <ronljohnsonjr@gmail.com> writes:
> On 10/20/22 10:02, Adrian Klaver wrote:
>> 3) What if you run without --jobs?

> It runs without error.  Add "--jobs=2" and the errors appear.

That's ... suggestive ... but not suggestive enough.  Can you
create a self-contained test case?  It probably doesn't depend
much at all on your data, just the schema.

            regards, tom lane