Postgre7.0.2 drop user bug - Mailing list pgsql-hackers

> I think we have found a bug in postgresql 7.0.2.  If I'm right then a fix
> for this should probably be added to 7.0.3 also.  Anyway without further
> adieu:
> 
> I have attached detail of my session at the end of this email, but the
> summary is as follows.
> 
> If I run the following commands my pg_group file will be corrupted and up
> to 2Gig in size. (FYI - I am starting this after a fresh initdb, and the
> pg_group file
> starts at 0 bytes)
> 
> create user foo1;
> create user foo2;
> create user foo3;
> create group test1;
> alter group test1 add user foo1,foo2,foo3;
>     (pg_group is 8192 bytes)
> drop user foo1,foo2;
>     (now my pg_group file is 24567 bytes)
> drop user foo3;
> 
> (now my psql is hanging, control-c does nothing to kill the query.  Also
> my pg_group file is now growing rapidly till it fills the disk or I kill
> -9 postmaster, which ever comes first.  Not good!)
> 
> I think the problem is with the drop user command (obviously).  After the
> alter group command all three user ids are in the pg_group table.  After I
> drop two of the users, both users are gone from the pg_user table, but the
> second user is still in pg_group.  So we now have a reference to a user
> that does not exist.  At this point I think the system is in trouble, and
> the last drop user command just seals the coffin.  If I do the same script
> but drop the users one at a time, everything is fine.  So apparently while
> the drop user command correctly drops all users from pg_user, it only
> checks pg_group for the first user in the list.
> 
> We found this problem on one of our soon to be production database
> servers, fortunately prior to install.  The only way to get the server
> back
> in to a fully functional state was to perform an initdb (I stopped
> postgre, then deleted the data directory then restarted using
> /etc/init.d/postgresql start  which performs initdb)
> 
> Anyway, any comments?  Can anyone else repeat this? I hope this is easy to
> fix.  I guess the quick fix is to disallow multiple users to be specified 
> in the drop user command.
> 
> Thanks much,
> 
> Matt O'Connor
> 
> 
> p.s..  as promised here is some detail from my session.
> I have tested this on both RH7 with pg7.0.2 as supplied by RH, and with
> RH6.2 with 7.0.2 rpms from Lamar.
> 
> 
> Red Hat Linux release 7.0 (Guinness)
> select version();
>                            version
> -------------------------------------------------------------
>  PostgreSQL 7.0.2 on i686-pc-linux-gnu, compiled by gcc 2.96
> (1 row)
> 
> ls -l ./data/pg_group
> -rw-------    1 postgres postgres        0 Oct 17 20:26 ./data/pg_group
> create user foo1;
> CREATE USER
> create user foo2;
> CREATE USER
> create user foo3;
> CREATE USER
> select * from pg_user;
>  usename  | usesysid | usecreatedb | usetrace | usesuper | usecatupd |
> passwd  | valuntil
> ----------+----------+-------------+----------+----------+-----------+----
> ------+----------
>  postgres |       26 | t           | t        | t        | t         |
> ******** |
>  foo1     |       27 | f           | f        | f        | f         |
> ******** |
>  foo2     |       28 | f           | f        | f        | f         |
> ******** |
>  foo3     |       29 | f           | f        | f        | f         |
> ******** |
> (4 rows)
> 
> create group test1;
> CREATE GROUP
> select * from pg_group;
>  groname | grosysid | grolist
> ---------+----------+---------
>  test1   |        1 |
> (1 row)
>  
> alter group test1 add user foo1,foo2,foo3;
> ALTER GROUP
> select * from pg_user;
>  usename  | usesysid | usecreatedb | usetrace | usesuper | usecatupd |
> passwd  | valuntil
> ----------+----------+-------------+----------+----------+-----------+----
> ------+----------
>  postgres |       26 | t           | t        | t        | t         |
> ******** |
>  foo1     |       27 | f           | f        | f        | f         |
> ******** |
>  foo2     |       28 | f           | f        | f        | f         |
> ******** |
>  foo3     |       29 | f           | f        | f        | f         |
> ******** |
> (4 rows)
>  
> select * from pg_group;
>  groname | grosysid |  grolist
> ---------+----------+------------
>  test1   |        1 | {27,28,29}
> (1 row)
>  
> ls -l ./data/pg_group
> -rw-------    1 postgres postgres     8192 Oct 17 20:27 ./data/pg_group
> drop user foo1,foo2;
> DROP USER
> ls -l ./data/pg_group
> -rw-------    1 postgres postgres    24576 Oct 17 20:27 ./data/pg_group
> select * from pg_user;
>  usename  | usesysid | usecreatedb | usetrace | usesuper | usecatupd |
> passwd  | valuntil
> ----------+----------+-------------+----------+----------+-----------+----
> ------+----------
>  postgres |       26 | t           | t        | t        | t         |
> ******** |
>  foo3     |       29 | f           | f        | f        | f         |
> ******** |
> (2 rows)
>  
> select * from pg_group;
>  groname | grosysid | grolist
> ---------+----------+---------
>  test1   |        1 | {29,27}
> (1 row)
>  
> drop user foo3;
> Cancel request sent
> Terminated
> bash-2.04$  ls -l ./data/pg_group
-rw-------    1 postgres postgres 438386688 Oct 17 20:27 ./data/pg_group
bash-2.04$

(as you can see, psql was not responding after the last drop user, I hit
control-c and is said Cancel request sent, but psql never returned to a
prompt, so I had to kill -9 the postmaster.