pg_dump doesn't cope very gracefully at all with databases containing
multiple tables not all owned by the same person. It knows enough
to issue \connect commands in the reload script that cause the
new tables to be owned by the same people as before. But the reload
script fails with permission errors later on during the data copying
phase, if the tables are not made world-writable.
This is certain to happen if the -z switch is not used to dump the
tables' grant/revoke status. I suspect that pg_dump ought not try
to save/restore table ownership unless it is also saving/restoring
access rights; that is, if -z is not given the \connect commands
shouldn't appear either. Then, without -z the reload script will
generate a new database wholly owned by the script invoker.
When using -z, the failure of the copy-in command could be fixed by
issuing more \connect commands so that the data transfer is done while
logged in as the table owner.
This is particularly nasty because the reload script fails even if
run as the Postgres superuser. I think this is because the script
reconnects as the various table owners and thereby loses superuser
access rights.
regards, tom lane
