BUG #1051: Cannot remove groups

The following bug has been logged online:

Bug reference: 1051
Logged by: Jari Aalto

Email address: jari(dot)aalto(at)poboxes(dot)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(at)w2kpicasso:/usr/share/postgresql# rm -rf data/
root(at)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(at)w2kpicasso:/usr/share/postgresql# rm -rf data/
root(at)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(at)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(at)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