Re: pg_restore ERROR: permission denied to change default privileges

On 6/13/25 11:23, Tom Lane wrote:
> Rachel Roch <rroch@tutanota.de> writes:
>> This is the error I am seeing:
>> pg_restore: error: could not execute query: ERROR:  permission denied to change default privilegesCommand was: ALTER
DEFAULTPRIVILEGES FOR ROLE "postgres" IN SCHEMA "public" GRANT SELECT ON TABLES TO "my_database_ro";
 
> 
> 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

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.

> pg_restore's "-1" switch and just ignore this error.
> 
>             regards, tom lane
> 
> 

-- 
Adrian Klaver
adrian.klaver@aklaver.com