- How does the above point affect full dumps that include schema and data? In my proposal, the copy commands will run as the user running the script, not the table owner anymore. Presumably, the user running the script is a superuser. Given that it is possible for a table owner to revoke their own INSERT privilege on their table, the existing behaviour is broken anyway.
This is why GRANT/REVOKE has to be postponed to the end. I think it would be a lot simpler and more reliable if you also postponed ALTER OWNER.
Ok, I have thought about this a bit and I have decided that you are right in that grant revoke MUST be deferred to the end of the file.
The reason is that there are two conditions under which we expect a dump restore to work. First is if you are restoring as a superuser second is if you are restoring as a user who owns ALL the objects in the file.
Restoring as superuser will work so long as we use alter owner instead of changing the current user.
The second is more tricky due to the fact that an object owner can revoke their own privileges from their objects. If a user does this, it cannot be guaranteed that they will be able to restore unless all grants are done last and all objects are initially created as that user. Consider revoking your own REFERENCES privilege, as an example.
Hence, I will have to make it that all existing dumping is done, then all the ownership changes will be done, then all the grants will be done.
Does that seem reasonable?
Chris
---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to increase your free space map settings