Re: Users + Groups = Roles, duplicate name issue

[ljb <ljb220@xxxxxxxxxxxxxx>]

jnasby@xxxxxxxxxxxxx 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;