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

On Dec12, 2010, at 17:01 , Tom Lane wrote:
> Florian Pflug <fgp@phlo.org> writes:
>> pg_upgrade aborted during the step "Adding support functions to new cluster" with "ERROR:  permission denied for
languagec" error. Unfortunately, the log didn't include the name of the database where the error occurred, so it took
mea 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?

I had two developers working with that database who regularly modify the schema, often creating new objects (it's a
developmentmachine). They both were annoyed that if one of them created a table, he'd be the owner and some operations
onthat table would be restricted to him and superusers. The "ALTER DATABASE SET ROLE" fixes that nicely for me. When I
neededto work as a superuser with that database, I simply did "SET ROLE <superuser role>" to restore my superuser
powers.

Nowadays, I could probably do the "SET ROLE" just for some specific combination of user and database. That option,
however,wasn't there at the time I did the "ALTER DATABASE SET ROLE". 

>> While one could argue that this behaviour is perfectly consistent, I believe most users will expect pg_upgrade (and
toa 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.

Sure. If you believe in proof by exaggeration, which I don't.

The way I see it, how is a DBA supposed to know that setting a per-database ROLE is a bad idea, but per-database
settingsfor other GUCs are fine. 
For example, what about synchronous_commit=off vacuum_freeze_min_age datestyle sql_inheritance
standard_conforming_stringsarray_nulls default_with_oids ... 

Without checking the code, all of these have about the same chance of breaking pg_upgrade. But then, by your line of
reasoning,"ALTER DATABASE SET ROLE" shouldn't haven been invented in the first place. Which maybe even true, but it's
toolate for that. So the next best thing, IMHO, is to give superusers a way to avoid the hazard it poses. 
> 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.
I'm sorry, I don't see that security hazard there. Care to explain?

best regards,
Florian Pflug