Thread: 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
"PostgreSQL Bugs List" <pgsql-bugs@postgresql.org> writes:
> I tried to install all from fresh with:
> ...
> 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.
Your trace shows no sign of having stopped the postmaster before
re-initdb'ing or starting it again afterwards. I think you've got some
unholy combination of old and new tables inside the postmaster. It'd be
a good idea to try again with those steps included. (I'm not sure just
how Windows handles deletion of directories that are still in use, but
on Unix a pg_ctl stop would no longer suffice to get rid of the old
postmaster, because after the rm -rf you can no longer get to the
directory in which the old postmaster is running. You might be best
advised to reboot.)
regards, tom lane
[patch] INSTALL doc note (was Re: BUG #1051: Cannot remove groups)
From
jari.aalto@poboxes.com (Jari Aalto)
Date:
"PostgreSQL Bugs List" <pgsql-bugs@postgresql.org> writes:
> I tried to install all from fresh with:
> ...
> 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.
Tom Lane:
Your trace shows no sign of having stopped the postmaster before
re-initdb'ing or starting it again afterwards. I think you've got some
unholy combination of old and new tables inside the postmaster. It'd be
a good idea to try again with those steps included. (I'm not sure just
how Windows handles deletion of directories that are still in use, but
on Unix a pg_ctl stop would no longer suffice to get rid of the old
postmaster, because after the rm -rf you can no longer get to the
directory in which the old postmaster is running. You might be best
advised to reboot.)
Thank you. That did it. May I suggest adding this:
--- INSTALL.old 2004-01-17 13:34:22.000000000 +0200
+++ INSTALL 2004-01-17 13:33:32.000000000 +0200
@@ -15,6 +15,7 @@
mkdir /usr/local/pgsql/data
chown postgres /usr/local/pgsql/data
su - postgres
+ <kill current postmaster. This clears groups and users>
LC_ALL=C /usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data
/usr/local/pgsql/bin/postmaster -D /usr/local/pgsql/data >logfile 2>&1 &
/usr/local/pgsql/bin/createdb test
Jari Aalto wrote: > Thank you. That did it. May I suggest adding this: > > > --- INSTALL.old 2004-01-17 13:34:22.000000000 +0200 > +++ INSTALL 2004-01-17 13:33:32.000000000 +0200 > @@ -15,6 +15,7 @@ > mkdir /usr/local/pgsql/data > chown postgres /usr/local/pgsql/data > su - postgres > + <kill current postmaster. This clears groups and users> > LC_ALL=C /usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data > /usr/local/pgsql/bin/postmaster -D /usr/local/pgsql/data >logfile > 2>&1 & /usr/local/pgsql/bin/createdb test This is plain wrong. Killing the postmaster doesn't clear anything.