Thanks a lot Tom for the detailed info. Few queries -
1. Is there any way I can create my own list file?
2. For Alter table <<table name>> owner to <<role name>> statement, what is the equivalent line in the list file?
3. Was using List file to achieve the following, i.e. executing the following lines in this order -
* Restoring the dump file using list file with role <<user1>>
* create table schema1.table1
* grant all on schema1 to user2
* alter table schema1.table1 to user2
Is the above possible using list file, if not what is the best alternative as we would like to automate.
PS: Don't have super user privileges on the target.
Regards,
Aditya D
On Sat, 28 Oct 2023 at 00:04, Tom Lane <tgl@xxxxxxxxxxxxx> wrote:
Aditya D <dsaditya91@xxxxxxxxx> writes:
> Problem Statement - The ACL command i.e. The grant statement gets called
> out at the end while performing pg_restore and the use case is to call it
> at the beginning. I believe even though the re-ordering is done the
> statements are executed using the "Internal Archive Id" while referring
> to the dump file.
Yeah, see the comments for RestorePass:
* For historical reasons, ACL items are interspersed with everything else in
* a dump file's TOC; typically they're right after the object they're for.
* However, we need to restore data before ACLs, as otherwise a read-only
* table (ie one where the owner has revoked her own INSERT privilege) causes
* data restore failures. On the other hand, matview REFRESH commands should
* come out after ACLs, as otherwise non-superuser-owned matviews might not
* be able to execute. (If the permissions at the time of dumping would not
* allow a REFRESH, too bad; we won't fix that for you.) We also want event
* triggers to be restored after ACLs, so that they can't mess those up.
*
* These considerations force us to make three passes over the TOC,
* restoring the appropriate subset of items in each pass. We assume that
* the dependency sort resulted in an appropriate ordering of items within
* each subset.
Use of an -L switch overrides the dependency sort, but not this
pass mechanism.
Whatever you're hoping to do by overriding that is most likely
just going to replace one kind of breakage by another.
regards, tom lane
