pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
adding a user with the permission to create trimmed users
5+ messages / 3 participants
[nested] [flat]

* adding a user with the permission to create trimmed users
@ 2007-08-17 09:50  Peter Elmers <p.elmers@gmx.de>
  0 siblings, 2 replies; 5+ messages in thread

From: Peter Elmers @ 2007-08-17 09:50 UTC (permalink / raw)
  To: pgsql-admin

Hi!

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 seperate limited one for the  
customer) is able to create full admin users or adequate ones.

Thank you for reading an answering,


Peter



^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: adding a user with the permission to create trimmed users
@ 2007-08-17 17:05  Kevin Grittner <Kevin.Grittner@wicourts.gov>
  parent: Peter Elmers <p.elmers@gmx.de>
  1 sibling, 0 replies; 5+ messages in thread

From: Kevin Grittner @ 2007-08-17 17:05 UTC (permalink / raw)
  To: Peter Elmers <p.elmers@gmx.de>; pgsql-admin

>>> On Fri, Aug 17, 2007 at  4:50 AM, in message
<389EEEE3-3B4E-4B0C-B16B-7F61E8EDE76C@gmx.de>, Peter Elmers <p.elmers@gmx.de>
wrote: 
> 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 seperate limited one for the  
> customer) is able to create full admin users or adequate ones.
 
Without more detail it is hard to give a specific answer, but it is possible
to grant a user the right to grant specific permissions.  See:
 
http://www.postgresql.org/docs/8.2/interactive/sql-grant.html
 
http://www.postgresql.org/docs/8.2/interactive/sql-revoke.html
 
Pay particular attention to "WITH GRANT OPTION" and "WITH ADMIN OPTION", as
well as the discussion of the public group.  (You will probably need to
revoke some of the default rights of the public group.)
 
If you can't get it to work as desired, please post again with  more
specifics.
 
-Kevin
 





^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: adding a user with the permission to create trimmed users
@ 2007-08-17 17:28  Ben Kim <bkim@tamu.edu>
  parent: Peter Elmers <p.elmers@gmx.de>
  1 sibling, 2 replies; 5+ messages in thread

From: Ben Kim @ 2007-08-17 17:28 UTC (permalink / raw)
  To: Peter Elmers <p.elmers@gmx.de>; +Cc: pgsql-admin

> 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



^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: adding a user with the permission to create trimmed users
@ 2007-08-17 17:39  Ben Kim <bkim@tamu.edu>
  parent: Ben Kim <bkim@tamu.edu>
  1 sibling, 0 replies; 5+ messages in thread

From: Ben Kim @ 2007-08-17 17:39 UTC (permalink / raw)
  To: Peter Elmers <p.elmers@gmx.de>; +Cc: pgsql-admin

> If you're on 8.1 and upwards, createrole is not separate from superuser. But

Sorry, please read "createrole is not separate" as "createrole is NOW 
separate". I also wanted to add that a good testing is needed.

Regards,

Ben K.
Developer
http://benix.tamu.edu



^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: adding a user with the permission to create trimmed users
@ 2007-08-21 14:17  Peter Elmers <p.elmers@gmx.de>
  parent: Ben Kim <bkim@tamu.edu>
  1 sibling, 0 replies; 5+ messages in thread

From: Peter Elmers @ 2007-08-21 14:17 UTC (permalink / raw)
  To: Ben Kim <bkim@tamu.edu>; +Cc: pgsql-admin

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




^ permalink  raw  reply  [nested|flat] 5+ messages in thread


end of thread, other threads:[~2007-08-21 14:17 UTC | newest]

Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-08-17 09:50 adding a user with the permission to create trimmed users Peter Elmers <p.elmers@gmx.de>
2007-08-17 17:05 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2007-08-17 17:28 ` Ben Kim <bkim@tamu.edu>
2007-08-17 17:39   ` Ben Kim <bkim@tamu.edu>
2007-08-21 14:17   ` Peter Elmers <p.elmers@gmx.de>

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