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@poczta.homelinux.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
