Обсуждение: pg_restore ERROR: permission denied to change default privileges
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'thave 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=requiredbname=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 DEFAULTPRIVILEGES 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 byrestoring a "pg_dumpall --globals-only" into the new database before attempting the pg_restore
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
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 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
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 otherflag 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 theother 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