Обсуждение: adding a user with the permission to create trimmed users

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

>>> 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

> 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

> 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

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