pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedUsers + Groups = Roles, duplicate name issue
4+ messages / 2 participants
[nested] [flat]
* Users + Groups = Roles, duplicate name issue
@ 2005-12-21 01:42 ljb <ljb220@mindspring.com>
2005-12-21 20:31 ` Re: Users + Groups = Roles, duplicate name issue Jim C. Nasby <jnasby@pervasive.com>
0 siblings, 1 reply; 4+ messages in thread
From: ljb @ 2005-12-21 01:42 UTC (permalink / raw)
To: pgsql-admin
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.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Users + Groups = Roles, duplicate name issue
2005-12-21 01:42 Users + Groups = Roles, duplicate name issue ljb <ljb220@mindspring.com>
@ 2005-12-21 20:31 ` Jim C. Nasby <jnasby@pervasive.com>
2005-12-22 02:32 ` Re: Users + Groups = Roles, duplicate name issue ljb <ljb220@mindspring.com>
0 siblings, 1 reply; 4+ messages in thread
From: Jim C. Nasby @ 2005-12-21 20:31 UTC (permalink / raw)
To: ljb <ljb220@mindspring.com>; +Cc: pgsql-admin
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Users + Groups = Roles, duplicate name issue
2005-12-21 01:42 Users + Groups = Roles, duplicate name issue ljb <ljb220@mindspring.com>
2005-12-21 20:31 ` Re: Users + Groups = Roles, duplicate name issue Jim C. Nasby <jnasby@pervasive.com>
@ 2005-12-22 02:32 ` ljb <ljb220@mindspring.com>
2005-12-27 21:02 ` Re: Users + Groups = Roles, duplicate name issue Jim C. Nasby <jnasby@pervasive.com>
0 siblings, 1 reply; 4+ messages in thread
From: ljb @ 2005-12-22 02:32 UTC (permalink / raw)
To: pgsql-admin
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;
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Users + Groups = Roles, duplicate name issue
2005-12-21 01:42 Users + Groups = Roles, duplicate name issue ljb <ljb220@mindspring.com>
2005-12-21 20:31 ` Re: Users + Groups = Roles, duplicate name issue Jim C. Nasby <jnasby@pervasive.com>
2005-12-22 02:32 ` Re: Users + Groups = Roles, duplicate name issue ljb <ljb220@mindspring.com>
@ 2005-12-27 21:02 ` Jim C. Nasby <jnasby@pervasive.com>
0 siblings, 0 replies; 4+ messages in thread
From: Jim C. Nasby @ 2005-12-27 21:02 UTC (permalink / raw)
To: ljb <ljb220@mindspring.com>; +Cc: 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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2005-12-27 21:02 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-12-21 01:42 Users + Groups = Roles, duplicate name issue ljb <ljb220@mindspring.com>
2005-12-21 20:31 ` Jim C. Nasby <jnasby@pervasive.com>
2005-12-22 02:32 ` ljb <ljb220@mindspring.com>
2005-12-27 21:02 ` Jim C. Nasby <jnasby@pervasive.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox