Thread: Upgrading from < 8.1 with users and groups with same name
Hi PostgreSQL developers! I recently got a rather interesting Debian bug [1]: When upgrading a pre-8.1 database to 8.1, the upgrade messes up permissions if the old database had users and groups with the same name. Since in 8.1 they get collapsed to a 'role' there will be a name clash. My current solution checks for this situation and aborts the upgrade if there is at least one name which is both an user and a group (and asks for renaming either before attempting the upgrade again). For an upstream solution, maybe pg_dumpall should detect this situation and abort with an error? Thank you in advance for any idea, Martin [1] http://bugs.debian.org/343622 --=20 Martin Pitt http://www.piware.de Ubuntu Developer http://www.ubuntu.com Debian Developer http://www.debian.org In a world without walls and fences, who needs Windows and Gates?
Martin Pitt wrote: -- Start of PGP signed section. > Hi PostgreSQL developers! > > I recently got a rather interesting Debian bug [1]: When upgrading a > pre-8.1 database to 8.1, the upgrade messes up permissions if the old > database had users and groups with the same name. Since in 8.1 they > get collapsed to a 'role' there will be a name clash. > > My current solution checks for this situation and aborts the upgrade > if there is at least one name which is both an user and a group (and > asks for renaming either before attempting the upgrade again). > > For an upstream solution, maybe pg_dumpall should detect this > situation and abort with an error? I think we decided that the number of users who have this problem and would be using upgraded pg_dumpall to upgrade to 8.1 is too small. (If we had realized this problem pre-8.1, it would have been great to have fixed it.) In your upgrade script, you _know_ they are going to use your code for the upgrade so it is much more useful for you to check it. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
Martin Pitt <martin@piware.de> writes:
> I recently got a rather interesting Debian bug [1]: When upgrading a
> pre-8.1 database to 8.1, the upgrade messes up permissions if the old
> database had users and groups with the same name. Since in 8.1 they
> get collapsed to a 'role' there will be a name clash.
I think the only real problem here is that the role ends up with
NOLOGIN set, which we could probably fix by reordering the commands;
but of course we can't do anything about dumps made with existing
versions of pg_dump.
> My current solution checks for this situation and aborts the upgrade
That seems like serious overkill.
regards, tom lane
Hi Bruce! Bruce Momjian [2005-12-29 20:06 -0500]: > I think we decided that the number of users who have this problem and=20 > would be using upgraded pg_dumpall to upgrade to 8.1 is too small. (If > we had realized this problem pre-8.1, it would have been great to have > fixed it.) In your upgrade script, you _know_ they are going to use > your code for the upgrade so it is much more useful for you to check it. Fine for me; of course I cannot force Debian users to use pg_upgradecluster (the script that contains all the hairy details and checks), there are people who do it the manual way. I just reported it here since other distributions have the same problem, so knowing the official upstream approach is always helpful. :) Thank you, Martin --=20 Martin Pitt http://www.piware.de Ubuntu Developer http://www.ubuntulinux.org Debian Developer http://www.debian.org
Hi! Tom Lane [2005-12-29 22:20 -0500]: > Martin Pitt <martin@piware.de> writes: > > I recently got a rather interesting Debian bug [1]: When upgrading a > > pre-8.1 database to 8.1, the upgrade messes up permissions if the old > > database had users and groups with the same name. Since in 8.1 they > > get collapsed to a 'role' there will be a name clash. >=20 > I think the only real problem here is that the role ends up with > NOLOGIN set, which we could probably fix by reordering the commands; It will also lead to confusion, especially if the user is not in the group with the same name. If the admin assigned different privileges to the group and to the user, then collapsing them into one role would change the privileges for the members of the group, or not? > but of course we can't do anything about dumps made with existing > versions of pg_dump. At least in Debian the upgrade process always uses the latest pg_dumpall. > > My current solution checks for this situation and aborts the upgrade >=20 > That seems like serious overkill. Since I currently do not know any other clean way, it seemed like a safe choice to me for now. The script just aborts and asks the admin to rename the affected user or groups, and then reattempt the upgrade. What would you recommend instead? Thanks, Martin --=20 Martin Pitt http://www.piware.de Ubuntu Developer http://www.ubuntulinux.org Debian Developer http://www.debian.org
Martin Pitt <martin@piware.de> writes:
> Tom Lane [2005-12-29 22:20 -0500]:
>> I think the only real problem here is that the role ends up with
>> NOLOGIN set, which we could probably fix by reordering the commands;
> It will also lead to confusion, especially if the user is not in the
> group with the same name. If the admin assigned different privileges
> to the group and to the user, then collapsing them into one role
> would change the privileges for the members of the group, or not?
It would, but that would have been a really confusing situation to start
with. I'm not finding this a compelling concern.
regards, tom lane