Hi,
Here is my test, I think the privilege should be revoked automatically.
Otherwise, it is a dangerous thing:
bash-2.05b$ createdb dod
CREATE DATABASE
bash-2.05b$ psql dod
Welcome to psql 8.0.3, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit
dod=# create user secretary_dod;
CREATE USER
dod=# create table terror (c1 int);
CREATE TABLE
dod=# grant select on terror to secretary_dod;
GRANT
dod=# \z terror
Access privileges for database "dod"
Schema | Name | Type | Access privileges
--------+--------+-------+------------------------------------------------------
public | terror | table | {postgres=arwdRxt/postgres,secretary_dod=r/postgres}
(1 row)
dod=# drop user secretary_dod;
DROP USER
dod=# \z terror
Access privileges for database "dod"
Schema | Name | Type | Access privileges
--------+--------+-------+--------------------------------------------
public | terror | table | {postgres=arwdRxt/postgres,103=r/postgres}
(1 row)
dod=# create user a_staff SYSID 103;
CREATE USER
dod=# \c - a_staff
You are now connected as new user "a_staff".
dod=> select * from terror;
c1
----
(0 rows)
*** WOW!, A_staff can what the Secretary of Dept of Defense could see ***