Crash and troubles with acl's - Mailing list pgsql-hackers

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 -------+