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.