Upgrading from < 8.1 with users and groups with same name

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

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

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

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

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