pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Upgrading to 8.2, changes in user/group management scripts
5+ messages / 4 participants
[nested] [flat]

* Upgrading to 8.2, changes in user/group management scripts
@ 2007-01-19 21:45 Peter Koczan <pjkoczan@gmail.com>
  2007-01-21 17:38 ` Re: Upgrading to 8.2, changes in user/group management scripts Jim C. Nasby <jim@nasby.net>
  0 siblings, 1 reply; 5+ messages in thread

From: Peter Koczan @ 2007-01-19 21:45 UTC (permalink / raw)
  To: pgsql-admin

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

^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: Upgrading to 8.2, changes in user/group management scripts
  2007-01-19 21:45 Upgrading to 8.2, changes in user/group management scripts Peter Koczan <pjkoczan@gmail.com>
@ 2007-01-21 17:38 ` Jim C. Nasby <jim@nasby.net>
  2007-01-22 20:36   ` Re: Upgrading to 8.2, changes in user/group management scripts Peter Koczan <pjkoczan@gmail.com>
  0 siblings, 1 reply; 5+ messages in thread

From: Jim C. Nasby @ 2007-01-21 17:38 UTC (permalink / raw)
  To: Peter Koczan <pjkoczan@gmail.com>; +Cc: pgsql-admin

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)



^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: Upgrading to 8.2, changes in user/group management scripts
  2007-01-19 21:45 Upgrading to 8.2, changes in user/group management scripts Peter Koczan <pjkoczan@gmail.com>
  2007-01-21 17:38 ` Re: Upgrading to 8.2, changes in user/group management scripts Jim C. Nasby <jim@nasby.net>
@ 2007-01-22 20:36   ` Peter Koczan <pjkoczan@gmail.com>
  2007-01-22 22:09     ` Re: Upgrading to 8.2, changes in user/group management scripts Stephen Frost <sfrost@snowman.net>
  2007-01-22 22:35     ` Re: Upgrading to 8.2, changes in user/group management scripts Alvaro Herrera <alvherre@commandprompt.com>
  0 siblings, 2 replies; 5+ messages in thread

From: Peter Koczan @ 2007-01-22 20:36 UTC (permalink / raw)
  To: Jim C. Nasby <jim@nasby.net>; +Cc: pgsql-admin

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)
>

^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: Upgrading to 8.2, changes in user/group management scripts
  2007-01-19 21:45 Upgrading to 8.2, changes in user/group management scripts Peter Koczan <pjkoczan@gmail.com>
  2007-01-21 17:38 ` Re: Upgrading to 8.2, changes in user/group management scripts Jim C. Nasby <jim@nasby.net>
  2007-01-22 20:36   ` Re: Upgrading to 8.2, changes in user/group management scripts Peter Koczan <pjkoczan@gmail.com>
@ 2007-01-22 22:09     ` Stephen Frost <sfrost@snowman.net>
  1 sibling, 0 replies; 5+ messages in thread

From: Stephen Frost @ 2007-01-22 22:09 UTC (permalink / raw)
  To: Peter Koczan <pjkoczan@gmail.com>; +Cc: Jim C. Nasby <jim@nasby.net>; pgsql-admin

* Peter Koczan (pjkoczan@gmail.com) wrote:
> 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.

Isn't there an option to drop the associated objects...?  Not sure if
that's an option for you though.

	Stephen

Attachments:

  signature.asc (application/pgp-signature, 188B) download

^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: Upgrading to 8.2, changes in user/group management scripts
  2007-01-19 21:45 Upgrading to 8.2, changes in user/group management scripts Peter Koczan <pjkoczan@gmail.com>
  2007-01-21 17:38 ` Re: Upgrading to 8.2, changes in user/group management scripts Jim C. Nasby <jim@nasby.net>
  2007-01-22 20:36   ` Re: Upgrading to 8.2, changes in user/group management scripts Peter Koczan <pjkoczan@gmail.com>
@ 2007-01-22 22:35     ` Alvaro Herrera <alvherre@commandprompt.com>
  1 sibling, 0 replies; 5+ messages in thread

From: Alvaro Herrera @ 2007-01-22 22:35 UTC (permalink / raw)
  To: Peter Koczan <pjkoczan@gmail.com>; +Cc: Jim C. Nasby <jim@nasby.net>; pgsql-admin

Peter Koczan wrote:
> 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.

Instead of deleting stuff from pg_authid directly, use DROP ROLE.
If this doesn't work because the role owns something, you may use a
combination of DROP OWNED and REASSIGN OWNED.

-- 
Alvaro Herrera                                http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support



^ permalink  raw  reply  [nested|flat] 5+ messages in thread


end of thread, other threads:[~2007-01-22 22:35 UTC | newest]

Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-01-19 21:45 Upgrading to 8.2, changes in user/group management scripts Peter Koczan <pjkoczan@gmail.com>
2007-01-21 17:38 ` Jim C. Nasby <jim@nasby.net>
2007-01-22 20:36   ` Peter Koczan <pjkoczan@gmail.com>
2007-01-22 22:09     ` Stephen Frost <sfrost@snowman.net>
2007-01-22 22:35     ` Alvaro Herrera <alvherre@commandprompt.com>

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