Received: from localhost (maia-2.hub.org [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id 8D63B9FB222 for ; Sun, 14 Jan 2007 16:12:04 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.187]) (amavisd-new, port 10024) with ESMTP id 11659-08 for ; Sun, 14 Jan 2007 16:11:44 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id 562489FB26E for ; Sun, 14 Jan 2007 16:11:58 -0400 (AST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.6/8.13.6) with ESMTP id l0EKBtRG012934; Sun, 14 Jan 2007 15:11:55 -0500 (EST) To: Sven Sporer cc: pgsql-admin@postgresql.org Subject: Re: User Management, drop users In-reply-to: <6A49A3F3-CF38-46F4-B994-044A75CDBE70@gmx.net> References: <6A49A3F3-CF38-46F4-B994-044A75CDBE70@gmx.net> Comments: In-reply-to Sven Sporer message dated "Sun, 14 Jan 2007 21:00:19 +0100" Date: Sun, 14 Jan 2007 15:11:55 -0500 Message-ID: <12933.1168805515@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200701/114 X-Sequence-Number: 24270 Sven Sporer writes: > 2) How do you restrict the users of a specific database to touch only > the objects in their database? They *are* so restricted. Your problem is that roles are installation-wide, not local to a particular database, and so you cannot enforce the restriction you hope for: CREATEROLE privilege is inherently to do with installation-wide actions. A possible workaround is to ensure that every user owns at least one object in "his" database -- table, function, whatever. Then it's not possible to drop the user without having first gotten into that database. (As of 8.1 or so --- this wasn't enforced before that.) However a user with CREATEROLE can still cause all sorts of mischief with ALTER USER, so preventing only the DROP case probably isn't that helpful. Bottom line is you don't give out that privilege to people you don't trust. If you need better separation among groups of users, give them each their own installation (postmaster). regards, tom lane