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

Robert Haas wrote:
> On Wed, Jan 5, 2011 at 11:08 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> > Or we could take the approach somebody was just espousing about
> >
> >> Our job is to prevent the user from *accidentally*
> >> shooting themselves in the foot.
>
> I don't see how you can compare those two cases with a straight face.
> In the FOREIGN KEY NOT ENFORCED case, there is an explicit piece of
> syntax by means of which the user is asking for the dangerous
> behavior. In this case, the user made a settings change which was
> allowed by the system and solved his problem, and then pg_upgrade
> broke. If he had typed ALTER DATABASE .. SET ROLE .. BREAK
> PG_UPGRADE, the two cases would be comparable. Or if we failed to
> enforce foreign keys by default, that'd be comparable, too.
>
> How exactly is the user supposed to know that ALTER DATABASE .. SET
> ROLE is a bad idea? You've repeatedly made remarks about
> "deliberately hosing the login system", but you've offered no evidence
> that the user "deliberately hosed" anything. Changed the behavior?
> Well, yeah. And fixed his problem, too! I even sympathize with his
> use case. Hosed? Well, maybe. It worked for him, until he tried to
> run pg_upgrade. Deliberately hosed, like he did it just to break
> things? Doesn't seem that way. Your argument rests on the
> presumption that the user should have known better than to execute a
> command which didn't produce an error and did solve his problem.
> Perhaps that's a reasonable argument in some cases - a user might be
> reasonably expected to foresee that setting work_mem to 100GB could
> cause problems even if it happens to fix the immediate issue, based on
> the description of the parameter - but neither you nor anyone else on
> this thread have offered more than hand-waving to explain how the user
> was supposed to know that it was unwise, or even to substantiate your
> position that it WAS unwise.

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

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +