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

Florian Pflug <fgp(at)phlo(dot)org> writes:
> 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.

That seems like a pretty stupid thing to have done; it would prevent
*any* connection to that database with superuser privileges, no?

> 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.

This is about like arguing that pg_dump and pg_upgrade should still work
after you've done "delete from pg_proc;". Superusers are assumed to
know what they're doing and not break fundamental operations.

I'm thinking that if there's anything we should forbid here, it's the
ALTER ... SET itself. In particular, some experimentation suggests that
a non-superuser database owner can do it:

regression=# create user joe;
CREATE ROLE
regression=# create database joe with owner joe;
CREATE DATABASE
regression=# \c joe joe
You are now connected to database "joe" as user "joe".
joe=> alter database joe set role joe;
ALTER DATABASE

which seems to me at least a bad idea and arguably a security hazard.

regards, tom lane