Received: from localhost (maia-5.hub.org [200.46.204.182]) by postgresql.org (Postfix) with ESMTP id D78D99FA5CF for ; Thu, 16 Aug 2007 13:45:08 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.182]) (amavisd-maia, port 10024) with ESMTP id 28696-01 for ; Thu, 16 Aug 2007 13:45:03 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id C42589FA61B for ; Thu, 16 Aug 2007 13:45:03 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.1/8.14.1) with ESMTP id l7GGhluR005954; Thu, 16 Aug 2007 12:43:47 -0400 (EDT) To: "=?UTF-8?Q?Rodrigo_De_Le=C3=B3n?=" cc: Joshua_Kramer , pgsql-sql@postgresql.org Subject: Re: SUPERUSER vs CREATEUSER causes foul 'code smell' In-reply-to: References: Comments: In-reply-to "=?UTF-8?Q?Rodrigo_De_Le=C3=B3n?=" message dated "Thu, 16 Aug 2007 10:41:00 -0500" Date: Thu, 16 Aug 2007 12:43:47 -0400 Message-ID: <5953.1187282627@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200708/146 X-Sequence-Number: 29084 "=?UTF-8?Q?Rodrigo_De_Le=C3=B3n?=" writes: > On 8/16/07, Joshua_Kramer wrote: >> If I want to create a role who can create other roles, but not have other >> SUPERUSER priveleges - how can I do that? > create role foo createrole login password 'foo'; CREATEROLE privilege is not exactly a superuser, but it implies a lot of administrative privileges anyway --- for instance the ability to change the password of another account. So this may not be exactly what the OP wants. We intended CREATEROLE as a way that a DBA could do all his routine account-management duties without being a "real" superuser (with the attendant ability to completely destroy the system with a slipup). Think of it as sudo that lets you do anything you want to /etc/passwd, but not "rm -rf /"... If you want exactly what was stated --- the ability to create new roles, and nothing else --- the best way is to create a function that does only that one thing, make it superuser-owned and SECURITY DEFINER, and grant execute privilege on it to whomever you want to be able to do that. (Don't forget to revoke the default public execute privilege.) Gateway functions of this sort are the standard solution whenever the system's classification of privileges isn't quite what you want. regards, tom lane