Received: from localhost (maia-2.hub.org [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id B9B7A9FB4BC for ; Fri, 19 Jan 2007 17:45:27 -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 89565-07 for ; Fri, 19 Jan 2007 17:45:16 -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.227]) by postgresql.org (Postfix) with ESMTP id F16CB9FB4B8 for ; Fri, 19 Jan 2007 17:45:19 -0400 (AST) Received: by wr-out-0506.google.com with SMTP id 68so540497wra for ; Fri, 19 Jan 2007 13:45:19 -0800 (PST) DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:message-id:date:from:to:subject:mime-version:content-type; b=Ah8CQz5E5e8Sa55L18hLu7dWqbklz1xGhMckseTxh7dG8Zg7elq0BrFZZCEgMgBTEvLCp1RjQWmMZxzh+Ewpge2vQU4k8P9nzcz+2ZR3twy2p8hcgrhh9XCVDtGQcxsfyqJTPBQO+04W/6rri/4vkab0DMbXnFX/Y00/SGJ2U7k= Received: by 10.90.75.10 with SMTP id x10mr3571187aga.1169243118945; Fri, 19 Jan 2007 13:45:18 -0800 (PST) Received: by 10.90.67.1 with HTTP; Fri, 19 Jan 2007 13:45:18 -0800 (PST) Message-ID: <4544e0330701191345l64b72722j7530db0d7d345f6f@mail.gmail.com> Date: Fri, 19 Jan 2007 15:45:18 -0600 From: "Peter Koczan" To: pgsql-admin@postgresql.org Subject: Upgrading to 8.2, changes in user/group management scripts MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_143102_17932788.1169243118817" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200701/177 X-Sequence-Number: 24333 ------=_Part_143102_17932788.1169243118817 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline 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. Peter ------=_Part_143102_17932788.1169243118817 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline 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.

Peter
------=_Part_143102_17932788.1169243118817--