Thread: BUG #1249: pg_restore doesnt handle restoring databases not created as a superuser

The following bug has been logged online:

Bug reference:      1249
Logged by:          William Schaub

Email address:      talon@cosmic-cow.net

PostgreSQL version: 7.4.3

Operating system:   Slackware 10 (Linux 2.4.27)

Description:        pg_restore doesnt handle restoring databases not created
as a superuser

Details:

PostgreSQL 7.4.5 (and possibly many versions before)

When restoring databases with pg_restore with the -C
option it will connect to template1, set authorization to
the user that created the database and then reconnect
as the user that created the database to the new database. this is where the
trouble starts, from here on
it trys to set authorization back to the superuser to
do grant and revoke. this will cause the restore to fail.

I have prepared a patch to force reconnects to be
done as the superuser specified in the -S option if
-S if given, otherwise the current behavior is used

Since pg_restore seems to use set authorization to go back to the database
owner before it creates any objects my patch seems safe to me, but i would
like to
know if it causes problems elseware.

it can be found at http://talon.home.cosmic-cow.net/pg_restore.patch

By the way your web form doesnt list 7.4.5 as an available version in the
PostgreSQL version drop down list.

"PostgreSQL Bugs List" <pgsql-bugs@postgresql.org> writes:
> When restoring databases with pg_restore with the -C
> option it will connect to template1, set authorization to
> the user that created the database and then reconnect
> as the user that created the database to the new database. this is where the
> trouble starts, from here on
> it trys to set authorization back to the superuser to
> do grant and revoke. this will cause the restore to fail.

Hmm.  This is still broken in CVS tip, but I think the correct solution
is just to replace "\connect db user" with "\connect db" everywhere.
This will retain the starting real user authentication throughout the
restore process.  We have completely abandoned use of \connect to change
authorization, and this seems just an unintentional hangover from the
old way.

            regards, tom lane

On Fri, 10 Sep 2004, Tom Lane wrote:

> Hmm.  This is still broken in CVS tip, but I think the correct solution
> is just to replace "\connect db user" with "\connect db" everywhere.
> This will retain the starting real user authentication throughout the
> restore process.  We have completely abandoned use of \connect to change
> authorization, and this seems just an unintentional hangover from the
> old way.

Attached is a backport of your CVS commit to 7.4.5 it seems to work fine
here after some testing.