Feedback-ID: i76984098:Fastmail
Message-ID: <e12c9b8d-0d77-4462-9f52-06376d5f9d65@aklaver.com>
Date: Fri, 13 Jun 2025 12:13:09 -0700
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: pg_restore ERROR: permission denied to change default privileges
To: Tom Lane <tgl@sss.pgh.pa.us>, Rachel Roch <rroch@tutanota.de>
Cc: Pgsql General <pgsql-general@lists.postgresql.org>
References: <OSeT_Gx--F-9@tutanota.de> <1407215.1749838987@sss.pgh.pa.us>
Content-Language: en-US
From: Adrian Klaver <adrian.klaver@aklaver.com>
In-Reply-To: <1407215.1749838987@sss.pgh.pa.us>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
Archived-At: <https://www.postgresql.org/message-id/e12c9b8d-0d77-4462-9f52-06376d5f9d65%40aklaver.com>
Precedence: bulk

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