adding a user with the permission to create trimmed users

Поиск
Список
Период
Сортировка
Искать

adding a user with the permission to create trimmed users

От:
Peter Elmers <p.elmers@gmx.de>
Дата:
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

Re: adding a user with the permission to create trimmed users

От:
"Kevin Grittner" <Kevin.Grittner@wicourts.gov>
Дата:
>>> On Fri, Aug 17, 2007 at  4:50 AM, in message
<389EEEE3-3B4E-4B0C-B16B-7F61E8EDE76C@gmx.de>, Peter Elmers 
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
 


Re: adding a user with the permission to create trimmed users

От:
Ben Kim <bkim@tamu.edu>
Дата:
> 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

Re: adding a user with the permission to create trimmed users

От:
Ben Kim <bkim@tamu.edu>
Дата:
> 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

Re: adding a user with the permission to create trimmed users

От:
Peter Elmers <p.elmers@gmx.de>
Дата:
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

FAQ