pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Ben Kim <bkim@tamu.edu>
To: Peter Elmers <p.elmers@gmx.de>
Cc: pgsql-admin@postgresql.org
Subject: Re: adding a user with the permission to create trimmed users
Date: Fri, 17 Aug 2007 12:28:14 -0500 (CDT)
Message-ID: <Pine.GSO.4.64.0708171216250.24598@coe.tamu.edu> (raw)
In-Reply-To: <389EEEE3-3B4E-4B0C-B16B-7F61E8EDE76C@gmx.de>
References: <389EEEE3-3B4E-4B0C-B16B-7F61E8EDE76C@gmx.de>

> 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



view thread (5+ messages)  latest in thread

Message-ID: <Pine.GSO.4.64.0708171216250.24598@coe.tamu.edu>
Permalink:  ../Pine.GSO.4.64.0708171216250.24598@coe.tamu.edu/
Also on:    postgresql.org/message-id/Pine.GSO.4.64.0708171216250.24598@coe.tamu.edu

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-admin@postgresql.org
  Cc: bkim@tamu.edu, p.elmers@gmx.de
  Subject: Re: adding a user with the permission to create trimmed users
  In-Reply-To: <Pine.GSO.4.64.0708171216250.24598@coe.tamu.edu>
  References: <389EEEE3-3B4E-4B0C-B16B-7F61E8EDE76C@gmx.de> <Pine.GSO.4.64.0708171216250.24598@coe.tamu.edu>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox