pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Jim C. Nasby <jnasby@pervasive.com>
To: ljb <ljb220@mindspring.com>
Cc: pgsql-admin@postgresql.org
Subject: Re: Users + Groups = Roles, duplicate name issue
Date: Tue, 27 Dec 2005 15:02:39 -0600
Message-ID: <20051227210239.GN72143@pervasive.com> (raw)
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>

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



view thread (4+ messages)

Message-ID: <20051227210239.GN72143@pervasive.com>
Permalink:  ../20051227210239.GN72143@pervasive.com/
Also on:    postgresql.org/message-id/20051227210239.GN72143@pervasive.com

 · 

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: jnasby@pervasive.com, ljb220@mindspring.com
  Subject: Re: Users + Groups = Roles, duplicate name issue
  In-Reply-To: <20051227210239.GN72143@pervasive.com>
  References: <doabtl$q00$1@news.hub.org> <20051221203123.GG72143@pervasive.com> <dod38j$2h2g$1@news.hub.org> <20051227210239.GN72143@pervasive.com>

* 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