Received: from localhost (maia-4.hub.org [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id C48AA9F9DD3 for ; Fri, 17 Aug 2007 14:30:38 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 49266-01 for ; Fri, 17 Aug 2007 14:30:31 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from coe.tamu.edu (edsun.coe.tamu.edu [165.91.232.6]) by postgresql.org (Postfix) with ESMTP id DECD89FB92E for ; Fri, 17 Aug 2007 14:30:30 -0300 (ADT) Received: (from root@localhost) by coe.tamu.edu (8.11.7p3+Sun/8.12.11) id l7HHTOB03934 for pgsql-admin@postgresql.org; Fri, 17 Aug 2007 12:29:24 -0500 (CDT) Received: from coe.tamu.edu (coe.tamu.edu [165.91.232.6]) by coe.tamu.edu (8.11.7p3+Sun/8.12.11) with ESMTP id l7HHSEC03905; Fri, 17 Aug 2007 12:28:14 -0500 (CDT) Date: Fri, 17 Aug 2007 12:28:14 -0500 (CDT) From: Ben Kim To: Peter Elmers cc: pgsql-admin@postgresql.org Subject: Re: adding a user with the permission to create trimmed users In-Reply-To: <389EEEE3-3B4E-4B0C-B16B-7F61E8EDE76C@gmx.de> Message-ID: References: <389EEEE3-3B4E-4B0C-B16B-7F61E8EDE76C@gmx.de> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII; format=flowed X-Virus-Scanned: by AMaViS 0.3.12 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200708/204 X-Sequence-Number: 26535 > 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