Received: from localhost (maia-2.hub.org [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id E6B759FB549 for ; Mon, 22 Jan 2007 16:37:02 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.187]) (amavisd-new, port 10024) with ESMTP id 79412-02 for ; Mon, 22 Jan 2007 16:36:55 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.4 Received: from wr-out-0506.google.com (wr-out-0506.google.com [64.233.184.236]) by postgresql.org (Postfix) with ESMTP id CBF439FB548 for ; Mon, 22 Jan 2007 16:36:57 -0400 (AST) Received: by wr-out-0506.google.com with SMTP id 68so802598wra for ; Mon, 22 Jan 2007 12:36:57 -0800 (PST) DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; b=KeTwssGImkCWNGrZxMh/7kkeT99jOsXqKivG78jNf7Ee+h8+dr0b/gzD5CRfzDSFFNLsyHuWAkWoEgC49cwHtMsiRqAIV/4VRqdmTIHqkdVdHqFHlRX8tWAQcEdqFmARHFt7cE4lwb79KfwZF/Zcj9zEOv7rJI6Kb2jD17lQpbc= Received: by 10.90.72.10 with SMTP id u10mr6818373aga.1169498215820; Mon, 22 Jan 2007 12:36:55 -0800 (PST) Received: by 10.90.63.9 with HTTP; Mon, 22 Jan 2007 12:36:55 -0800 (PST) Message-ID: <4544e0330701221236o205ec223t7da292a1cce6bba3@mail.gmail.com> Date: Mon, 22 Jan 2007 14:36:55 -0600 From: "Peter Koczan" To: "Jim C. Nasby" Subject: Re: Upgrading to 8.2, changes in user/group management scripts Cc: pgsql-admin@postgresql.org In-Reply-To: <20070121173854.GD77382@nasby.net> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_172940_3515303.1169498215627" References: <4544e0330701191345l64b72722j7530db0d7d345f6f@mail.gmail.com> <20070121173854.GD77382@nasby.net> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200701/204 X-Sequence-Number: 24360 ------=_Part_172940_3515303.1169498215627 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline The main thing I'm worried about is the orphaned objects problem. It's not adding users so much as removing them that I'm concerned about (I work at a University and we remove inactive/non-enrolled users). These cases would likely require a lot more in-depth intervention by myself and other staff. On 1/21/07, Jim C. Nasby wrote: > > 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) > ------=_Part_172940_3515303.1169498215627 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline The main thing I'm worried about is the orphaned objects problem. It's not adding users so much as removing them that I'm concerned about (I work at a University and we remove inactive/non-enrolled users). These cases would likely require a lot more in-depth intervention by myself and other staff.

On 1/21/07, Jim C. Nasby <jim@nasby.net> wrote:
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)

------=_Part_172940_3515303.1169498215627--