Received: from localhost (maia-2.hub.org [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id 487869F963C for ; Tue, 21 Aug 2007 11:17:18 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.187]) (amavisd-maia, port 10024) with ESMTP id 88382-01 for ; Tue, 21 Aug 2007 11:17:00 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5 Received: from mail.gmx.net (mail.gmx.net [213.165.64.20]) by postgresql.org (Postfix) with SMTP id A8CC29F9648 for ; Tue, 21 Aug 2007 11:17:12 -0300 (ADT) Received: (qmail invoked by alias); 21 Aug 2007 14:17:10 -0000 Received: from fwo.gutzmann.com (EHLO [10.0.2.200]) [217.6.22.218] by mail.gmx.net (mp031) with SMTP; 21 Aug 2007 16:17:10 +0200 X-Authenticated: #6837117 X-Provags-ID: V01U2FsdGVkX1/teu1JfYHt1j2OaIeliZxeKrZeb0PKkEyqtwenxx bhIVRsVfmZXOCN In-Reply-To: References: <389EEEE3-3B4E-4B0C-B16B-7F61E8EDE76C@gmx.de> Mime-Version: 1.0 (Apple Message framework v752.3) Content-Type: text/plain; charset=US-ASCII; delsp=yes; format=flowed Message-Id: <9DC874AE-B609-4FCF-B0E8-B055658667AB@gmx.de> Cc: pgsql-admin@postgresql.org Content-Transfer-Encoding: 7bit From: Peter Elmers Subject: Re: adding a user with the permission to create trimmed users Date: Tue, 21 Aug 2007 16:17:08 +0200 To: Ben Kim X-Mailer: Apple Mail (2.752.3) X-Y-GMX-Trusted: 0 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200708/248 X-Sequence-Number: 26579 Hi Ben! Thanks for your answer! It hits the spot. Peter Am 17.08.2007 um 19:28 schrieb Ben Kim: >> I want to know whether there exist the possibility to create a >> user who has the permission to create users with preset limited >> permissions. >> >> In detail: >> I do not want that the admin user (a separate limited one for the >> customer) is able to create full admin users or adequate ones. > > It's likely possible. There might be a better reference but you > might want to read this: (Look for Tom Lane's post) > > http://archives.devshed.com/forums/databases-124/anyone-know-a- > good- opensource-crm-that-actually-installs-with-2217243-2.html > > Following his example: > > create or replace function makeuser(text, text) returns void as $$ > begin execute 'create role ' || quote_ident($1) || ' login > password ' > || quote_literal($2) ; end$$ language plpgsql security definer; > > then you can > > create role a_group; > grant select,insert,update on a_table to a_group with grant option; > alter group a_group add user a_user; > > so a_user can now > > select makeuser('b_user','b_password'); > grant select on a_table to b_user; > > > There might be a better way though. > > If you're on 8.1 and upwards, createrole is not separate from > superuser. But it seemed if a user is given createrole, even though > he's not given the superuser, s/he can drop other users (who s/he > didn't create.) So alter user...createuser may not fit here. Please > correct me if I'm wrong. > > > HTH > > > Ben K. > Developer > http://benix.tamu.edu