public inbox for [email protected]
help / color / mirror / Atom feedpg_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]>
2025-06-13 18:23 ` Re: pg_restore ERROR: permission denied to change default privileges Tom Lane <[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:08 pg_restore ERROR: permission denied to change default privileges Rachel Roch <[email protected]>
@ 2025-06-13 18:23 ` 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]>
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 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 ` Adrian Klaver <[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-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-14 08:42 ` Rachel Roch <[email protected]>
2025-06-14 15:21 ` Re: pg_restore ERROR: permission denied to change default privileges Adrian Klaver <[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-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-14 08:42 ` Re: pg_restore ERROR: permission denied to change default privileges Rachel Roch <[email protected]>
@ 2025-06-14 15:21 ` Adrian Klaver <[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