Received: from localhost (maia-5.hub.org [200.46.204.182]) by postgresql.org (Postfix) with ESMTP id 9F5A29FB1F9 for ; Sun, 21 Jan 2007 13:39:05 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.182]) (amavisd-new, port 10024) with ESMTP id 44384-07 for ; Sun, 21 Jan 2007 13:38:50 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from noel.decibel.org (noel.decibel.org [67.100.216.10]) by postgresql.org (Postfix) with ESMTP id CE0D19FACFE for ; Sun, 21 Jan 2007 13:39:01 -0400 (AST) Received: by noel.decibel.org (Postfix, from userid 1001) id 75AF756497; Sun, 21 Jan 2007 11:38:58 -0600 (CST) Received: (hashcash-sendmail, from uid 1001); Sun, 21 Jan 2007 11:38:55 -0600 Date: Sun, 21 Jan 2007 11:38:55 -0600 From: "Jim C. Nasby" To: Peter Koczan Cc: pgsql-admin@postgresql.org Subject: Re: Upgrading to 8.2, changes in user/group management scripts Message-ID: <20070121173854.GD77382@nasby.net> References: <4544e0330701191345l64b72722j7530db0d7d345f6f@mail.gmail.com> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <4544e0330701191345l64b72722j7530db0d7d345f6f@mail.gmail.com> X-Operating-System: FreeBSD 6.1-RELEASE-p10 amd64 X-Distributed: Join the Effort! http://www.distributed.net User-Agent: Mutt/1.5.13 (2006-08-11) X-Hashcash: 1:20:070121:pjkoczan@gmail.com::OtxXzkInbEadq3q6:0000000000000000000 0000000000000000000000007UXb X-Hashcash: 1:20:070121:pgsql-admin@postgresql.org::6DwQQcvcWvNKnIdr:00000000000 0000000000000000000000009BRC X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200701/182 X-Sequence-Number: 24338 On Fri, Jan 19, 2007 at 03:45:18PM -0600, Peter Koczan wrote: > I'm upgrading our Postgres installation from 7.4 to 8.2 (I know, I know, but > we've been busy). The biggest thing to change will be our user/group > management scripts, as they directly modify pg_shadow and pg_group. Beyond > being potentially stupid (but hey, I didn't write it), this just won't work > anymore, for a few reasons, which I will get to in a bit. > > What we used to do (all in a transaction block so it's nice and atomic): > For users: Delete pg_shadow, read in logins/uids from /etc/passwd and create > users, making sure the Unix uid matches up with the postgres sysid. > For groups: For each group, drop the group, read in the new group from > either another database table or some other mechanism. > > Now, having the Unix uids match up with the Postgres sysids isn't such a big > deal since we'll be using Kerberos, which can presumably match up based on > login name. It was nice to have them match up, but it's probably not > necessary. However, the above mechanisms won't work for the following > reasons: > > 1. pg_shadow and pg_group are now views, and you can't update views. > 2a. Simply dropping a user is insufficient if the user owns anything in the > database cluster. > 2b. Deleting a user by removing the row from pg_authid works, but then the > object owner is unknown and the object disappears unless you know where to > look for it in the system tables. The objects are effectively orphaned. > 3. There seems to be no way to recover the old sysid, as the "WITH SYSID > uid" clause in CREATE USER/CREATE ROLE is now just noise, and trying to > insert it directly into the system table proved fruitless. If you can't > recover the old sysid, it leads to the orphaned objects problem described in > 2b. > > So, I'm wondering how I can do something roughly equivalent to this, I've > been considering using some sort of "diff"-like mechanism, but it'd be nice > to have something simpler. Any help would be greatly appreciated. You already stated there's no reason to have the ID's match, so why bother? Just check each user/group name to see if it already exists and add it if it doesn't. -- Jim Nasby jim@nasby.net EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)