The following bug has been logged online:
Bug reference: 1203
Logged by: Geoff Kloess
Email address: geoffk@garden-grove.org
PostgreSQL version: 7.4
Operating system: Redhat ES3
Description: Dropping a user does not revoke their permissions
Details:
Maybe this isn't a bug, because I find it hard to believe that it hasn't
been noticed already, but I didn't find anything in the documentation about
this.
Dropping a user does not automatically revoke their permissions on tables,
instead it changes the permission from their name to their system id number
when displayed using "\z".
It seems weird that the database will keep around permissions for
non-existent users. And it causes an error when doing a pg_restore.
It is also impossible to revoke permissions when the user attached to them
does not exist, which means you have to create a temporary user with the
same sysid as the old user, revoke the permissions and then drop the
temporary user.
It seems like it would make a lot more sense to just revoke the permissions
at the same time the user is dropped.