Re: Users + Groups = Roles, duplicate name issue - Mailing list pgsql-admin

Bummer, it looks like there isn't any good solution for the case of
identical user and group names. You might want to search through the
archives to see if this scenario was discussed when roles were being
designed, but it looks like your best bet is to rename either the user
or the group before dumping.

On Thu, Dec 22, 2005 at 02:32:53AM +0000, ljb wrote:
> jnasby@pervasive.com wrote:
> > Which version of pg_dump did you use to dump the old database? The
> > recommended procedure is to use the newer version of pg_dump (ie:
> > pg_dump from 8.1.1) to dump the old database. It's possible that the
> > newer version of pg_dump has facilities in place to deal with this.
> > Those facilities would obviously be missing from older versions.
>
> Good suggestion.  I was using the 7.4.x pg_dumpall.  So I tried using 8.1.1
> pg_dumpall to see what would happen.  I got the same results - both users
> had rights on both tables after the reload, except for one difference: when
> the 7.4.x database was dumped with 8.1.1 and reloaded into 8.1.1, the
> duplicate user/group name 'test' was not valid for login.  I don't know if
> this is intentional or just a side effect of processing groups after users.
> The 8.1.1 pg_dumpall generated these commands:
>
>   CREATE ROLE ljb;
>   ALTER ROLE ljb WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN ...
>   CREATE ROLE test;
>   ALTER ROLE test WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN ...
> * CREATE ROLE test;
> + ALTER ROLE test WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB NOLOGIN;
>   CREATE ROLE acct;
>   ALTER ROLE acct WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB NOLOGIN;
>   GRANT test TO ljb;
>   GRANT acct TO test;
>
> The second CREATE ROLE test (marked *) gets an error: role already exists,
> just like it does using the 7.4.x dump, and is ignored. But unlike the
> 7.4.x dump, here the second ALTER ROLE (marked +) results in the nologin
> flag being set for test (because it is looking at the test group at this
> point, I presume). The end result is that one user 'ljb' now has rights to
> a table that was not accessible before, and the other user 'test' can't log
> in now.
>
> For reference, here is the setup:
>   create group test;
>   create group acct;
>   create user ljb with password '...' in group test;
>   create user test with password '...' in group acct;
>   create table test_data (id integer);
>   create table money_data (id integer);
>   grant all on test_data to group test;
>   grant all on money_data to group acct;
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
>

--
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461