pg_restore ERROR: permission denied to change default privileges

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 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
pg_restore's "-1" switch and just ignore this error.

regards, tom lane

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 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.klaver@aklaver.com

13 Jun 2025, 20:13 by adrian.klaver@aklaver.com:

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.

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.

On 6/14/25 01:42, Rachel Roch wrote:

13 Jun 2025, 20:13 by adrian.klaver@aklaver.com:

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.

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

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

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

--
Adrian Klaver
adrian.klaver@aklaver.com

14 Jun 2025, 16:21 by adrian.klaver@aklaver.com:

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

You have the wrong end of the stick. ;-)

As per Grep 3.8 release notes (https://lists.gnu.org/archive/html/info-gnu/2022-09/msg00001.html):

"The egrep and fgrep commands, which have been deprecated since release 2.5.3 (2007), now warn that they are obsolescent and should be replaced by grep -E and grep -F."

On 6/15/25 01:06, Rachel Roch wrote:

14 Jun 2025, 16:21 by adrian.klaver@aklaver.com:

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

You have the wrong end of the stick. ;-)

Don't think so, the -F is redundant.

Try grep -F then fgrep and then fgrep -F on the same file.

As per Grep 3.8 release notes (https://lists.gnu.org/archive/html/info-gnu/2022-09/msg00001.html):

"The egrep and fgrep commands, which have been deprecated since release 2.5.3 (2007), now warn that they are obsolescent and should be replaced by grep -E and grep -F."

--
Adrian Klaver
adrian.klaver@aklaver.com

On 6/15/25 08:15, Adrian Klaver wrote:

On 6/15/25 01:06, Rachel Roch wrote:

14 Jun 2025, 16:21 by adrian.klaver@aklaver.com:

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

You have the wrong end of the stick. ;-)

Don't think so, the -F is redundant.

It is redundant for fgrep.

Try grep -F then fgrep and then fgrep -F on the same file.

As per Grep 3.8 release notes
(https://lists.gnu.org/archive/html/info-gnu/2022-09/msg00001.html):

"The egrep and fgrep commands, which have been deprecated since
release 2.5.3 (2007), now warn that they are obsolescent and should
be replaced by grep -E and grep -F."

--
Adrian Klaver
adrian.klaver@aklaver.com

15 Jun 2025, 17:15 by adrian.klaver@aklaver.com:

It is redundant for fgrep.

Talk about muscle memory !  How many years have I still been typing fgrep -F thinking I was typing grep -F !