BUG #1051: Cannot remove groups

The following bug has been logged online:

Bug reference:      1051
Logged by:          Jari Aalto

Email address:      jari.aalto@poboxes.com

PostgreSQL version: 7.4

Operating system:   W2k SP4 / Cygwin

Description:        Cannot remove groups

Details:

It is impossible to give details how this error
is triggered, because I do not know enough details
of PG's internals. I happened to crete databases, user, groups and then
deleting them in random order and now the groups are totally inaccessible in
all ways.


The problem is that the admin files say that I have
groups. But they cannot be removed. And because
they cannot be removed or recreated, I cannot
grant anything to groups.

I tried to install all from  fresh with:

root@w2kpicasso:/usr/share/postgresql# rm -rf data/
   root@w2kpicasso:/usr/share/postgresql# LC_ALL=C initdb -D
/usr/share/postgresql/data



But that dind't affect the groups or users. This was verified by running
select from pg_group and pg_users immedately after total recreation.

See logs below. Please let me know how to preceed
and help to find more details for you.

Jari

----------------------------------------------------

root@w2kpicasso:/usr/share/postgresql# rm -rf data/
   root@w2kpicasso:/usr/share/postgresql# LC_ALL=C initdb -D
/usr/share/postgresql/
data
The files belonging to this database system will be owned by user "root".
This user must also own the server process.

The database cluster will be initialized with locale C.

creating directory /usr/share/postgresql/data... ok
creating directory /usr/share/postgresql/data/base... ok
creating directory /usr/share/postgresql/data/global... ok
creating directory /usr/share/postgresql/data/pg_xlog... ok
creating directory /usr/share/postgresql/data/pg_clog... ok
selecting default max_connections... 100
selecting default shared_buffers... 1000
creating configuration files... ok
creating template1 database in /usr/share/postgresql/data/base/1... ok
initializing pg_shadow... ok
enabling unlimited row size for system tables... ok
initializing pg_depend... ok
creating system views... ok
loading pg_description... ok
creating conversions... ok
setting privileges on built-in objects... ok
creating information schema... ok
vacuuming database template1... ok
copying template1 to template0... ok

Success. You can now start the database server using:

    /bin/postmaster -D /usr/share/postgresql/data
or
    /bin/pg_ctl -D /usr/share/postgresql/data -l logfile start

root@w2kpicasso:/usr/share/postgresql# psql -d template1
   Welcome to psql 7.4, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help on internal slash commands
       \g or terminate with semicolon to execute query
       \q to quit

template1=# SELECT  groname
template1-#         , grosysid
template1-#         , grolist       AS "Users in group"
template1-# FROM    pg_group
template1-# ;
 groname | grosysid | Users in group
---------+----------+----------------
 admin   |      112 |
 root    |      113 |
 reader  |      110 | {106}
 users   |      111 | {107,108}
(4 rows)

template1=# SELECT  usename
template1-#         , usesysid
template1-#         , usecreatedb   AS "create db"
template1-#         , usesuper      AS "root"
template1-#         , usecatupd     AS "sys catalogs"
template1-# FROM    pg_user
template1-# ;
 usename | usesysid | create db | root | sys catalogs
---------+----------+-----------+------+--------------
 root    |        1 | t         | t    | t
 scott   |      106 | f         | f    | f
 test    |      107 | f         | f    | f
 king    |      108 | f         | f    | f
(4 rows)


template1=# drop group admin;
ERROR:  group "admin" does not exist
template1=# drop group root;
ERROR:  group "root" does not exist
template1=# drop group reader;
ERROR:  group "reader" does not exist
template1=# drop group users;
ERROR:  group "users" does not exist
template1=#


root@w2kpicasso:/usr/share/postgresql# ls -la
total 22134
drwxr-xr-x    5 root     Administ        0 Jan 17  2002 .
drwxr-xr-x   67 root     Administ        0 Feb 10  2001 ..
drwxr-xr-x    2 root     Administ        0 Nov 26 22:49 contrib
-rw-r--r--    1 root     Administ    38974 Nov 19 18:21
conversion_create.sql
drwxr-xr-x    6 root     Administ        0 Jan 16 20:22 data
-rw-r--r--    1 root     Administ    65566 Nov 19 18:21
information_schema.sql
drwxr-xr-x    2 root     Administ        0 Nov 26 22:49 java
-rw-r--r--    1 root     Administ        0 May 18  2003
list.emacs-bbdb.spool
-rw-r--r--    1 root     Administ  7012794 May 18  2003
list.emacs-devel.spool
-rw-r--r--    1 root     Administ  7168836 May 18  2003
list.emacs-ding.spool
-rw-r--r--    1 root     Administ        0 May 18  2003 list.emacs-jde.spool
-rw-r--r--    1 root     Administ        0 May 18  2003 list.emacs-nt.spool
-rw-r--r--    1 root     Administ        0 May 18  2003
list.emacs.tt-users.spool
-rw-r--r--    1 root     Administ        0 May 18  2003 list.xemacs-nt.spool
-rw-r--r--    1 root     Administ  8046174 May 18  2003 list.xemacs.spool
lrwxrwxrwx    1 root     Administ      148 Oct 16 21:44 pg_hba.conf ->
/etc/postgres/pg_hba.conf
-rw-r--r--    1 root     Administ     3288 Nov 19 18:21 pg_hba.conf.sample
-rw-r--r--    1 root     Administ     1441 Nov 19 18:21 pg_ident.conf.sample
-rw-r--r--    1 root     Administ      606 Nov 19 18:22
pg_service.conf.sample
-rw-r--r--    1 root     Administ   242292 Nov 19 18:21 postgres.bki
-rw-r--r--    1 root     Administ    51982 Nov 19 18:21 postgres.description
-rw-r--r--    1 root     Administ     7787 Nov 19 18:21
postgresql.conf.sample
-rw-r--r--    1 root     Administ    19020 Nov 19 18:21 sql_features.txt