Re: Clearing old user ids completely

Erik Jones wrote:
> 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.

Well, yes, that's the way I normally change the user of a table. I
usually only mess with pg_class if I want to do a mass change on the
owners of the table without having to mess with building a table list
separately and creating the individual ALTER TABLE ... OWNER commands.
My main trouble is just trying to completely get rid of the faulty
permissions assigned to the table without having to leave the previous
owner account sitting in the system.

Justin Pasher