Re: permission to create user

Rafal Pietrak wrote:
> On Mon, 2006-07-17 at 07:54 -0400, John DeSoi wrote:
>
>> On Jul 17, 2006, at 2:56 AM, Timothy Smith wrote:
>>
>>
>>> is it possible to give a non super user the ability to create
>>> another user of a different group?
>>> i'm looking for a way to assign a special group of admin's just
>>> enough rights to create other lowbie users without letting them
>>> bypass all other access restrictions.
>>>
>> You could create a function with the SECURITY DEFINER option which
>> allows the function to be executed with the privileges of the user
>> that created it.
>>
>
> I've been trying to do that same thing, and it works even without the
> function. Still, it works with a 'glitch' but the reason for that
> 'glitch' is not quite clear to me. When I have:
>         CREATE GROUP masters;
>         ALTER ROLE masters CREATEUSER;
>         CREATE USER user_one IN GROUP MASTERS;
>         CREATE TABLE test1 (stamp timestamp, thing text);
>         REVOKE ALL ON test1 FROM PUBLIC;
>         GRANT INSERT ON test1 TO MASTERS;
>
> Then, then I do:
>         system_prompt$ psql -U user_one mydb
>         mydb> INSERT INTO test1 (stamp) VALUES (current_timestamp);
>                 -- this works OK!!
>         mydb> CREATE USER user_two;
>                 -- this fails unless I do:
>         mydb> SET ROLE masters;
>         mydb> CREATE USER user_two;
>                 -- this works OK, "user_two" gets created.
>
> Any one knows, why do I have to explicitly SET ROLE, when I try to
> exercise the group priviledge of role creation, while I don't need that
> when accessing tables? Is this a feature, or a bug?
>
>
I got it to work for me using the previous advice of setting CREATEROLE
for the group of users i wanted to have permission to do so.