niXforums Forum Index
 FAQFAQ   SearchSearch   MemberlistMemberlist   UsergroupsUsergroups   RegisterRegister 
 ProfileProfile   PreferencesPreferences   Log in to check your private messagesLog in to check your private messages   Log inLog in 
·  nixdoc.net ·  man pages ·  Linux HOWTOs ·  FreeBSD Tips ·  Forums
navigation Forum index » Databases » PostgreSQL
permission to create user
Post new topic   Reply to topic Page 1 of 1 [9 Posts] View previous topic :: View next topic
Author Message
Timothy Smith
*nix forums addict


Joined: 16 Apr 2005
Posts: 61

PostPosted: Mon Jul 17, 2006 6:56 am    Post subject: permission to create user Reply with quote
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.

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Back to top
John DeSoi
*nix forums Guru Wannabe


Joined: 14 Mar 2005
Posts: 116

PostPosted: Mon Jul 17, 2006 11:54 am    Post subject: Re: permission to create user Reply with quote
On Jul 17, 2006, at 2:56 AM, Timothy Smith wrote:

Quote:
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
Back to top
Michael Fuhr
*nix forums Guru


Joined: 02 Mar 2005
Posts: 674

PostPosted: Mon Jul 17, 2006 12:23 pm    Post subject: Re: permission to create user Reply with quote
On Mon, Jul 17, 2006 at 07:54:08AM -0400, John DeSoi wrote:
Quote:
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

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
Back to top
Rafal Pietrak
*nix forums beginner


Joined: 23 May 2006
Posts: 36

PostPosted: Tue Jul 18, 2006 7:11 am    Post subject: Re: permission to create user Reply with quote
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:
Quote:
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>

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly
Back to top
Rafal Pietrak
*nix forums beginner


Joined: 23 May 2006
Posts: 36

PostPosted: Tue Jul 18, 2006 11:45 am    Post subject: Re: permission to create user Reply with quote
On Mon, 2006-07-17 at 07:54 -0400, John DeSoi wrote:
Quote:
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

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq
Back to top
Michael Fuhr
*nix forums Guru


Joined: 02 Mar 2005
Posts: 674

PostPosted: Tue Jul 18, 2006 1:31 pm    Post subject: Re: permission to create user Reply with quote
On Tue, Jul 18, 2006 at 01:45:01PM +0200, Rafal Pietrak wrote:
Quote:
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

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
Back to top
Timothy Smith
*nix forums addict


Joined: 16 Apr 2005
Posts: 61

PostPosted: Tue Jul 18, 2006 1:45 pm    Post subject: Re: permission to create user Reply with quote
Rafal Pietrak wrote:
Quote:
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.

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
Back to top
Rafal Pietrak
*nix forums beginner


Joined: 23 May 2006
Posts: 36

PostPosted: Tue Jul 18, 2006 1:53 pm    Post subject: Re: permission to create user Reply with quote
On Tue, 2006-07-18 at 07:31 -0600, Michael Fuhr wrote:
Quote:
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

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
Back to top
Tom Lane
*nix forums Guru


Joined: 24 Mar 2005
Posts: 2070

PostPosted: Tue Jul 18, 2006 5:06 pm    Post subject: Re: permission to create user Reply with quote
Rafal Pietrak <rafal@zorro.isa-geek.com> writes:
Quote:
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

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly
Back to top
Google
Back to top
Display posts from previous:   
Post new topic   Reply to topic Page 1 of 1 [9 Posts] View previous topic :: View next topic
The time now is Thu Jan 08, 2009 6:37 am | All times are GMT
navigation Forum index » Databases » PostgreSQL
Jump to:  

Similar Topics
Topic Author Forum Replies Last Post
No new posts postfix smtp authentication using mysql stored user/pass rtresidd Postfix 0 Fri Oct 03, 2008 5:58 am
No new posts User Environment - export PATH variable paalepu AIX 0 Tue Sep 12, 2006 8:12 pm
No new posts Permission denied executing apachectl Josep Sanmarti Apache 1 Fri Jul 21, 2006 10:06 am
No new posts Capturing user login Information of windows sachin PHP 3 Fri Jul 21, 2006 5:44 am
No new posts Recommend an email program for the debian-user-digest? DJ Hackenbruch Debian 5 Fri Jul 21, 2006 3:20 am

Debt Consolidation | Debt Consolidation | Car Finance | Debt Consolidation | Rapidshare eBooks Download
Copyright © 2004-2005 DeniX Solutions SRL
 
Other DeniX Solutions sites: Unix/Linux blog |  electronics forum |  medicine forum |  science forum | 
Privacy Policy


Powered by phpBB © 2001, 2005 phpBB Group

[ Time: 0.2342s ][ Queries: 16 (0.1251s) ][ GZIP on - Debug on ]