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@xxxxxxxxxx
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
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
