Example:

% sql
Welcome to the POSTGRESQL interactive sql monitor:
  Please read the file COPYRIGHT for copyright terms of POSTGRESQL
[PostgreSQL 6.5.0 on i586-pc-linux-gnu, compiled by gcc egcs-2.91.66]

   type \? for help on slash commands
   type \q to quit
   type \g or terminate with semicolon to execute query
 You are currently connected to the database: david

david=> create user sss;
CREATE USER
david=> select * from pg_shadow;
usename |usesysid|usecreatedb|usetrace|usesuper|usecatupd|passwd|valuntil                    
--------+--------+-----------+--------+--------+---------+------+----------------------------
postgres|     502|t          |t       |t       |t        |      |Sat Jan 31 07:00:00 2037 CET
david   |     501|t          |t       |t       |t        |      |                            
sss     |     503|f          |t       |f       |t        |      |                            
(3 rows)

david=> create table test ( i int );
CREATE
david=> grant all on test to sss;
CHANGE
david=> \z test
Database    = david
 +----------+--------------------------+
 | Relation | Grant/Revoke Permissions |
 +----------+--------------------------+
 | test     | {"=","sss=arwR"}         |
 +----------+--------------------------+
david=> drop user sss; 
DROP USER
david=> \z test
Database    = david
 +----------+--------------------------+
 | Relation | Grant/Revoke Permissions |
 +----------+--------------------------+
 | test     | {"=","503=arwR"}         |
 +----------+--------------------------+


All rights for user 'sss' remains there (but now identified by
id=503). I'am not sure, if this is error, but it is dangerous.
 ('createuser' with id=503 will grant all rights to new user)

                                        David

-- 
* David Sauer, student of Czech Technical University
* electronic mail: davids@orfinet.cz (mime compatible)

20 May 1999 11:30:34 +0200
David Sauer <davids@orfinet.cz>
drop user doesn't remove rights from tables ...
> david=> create user sss;
> CREATE USER
> david=> select * from pg_shadow;
> usename |usesysid|usecreatedb|usetrace|usesuper|usecatupd|passwd|valuntil                    
> --------+--------+-----------+--------+--------+---------+------+----------------------------
> postgres|     502|t          |t       |t       |t        |      |Sat Jan 31 07:00:00 2037 CET
> david   |     501|t          |t       |t       |t        |      |                            
> sss     |     503|f          |t       |f       |t        |      |                            
> (3 rows)
> 
> david=> create table test ( i int );
> CREATE
> david=> grant all on test to sss;
> CHANGE
> david=> \z test
> Database    = david
>  +----------+--------------------------+
>  | Relation | Grant/Revoke Permissions |
>  +----------+--------------------------+
>  | test     | {"=","sss=arwR"}         |
>  +----------+--------------------------+
> david=> drop user sss; 
> DROP USER
> david=> \z test
> Database    = david
>  +----------+--------------------------+
>  | Relation | Grant/Revoke Permissions |
>  +----------+--------------------------+
>  | test     | {"=","503=arwR"}         |
>  +----------+--------------------------+
> 
> 
> All rights for user 'sss' remains there (but now identified by
> id=503). I'am not sure, if this is error, but it is dangerous.
>  ('createuser' with id=503 will grant all rights to new user)

This has been pointed out before.  Not sure how to deal with it.


-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Wed, 7 Jul 1999 14:32:17 -0400 (EDT)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] drop user doesn't remove rights from tables ...