Re: Users + Groups = Roles, duplicate name issue

jnasby(at)pervasive(dot)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;