Thread: Users + Groups = Roles, duplicate name issue
I loaded a 7.4.x dump into a new 8.1.1 database and found out what happens
if you had the same name as both a user and a group. You can get users with
more rights than they had before. I guess it is too late, but perhaps a
mention in the release text would have been a good idea. Advise people to
rename any group which has the same name as a user.
For example, if at 7.4.x I have:
Group: Is granted all rights to table:
test test_data
acct money_data
Username: Member of group: And therefore gets all rights to table:
ljb test test_data
test acct money_data
After loading the dump into 8.1.1, the test user and test group get merged
into a single role, so the test user gets granted all rights to the test_data
table. In addition, 'ljb' now effectively is a member of the 'acct' group
(via the test role), so is granted all rights to the money_data table.
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. On Wed, Dec 21, 2005 at 01:42:14AM +0000, ljb wrote: > I loaded a 7.4.x dump into a new 8.1.1 database and found out what happens > if you had the same name as both a user and a group. You can get users with > more rights than they had before. I guess it is too late, but perhaps a > mention in the release text would have been a good idea. Advise people to > rename any group which has the same name as a user. > > For example, if at 7.4.x I have: > Group: Is granted all rights to table: > test test_data > acct money_data > > Username: Member of group: And therefore gets all rights to table: > ljb test test_data > test acct money_data > > After loading the dump into 8.1.1, the test user and test group get merged > into a single role, so the test user gets granted all rights to the test_data > table. In addition, 'ljb' now effectively is a member of the 'acct' group > (via the test role), so is granted all rights to the money_data table. > > ---------------------------(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 > -- 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
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;
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