From: ljb <ljb220@mindspring.com>
To: pgsql-admin@postgresql.org
Subject: Re: Users + Groups = Roles, duplicate name issue
Date: Thu, 22 Dec 2005 02:32:53 +0000 (UTC)
Message-ID: <dod38j$2h2g$1@news.hub.org> (raw)
References: <doabtl$q00$1@news.hub.org>
<20051221203123.GG72143@pervasive.com>
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;
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-admin@postgresql.org
Cc: ljb220@mindspring.com
Subject: Re: Users + Groups = Roles, duplicate name issue
In-Reply-To: <dod38j$2h2g$1@news.hub.org>
References: <doabtl$q00$1@news.hub.org> <20051221203123.GG72143@pervasive.com> <dod38j$2h2g$1@news.hub.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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