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

Wed, 06 May 1998 15:51:50 -0400
Tom Lane <tgl@sss.pgh.pa.us>
An item for the TODO list: pg_dump and multiple table owners
> 
> 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.

This is a very good point.  I will look into it.

-- 
Bruce Momjian                          |  830 Blythe Avenue
maillist@candle.pha.pa.us              |  Drexel Hill, Pennsylvania 19026
  +  If your life is a hard drive,     |  (610) 353-9879(w)
  +  Christ can be your backup.        |  (610) 853-3000(h)

Wed, 6 May 1998 17:45:34 -0400 (EDT)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] An item for the TODO list: pg_dump and multiple table
	owners