Clearing old user ids completely

From: Justin Pasher <justinp(at)newmediagateway(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Clearing old user ids completely
Date: 2008-01-15 21:59:16
Message-ID: 478D2CB4.60108@newmediagateway.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

PostgreSQL 7.4.17

My situation is basically like the one states in the archives:

http://archives.postgresql.org/pgsql-sql/2005-10/msg00165.php

We have some tables that used to be owned by a user (user id 117) that
no longer exists. Because the user no longer exists, when the database
is dumped via pg_dump, it spits out warnings about an invalid owner. The
reason behind all of this is completely understandable (kind of like a
dangling symlink), and the solution in the archive to get a usable dump
is to recreate the user with the missing ID, then Postgres will no
longer complain.

My question is if there is any way to truly delete the previous user and
fix any associated permissions that may be dangling around. I've noticed
it's possible to update the pg_class table's relowner column to alter
the owner of a table (not sure if that's really safe, though). However,
the relacl column is of type "aclitem[]", so you can't update it in the
same way. Newer versions of Postgres (8.1) will completely prevent you
from deleting the user if anything is still linked to it, but I'm
confused exactly how to get this older permission information cleared out.

Thanks.

--
Justin Pasher

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Erik Jones 2008-01-15 22:18:11 Re: Clearing old user ids completely
Previous Message David Fetter 2008-01-15 20:58:33 Re: Online Oracle to Postgresql data migration