pg_restore 12 "permission denied for schema" errors

Hi Ron,

Am 20.10.22 um 02:58 schrieb 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:

[…]
-ddbname
--dbname=dbname
          Connect to database dbnameand restore directly into the database. The dbnamecan 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 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.

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.

you're right, of course. Overseen that detail. I've to learn to read more carefully before answering. Sorry.

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

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