Обсуждение: 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
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
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 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.
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@poczta.homelinux.com>
Rafal Pietrak <rafal@zorro.isa-geek.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