Thread: Upgrading from < 8.1 with users and groups with same name

Upgrading from < 8.1 with users and groups with same name

From
Martin Pitt
Date:
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?

Re: Upgrading from < 8.1 with users and groups with same name

From
Bruce Momjian
Date:
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

Re: Upgrading from < 8.1 with users and groups with same name

From
Tom Lane
Date:
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

Re: Upgrading from < 8.1 with users and groups with same name

From
Martin Pitt
Date:
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

Re: Upgrading from < 8.1 with users and groups with same name

From
Martin Pitt
Date:
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

Re: Upgrading from < 8.1 with users and groups with same name

From
Tom Lane
Date:
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