pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Peter Koczan <pjkoczan@gmail.com>
To: pgsql-admin@postgresql.org
Subject: Upgrading to 8.2, changes in user/group management scripts
Date: Fri, 19 Jan 2007 15:45:18 -0600
Message-ID: <4544e0330701191345l64b72722j7530db0d7d345f6f@mail.gmail.com> (raw)

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

view thread (5+ messages)  latest in thread

Message-ID: <4544e0330701191345l64b72722j7530db0d7d345f6f@mail.gmail.com>
Permalink:  ../4544e0330701191345l64b72722j7530db0d7d345f6f@mail.gmail.com/
Also on:    postgresql.org/message-id/4544e0330701191345l64b72722j7530db0d7d345f6f@mail.gmail.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: pjkoczan@gmail.com
  Subject: Re: Upgrading to 8.2, changes in user/group management scripts
  In-Reply-To: <4544e0330701191345l64b72722j7530db0d7d345f6f@mail.gmail.com>
  References: <4544e0330701191345l64b72722j7530db0d7d345f6f@mail.gmail.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