Re: pg_restore ERROR: permission denied to change default privileges

On 6/13/25 11:23, Tom Lane wrote:
> Rachel Roch <rroch(at)tutanota(dot)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 DEFAULT PRIVILEGES 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(dot)klaver(at)aklaver(dot)com