public inbox for [email protected]help / color / mirror / Atom feed
pg_restore ERROR: permission denied to change default privileges 5+ messages / 3 participants [nested] [flat]
* pg_restore ERROR: permission denied to change default privileges @ 2025-06-13 18:08 Rachel Roch <[email protected]> 0 siblings, 1 reply; 5+ messages in thread From: Rachel Roch @ 2025-06-13 18:08 UTC (permalink / raw) To: Pgsql General <[email protected]> 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 ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: pg_restore ERROR: permission denied to change default privileges @ 2025-06-13 18:23 Tom Lane <[email protected]> parent: Rachel Roch <[email protected]> 0 siblings, 2 replies; 5+ messages in thread From: Tom Lane @ 2025-06-13 18:23 UTC (permalink / raw) To: Rachel Roch <[email protected]>; +Cc: Pgsql General <[email protected]> Rachel Roch <[email protected]> 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 ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: pg_restore ERROR: permission denied to change default privileges @ 2025-06-13 19:13 Adrian Klaver <[email protected]> parent: Tom Lane <[email protected]> 1 sibling, 0 replies; 5+ messages in thread From: Adrian Klaver @ 2025-06-13 19:13 UTC (permalink / raw) To: Tom Lane <[email protected]>; Rachel Roch <[email protected]>; +Cc: Pgsql General <[email protected]> On 6/13/25 11:23, Tom Lane wrote: > Rachel Roch <[email protected]> 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 [email protected] ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: pg_restore ERROR: permission denied to change default privileges @ 2025-06-14 08:42 Rachel Roch <[email protected]> parent: Tom Lane <[email protected]> 1 sibling, 1 reply; 5+ messages in thread From: Rachel Roch @ 2025-06-14 08:42 UTC (permalink / raw) To: Adrian Klaver <[email protected]>; +Cc: Tom Lane <[email protected]>; Pgsql General <[email protected]> 13 Jun 2025, 20:13 by [email protected]: > > 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. ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: pg_restore ERROR: permission denied to change default privileges @ 2025-06-14 15:21 Adrian Klaver <[email protected]> parent: Rachel Roch <[email protected]> 0 siblings, 0 replies; 5+ messages in thread From: Adrian Klaver @ 2025-06-14 15:21 UTC (permalink / raw) To: Rachel Roch <[email protected]>; +Cc: Tom Lane <[email protected]>; Pgsql General <[email protected]> On 6/14/25 01:42, Rachel Roch wrote: > > > > 13 Jun 2025, 20:13 by [email protected]: > >> >> 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 [email protected] ^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2025-06-14 15:21 UTC | newest] Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2025-06-13 18:08 pg_restore ERROR: permission denied to change default privileges Rachel Roch <[email protected]> 2025-06-13 18:23 ` Re: pg_restore ERROR: permission denied to change default privileges Tom Lane <[email protected]> 2025-06-13 19:13 ` Re: pg_restore ERROR: permission denied to change default privileges Adrian Klaver <[email protected]> 2025-06-14 08:42 ` Re: pg_restore ERROR: permission denied to change default privileges Rachel Roch <[email protected]> 2025-06-14 15:21 ` Re: pg_restore ERROR: permission denied to change default privileges Adrian Klaver <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox