Subject: pg_restore ERROR: permission denied to change default privileges

From: Rachel Roch <[email-masked]>
Date: Fri, 13 Jun 2025 20:08:11 +0200 (CEST)

I have a pg_dump from a postgres instance that I am attempting to restore onto a cloud one (i.e. an instance where I don't have access to the postgres superuser)

The dump was taken with:

pg_dump -Fc --quote-all-identifiers --serializable-deferrable --no-unlogged-table-data my_database > my_database.dump

I am attempting to restore it using the proxy admin user provided by the cloud provider:

pg_restore -d "host=foobar.example.com port=12345 user=my_cloud_admin_user sslrootcert=/path/to/the/cert.crt sslmode=require dbname=my_database" -O -1 my_database.dump

This is the error I am seeing:
pg_restore: error: could not execute query: ERROR:  permission denied to change default privilegesCommand was: ALTER DEFAULT PRIVILEGES FOR ROLE "postgres" IN SCHEMA "public" GRANT SELECT ON TABLES TO "my_database_ro";

N.B. "my_database_ro" being a user that was on the original database, and was successfully created in the new database by restoring a "pg_dumpall --globals-only" into the new database before attempting the pg_restore

---

From: Tom Lane <[email-masked]>
Date: Fri, 13 Jun 2025 14:23:07 -0400

> pg_restore: error: could not execute query: ERROR:  permission denied to change default privilegesCommand was: ALTER DEFAULT PRIVILEGES FOR ROLE "postgres" IN SCHEMA "public" GRANT SELECT ON TABLES…

Well, you aren't going to be able to do that if you're not superuser.

You could undo that ALTER in the source database and re-make the dump,
or edit the dump script to remove this command, or not use
pg_restore's "-1" switch and just ignore this error.

			regards, tom lane

---

From: Adrian Klaver <[email-masked]>
Date: Fri, 13 Jun 2025 12:13:09 -0700

> or edit the dump script to remove this command, or not use

To get at an editable script you can do something like:

pg_restore -f my_database_txt.sql  my_database.dump

This will give you a plain text version of the dump that you can feed 
back to psql to load into remote database.

If you want to do this in steps you can do:

pg_restore -s-f my_database_sch_txt.sql  my_database.dump

to get the object(schema) definitions only and then

pg_restore -a -f my_database_data_txt.sql  my_database.dump

to get the data definitions.

-- 
Adrian Klaver
[email-masked]

---

From: Rachel Roch <[email-masked]>
Date: Sat, 14 Jun 2025 10:42:59 +0200 (CEST)

13 Jun 2025, 20:13 by [email-masked]:

> This will give you a plain text version of the dump that you can feed back to psql to load into remote database.
>

Thanks Adrian !

I had thought maybe maybe I could do a "pg_restore -l my_database.dump" and ignore the relevant line using whatever the other flag is, but sadly there doesn't appear to be enough flexibility, i.e. 

pg_restore -l my_database.dump | fgrep -F postgres
gives:
2067; 826 16607 DEFAULT ACL public DEFAULT PRIVILEGES FOR TABLES postgres

But

pg_restore -l my_database.dump | fgrep -F my_database_ro
gives nothing.   :(

So either your solution or Tom's "just ignore it" sound like they'll work.

---

From: Adrian Klaver <[email-masked]>
Date: Sat, 14 Jun 2025 08:21:19 -0700

> pg_restore -l my_database.dump | fgrep -F my_database_ro

That is because the lines returned from pg_restore -l are not the full 
commands, they represent(generally) a summary of the object, its name 
and the owner.

The error message and your first example above show that the command is 
there. See at here:

https://www.postgresql.org/docs/current/app-pgrestore.html

in the Examples section how you can comment out the line. Then you could 
use -L to feed the list back to pg_restore.

Isn't fgrep -F redundant? As I understand it fgrep = grep -F