X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id F03B45320C for ; Sat, 14 May 2005 12:32:34 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 97853-08 for ; Sat, 14 May 2005 15:32:26 +0000 (GMT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id EC3EB52C7A for ; Sat, 14 May 2005 12:32:25 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id j4EFWNdf003869; Sat, 14 May 2005 11:32:23 -0400 (EDT) To: Alvaro Herrera Cc: "Harris, Richard" , pgsql-admin@postgresql.org Subject: Re: Permissions not removed when group dropped In-reply-to: <20050514053053.GA26186@surnet.cl> References: <20050513213505.GB17830@surnet.cl> <172.1116038893@sss.pgh.pa.us> <20050514053053.GA26186@surnet.cl> Comments: In-reply-to Alvaro Herrera message dated "Sat, 14 May 2005 01:30:53 -0400" Date: Sat, 14 May 2005 11:32:23 -0400 Message-ID: <3868.1116084743@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.006 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200505/199 X-Sequence-Number: 17628 Alvaro Herrera writes: > No, that's why I haven't mentioned it :-) I know I can't do anything > about objects in other databases. But I have wondered if we could > implement CASCADE behavior for an object whose dependencies are only > local to the current database (where CASCADE actually mean delete > references in ACLs, and reject the action altogether if there is an > ownership reference. And of course, if the object is a tablespace, > reject as well.) Well, I think ACL references could be auto-dropped (ie, even in RESTRICT mode); and group memberships too. It seems to me that those do not amount to independent objects that need protection. What I'd like CASCADE to mean is "drop owned objects". I agree it makes sense to restrict the owned database and owned tablespace cases even in CASCADE mode, although that's more of a judgement call than a necessary conclusion. So what we've got is: 1. Reject if any references to user from within other databases (implementation restriction). 2. Reject if user owns any databases or tablespaces (safety feature). 3. If CASCADE mode, drop any owned objects within the current database; if RESTRICT mode, reject if there are owned objects within the current database. 4. Auto-drop any remaining references (ACLs and group memberships). 5. Drop the user itself. The main problem I see with this is that if you do have a user you want to get rid of who owns objects in multiple databases, it's still mighty hard to do it. It'd be nice to have some kind of command that either drops or reassigns ownership of everything the user has in the current database. Then you could use that repeatedly until you'd reached a point where DROP USER would work. regards, tom lane