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

On Thu, Jan 6, 2011 at 1:59 PM, Bruce Momjian <bruce(at)momjian(dot)us> wrote:
> Well, if everyone who logs in gets the same username, you can easily
> conclude that trying to dump/restore the database will cause problems if
> you have objects in there that are not owned by that user.

I can't, and neither could Florian. I'm not sure why this is so
obvious to you and Tom. Unless I've made some catastrophic *manual*
change to the system catalogs, like nuking pg_proc, I expect dump and
restore to just work. pg_dump's job is to emit a series of commands
that will work. Every time I run across a case where it doesn't, I'm
violently annoyed, because it's happened to me as a user and it feels
like a bug every time. Florian is probably made of a bit sterner
stuff than the typical user, but a typical user doesn't go "Oh, gee,
dump and restore didn't work, I guess that setting I installed in
there six years ago must actually be something that the developers
never intended for me to do." First they cuss, and then they blame us
for not being able to dump the database that we let them create, and
then if they're really ticked they go use some other product. When
someone actually takes the time to troubleshoot what broke and let us
know, the only correct response from our end is to say "thanks, we'll
work on making that less confusing", not "well that was a stupid thing
to do".

> I now realize the pg_upgrade problem was that it requires super-user
> objects.  You could argue that requiring the ability for a super-user to
> do things in every database is either reasonable or overly-restrictive.
> I am not sure which it is, but I do know pg_upgrade requires it.  Does
> anything else require super-user in every database.

Monitoring and/or management applications of any sort, I would assume.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company