dangling permission on tables after drop user.

Vivek Khera <khera@kcilink.com>

From: Vivek Khera <khera@kcilink.com>
To: pgsql-general@postgresql.org
Date: 2004-09-29T20:24:11Z
Lists: pgsql-general
I have a database which started on Pg 7.1, moved to 7.2 via 
pg_dump/restore, and ultimately to Pg 7.4 likewise.

While it was in 7.2, I added one user and granted access to various 
tables.  After the 7.4 migration, that user was no longer needed, so 
was removed via "dropuser" command line tool.

Now, when I pg_dump that db using the version 7.4.5 tools, I cannot 
restore because there are still grants in there for this phantom user:

REVOKE ALL ON TABLE partners FROM PUBLIC;
GRANT INSERT,SELECT,UPDATE,DELETE ON TABLE partners TO www;
GRANT ALL ON TABLE partners TO "102";

there is no user with ID 102 in the pg_user view.  pg_restore complains 
about the missing user "102".  And no, the user was not "102" it was 
the name of a (former) employee.

My questions are:

1) did I do something wrong in dropping that user?
2) how do I fix this in my system tables?

The gross hack is to pg_restore to an ascii file and delete those GRANT 
lines, but the compressed dump is over 2Gb for this database.


Vivek Khera, Ph.D.
+1-301-869-4449 x806