Hi Hackers,
(yes I,m here --- I "unsubscribed" only on the list, not on the
majorodomo...) 

I've found a very ugly bug in V6.1, V6.2 and (without testing) I think
it's still there in V6.3.

To reproduce it use following script:
====================================================================
-- SQL script: acl_crash.sql 
-- Call: psql -d template1 -f acl_crash.sql
--
-- SQL script to reproduce the situation where PostgreSQL crashes when the 
-- pg_class is accesed which has an relacl entry, but the corresponding
-- user has been deleted.
--
-- drop datebase testdb;

create database testdb;
\connect testdb
create table testtab (i4 int4, c20 char(20));
insert into pg_user (usename,usesysid,usecreatedb,usetrace,usesuper,usecatupd)
	values ('user1',990,'t','t','t','t');
insert into pg_user (usename,usesysid,usecreatedb,usetrace,usesuper,usecatupd)
	values ('user2',991,'t','t','t','t');

-- Check pg_class - all OK!
select relname,relacl from pg_class where relname not like 'pg_%';
-- now we create some acl-item entries...
grant insert,select,update on testtab to user1;
grant insert,select,update on testtab to user2;
revoke select,update on testtab from user2;

-- Check pg_class - still everything OK!
select relname,relacl from pg_class where relname not like 'pg_%';

-- Delete a user which exists in a relacl-list
delete from pg_user where usename='user2';

-- Check pg_class - HERE we crash - with a corrupted pg_class???
select relname,relacl from pg_class where relname not like 'pg_%';
===================================================================
The situation is fatal from the following point of view:

1. The server side works still after this situation. Maybe the user finds
   the error just after days or weeks of work.

2. There is NO way to access the pg_class without a core dump. Thus it is
   immpossible to 'dump' out the table because the pg_dump reads the
   pg_class always. It seems to be (from the users sight) a currupted
   pg_class table - which can't be repaired. 

I saved my live with a patched pg_dump version (which does not touch the  
relacl values) dumped the database out and created a new database 
... and crashed a little later of course - because I didn't know the
reason for the creash - once ago.
--------------------------------

Of course there is a workaround -- IF the problem is known ;-):
Do a
                 revoke all on <table> from <user>
for any table which has an acl-item entry (maybe it's simpler to call it
for any table).  This removes the acl-item from the table entry in
'pg_class' and the user can be deleted from the 'pg_user' table, without
troubles.

Best regards,
	Gerhard

PS: I will stay on the list - I never thought that someone remembers my
    name :-)
 
+-----------------+             +--- gerhardr@tech-edv.co.at ---+
| Technische EDV   \ Reithofer / Technical Sofware Developement |
| A-2136 Laa/Thaya  \ Gerhard / Tel +43-2522/8726 +-------------+
| Staatsbahnstr. 100 +-------+ Fax +43-2522/87268 |
+----- http://members.aon.at/tech-edv/Info -------+


Wed, 18 Feb 1998 00:31:31 +0100 (MET)
Gerhard Reithofer <gerhardr@tech-edv.co.at>
Crash and troubles with acl's
Please try 6.3.  I think this was addressed.

> 
> Hi Hackers,
> (yes I,m here --- I "unsubscribed" only on the list, not on the
> majorodomo...) 
> 
> I've found a very ugly bug in V6.1, V6.2 and (without testing) I think
> it's still there in V6.3.
> 
> To reproduce it use following script:
> ====================================================================
> -- SQL script: acl_crash.sql 
> -- Call: psql -d template1 -f acl_crash.sql
> --
> -- SQL script to reproduce the situation where PostgreSQL crashes when the 
> -- pg_class is accesed which has an relacl entry, but the corresponding
> -- user has been deleted.
> --
> -- drop datebase testdb;
> 
> create database testdb;
> \connect testdb
> create table testtab (i4 int4, c20 char(20));
> insert into pg_user (usename,usesysid,usecreatedb,usetrace,usesuper,usecatupd)
> 	values ('user1',990,'t','t','t','t');
> insert into pg_user (usename,usesysid,usecreatedb,usetrace,usesuper,usecatupd)
> 	values ('user2',991,'t','t','t','t');
> 
> -- Check pg_class - all OK!
> select relname,relacl from pg_class where relname not like 'pg_%';
> -- now we create some acl-item entries...
> grant insert,select,update on testtab to user1;
> grant insert,select,update on testtab to user2;
> revoke select,update on testtab from user2;
> 
> -- Check pg_class - still everything OK!
> select relname,relacl from pg_class where relname not like 'pg_%';
> 
> -- Delete a user which exists in a relacl-list
> delete from pg_user where usename='user2';
> 
> -- Check pg_class - HERE we crash - with a corrupted pg_class???
> select relname,relacl from pg_class where relname not like 'pg_%';
> ===================================================================
> The situation is fatal from the following point of view:
> 
> 1. The server side works still after this situation. Maybe the user finds
>    the error just after days or weeks of work.
> 
> 2. There is NO way to access the pg_class without a core dump. Thus it is
>    immpossible to 'dump' out the table because the pg_dump reads the
>    pg_class always. It seems to be (from the users sight) a currupted
>    pg_class table - which can't be repaired. 
> 
> I saved my live with a patched pg_dump version (which does not touch the  
> relacl values) dumped the database out and created a new database 
> ... and crashed a little later of course - because I didn't know the
> reason for the creash - once ago.
> --------------------------------
> 
> Of course there is a workaround -- IF the problem is known ;-):
> Do a
>                  revoke all on <table> from <user>
> for any table which has an acl-item entry (maybe it's simpler to call it
> for any table).  This removes the acl-item from the table entry in
> 'pg_class' and the user can be deleted from the 'pg_user' table, without
> troubles.
> 
> Best regards,
> 	Gerhard
> 
> PS: I will stay on the list - I never thought that someone remembers my
>     name :-)
>  
> +-----------------+             +--- gerhardr@tech-edv.co.at ---+
> | Technische EDV   \ Reithofer / Technical Sofware Developement |
> | A-2136 Laa/Thaya  \ Gerhard / Tel +43-2522/8726 +-------------+
> | Staatsbahnstr. 100 +-------+ Fax +43-2522/87268 |
> +----- http://members.aon.at/tech-edv/Info -------+
> 
> 
> 


-- 
Bruce Momjian
maillist@candle.pha.pa.us

Tue, 17 Feb 1998 19:24:10 -0500 (EST)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] Crash and troubles with acl's