Re: permission to create user

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.

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.

http://www.postgresql.org/docs/8.1/interactive/sql-createfunction.html

John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL

On Mon, Jul 17, 2006 at 07:54:08AM -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.

Also, if you're using 8.1, then giving certain roles the CREATEROLE
attribute might be what you're after.

http://www.postgresql.org/docs/8.1/interactive/role-attributes.html

--
Michael Fuhr

Hi,

I've been trying to do that same thing, and it works.

Still, one point in the process 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?

-R

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.
>
>
> http://www.postgresql.org/docs/8.1/interactive/sql-createfunction.html
>
>
>
>
> John DeSoi, Ph.D.
> http://pgedit.com/
> Power Tools for PostgreSQL
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
--
Rafal Pietrak <rafal(at)poczta(dot)homelinux(dot)com>

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?

--
-R

On Tue, Jul 18, 2006 at 01:45:01PM +0200, Rafal Pietrak wrote:
> 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?

http://www.postgresql.org/docs/8.1/interactive/role-membership.html

"The role attributes LOGIN, SUPERUSER, CREATEDB, and CREATEROLE can
be thought of as special privileges, but they are never inherited
as ordinary privileges on database objects are. You must actually
SET ROLE to a specific role having one of these attributes in order
to make use of the attribute. Continuing the above example, we
might well choose to grant CREATEDB and CREATEROLE to the admin
role. Then a session connecting as role joe would not have these
privileges immediately, only after doing SET ROLE admin."

--
Michael Fuhr

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.

On Tue, 2006-07-18 at 07:31 -0600, Michael Fuhr wrote:
> On Tue, Jul 18, 2006 at 01:45:01PM +0200, Rafal Pietrak wrote:
> > 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?
>
> http://www.postgresql.org/docs/8.1/interactive/role-membership.html
>
> "The role attributes LOGIN, SUPERUSER, CREATEDB, and CREATEROLE can
> be thought of as special privileges, but they are never inherited
> as ordinary privileges on database objects are. You must actually
> SET ROLE to a specific role having one of these attributes in order
> to make use of the attribute. Continuing the above example, we
> might well choose to grant CREATEDB and CREATEROLE to the admin
> role. Then a session connecting as role joe would not have these
> privileges immediately, only after doing SET ROLE admin."

Thenx. So it's a feature (it is documented).

My appology if the following question is naive, but digging it a bit
more:

Is it a feature, because it should be that way.... why? (standard says
so?) ...or it's a feature because it's documented: "Although we'd like
it to work like priviledges work on tables, current server-side
framework does not allow us to impolement it that way."

In other words:
1) is the discrepancy by design (why?) or
2) is it by accident - just results from development history.

--
-R

Rafal Pietrak <rafal(at)zorro(dot)isa-geek(dot)com> writes:
> 1) is the discrepancy by design (why?) or

Yes. I think we were mostly concerned about superuserness being too
dangerous to inherit.

regards, tom lane