X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 059639DCC87 for ; Wed, 21 Dec 2005 22:52:18 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 76554-03 for ; Wed, 21 Dec 2005 22:52:22 -0400 (AST) Received: from news.hub.org (news.hub.org [200.46.204.72]) by postgresql.org (Postfix) with ESMTP id EE3279DCC8A for ; Wed, 21 Dec 2005 22:52:15 -0400 (AST) Received: from news.hub.org (news.hub.org [200.46.204.72]) by news.hub.org (8.13.1/8.13.1) with ESMTP id jBM2qDUo058382 for ; Thu, 22 Dec 2005 02:52:13 GMT (envelope-from news@news.hub.org) Received: (from news@localhost) by news.hub.org (8.13.1/8.13.1/Submit) id jBM2Wrgm096111 for pgsql-admin@postgresql.org; Thu, 22 Dec 2005 02:32:53 GMT (envelope-from news) From: ljb X-Newsgroups: pgsql.admin Subject: Re: Users + Groups = Roles, duplicate name issue Date: Thu, 22 Dec 2005 02:32:53 +0000 (UTC) Organization: Hub.Org Networking Services Lines: 43 Message-ID: References: <20051221203123.GG72143@pervasive.com> X-Complaints-To: usenet@news.hub.org User-Agent: slrn/0.9.8.1 (Linux) To: pgsql-admin@postgresql.org X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=-1.44 required=5 tests=[ALL_TRUSTED=-1.44] X-Spam-Score: -1.44 X-Spam-Level: X-Archive-Number: 200512/262 X-Sequence-Number: 19982 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;