X-Original-To: pgsql-hackers-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 2DF8353EF4 for ; Sun, 15 May 2005 18:42:56 -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 92041-08 for ; Sun, 15 May 2005 21:42:51 +0000 (GMT) Received: from mr1.surnet.cl (smtp1.surnet.cl [216.155.73.162]) by svr1.postgresql.org (Postfix) with ESMTP id 5038453EEE for ; Sun, 15 May 2005 18:42:47 -0300 (ADT) Received: from smtp1.surnet.cl (216.155.73.168) by mr1.surnet.cl (7.0.031.3) id 42587EDE007784E9; Sun, 15 May 2005 17:42:19 -0400 Received: from smtp2.surnet.cl (smtp2.surnet.cl []) by mr1.surnet.cl ([216.155.73.168]); Sun, 15 May 2005 21:42:18 +0000 Received: from cluster.surnet.cl (216.155.73.164) by smtp2.surnet.cl (7.0.031.3) id 4259ADE30049B2B2; Sun, 15 May 2005 17:43:23 -0400 Received: from localhost (200.85.219.173) by cluster.surnet.cl (7.0.024) id 4276578F001D84DF; Sun, 15 May 2005 17:42:47 -0400 Received: by localhost (Postfix, from userid 1000) id 81610C32DDE; Sun, 15 May 2005 17:42:53 -0400 (CLT) Date: Sun, 15 May 2005 17:42:53 -0400 From: Alvaro Herrera To: Tom Lane Cc: Hackers Subject: Re: [ADMIN] Permissions not removed when group dropped Message-ID: <20050515214253.GA8391@surnet.cl> References: <20050513213505.GB17830@surnet.cl> <172.1116038893@sss.pgh.pa.us> <20050514053053.GA26186@surnet.cl> <3868.1116084743@sss.pgh.pa.us> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <3868.1116084743@sss.pgh.pa.us> User-Agent: Mutt/1.5.9i X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=2.261 tagged_above=0 required=5 tests=AWL, DNS_FROM_RFC_ABUSE, DNS_FROM_RFC_POST, FORGED_RCVD_HELO, RCVD_IN_NJABL_PROXY X-Spam-Level: ** X-Archive-Number: 200505/1032 X-Sequence-Number: 67908 [Moved to -hackers] On Sat, May 14, 2005 at 11:32:23AM -0400, Tom Lane wrote: > So what we've got [for DROP USER] 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. Sounds reasonable. Additionally we need to think what should happen if the user is the grantor of some privilege. I think we should warn in RESTRICT mode, and in CASCADE, revoke the privilege from the grantee. > 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. Hmm. We could implement something like "DROP USER LOCALLY [CASCADE | RESTRICT]", which would be a very misleading name for operations 2-4 above. Additionally, if the user doesn't have references in other databases, drop the user itself. (Note it's inconsistent.) For DROP TABLESPACE, I think we should also provide some sort of DROP LOCALLY, that drops all objects in the current database. (Suggestions for the exact spelling of the option are welcome.) -- Alvaro Herrera () "La gente vulgar solo piensa en pasar el tiempo; el que tiene talento, en aprovecharlo"