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(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?

I had two developers working with that database who regularly modify the schema, often creating new objects (it's a development machine). They both were annoyed that if one of them created a table, he'd be the owner and some operations on that table would be restricted to him and superusers. The "ALTER DATABASE SET ROLE" fixes that nicely for me. When I needed to 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 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.

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 settings for other GUCs are fine.
For example, what about
synchronous_commit=off
vacuum_freeze_min_age
datestyle
sql_inheritance
standard_conforming_strings
array_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 too late 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