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

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

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.2 (GNU/Linux)

iD8DBQFDtFx7DecnbV4Fd/IRAvxxAJ9tJBYinbI2jatrLRoOd4l/cdGJaACgpGe5
k+YczZw+gplvgwD/tvytx8E=
=OsmQ
-----END PGP SIGNATURE-----

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

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

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

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

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

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.1 (GNU/Linux)

iD8DBQFDtQmnDecnbV4Fd/IRAq51AKCjxeNsVe6m73CwJWHzKl2fnJn13QCfejrf
fSmikMZFShuo5XG74GqhEvg=
=Yu9q
-----END PGP SIGNATURE-----

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

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.1 (GNU/Linux)

iD8DBQFDtQsiDecnbV4Fd/IRAsA+AJ4vH2X8v1MjLBGen4Xi6ozkTuq9cQCgr8DO
DTAEN7xv+rsYgb60KVxC490=
=uhp3
-----END PGP SIGNATURE-----

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

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly