Re: Clearing old user ids completely

On Jan 15, 2008, at 3:59 PM, Justin Pasher wrote:

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

Well, you could try, as a superuser, changing the ownership of all of
those tables to an existing user and you can do that via ALTER TABLE
without having to edit pg_class directly.

Erik Jones

DBA | Emma®
erik@myemma.com
800.595.4401 or 615.292.5888
615.292.0777 (fax)

Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com