pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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;



view thread (4+ messages)  latest in thread

Message-ID: <dod38j$2h2g$1@news.hub.org>
Permalink:  ../dod38j$2h2g$1@news.hub.org/
Also on:    postgresql.org/message-id/dod38j$2h2g$1@news.hub.org

 · 

reply

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