Re: Clearing old user ids completely - Mailing list pgsql-general

On Jan 15, 2008, at 4:53 PM, Justin Pasher wrote:

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

You can build and EXECUTE the ALTER TABLE commands in a function of a
few lines.  With regards to removing the faulty permissions, will
REVOKE not work if the user doesn't exist in the system anymore (I
honestly don't know much about pre-8.0 behaviours)?  If not take a
look at the aclitem functions in the pg_catalog schema (in psql: \df
*acl*), they may be what you're looking for.

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