Re: Problem with pg_upgrade (8.4 -> 9.0) due to ALTER DATABASE SET ROLE

Florian Pflug wrote:
> Hi
> 
> I've just ran into a problem while upgrading from 8.4 to 9.0.
> 
> pg_upgrade aborted during the step "Adding support functions to new
> cluster" with "ERROR:  permission denied for language c" error.
> Unfortunately, the log didn't include the name of the database where
> the error occurred, so it took me a while to figure out that the culprit
> was a "ALTER DATABASE SET ROLE = <non-superuser>" I had done on one of
> my databases, which effectively prevented pg_upgrade from connection
> with superuser privileges.
> 
> While one could argue that this behaviour is perfectly consistent, I
> believe most users will expect pg_upgrade (and to a lesser extent also
> pg_dump and pg_restore) to be unaffected by such settings.
> 
> Should we provide a way (for super-users only, of course) to skip
> per-database/per-role settings when connecting?

I think pg_dumpall would have failed with this setup too, so I don't see
this as a pg_upgrade bug, nor something that I am willing to risk adding
to pg_upgrade.

Perhaps the error report can be improved.

-- Bruce Momjian  <bruce@momjian.us>        http://momjian.us EnterpriseDB
http://enterprisedb.com
 + It's impossible for everything to be true. +