pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedadding a user with the permission to create trimmed users
5+ messages / 3 participants
[nested] [flat]
* adding a user with the permission to create trimmed users
@ 2007-08-17 09:50 Peter Elmers <p.elmers@gmx.de>
2007-08-17 17:05 ` Re: adding a user with the permission to create trimmed users Kevin Grittner <Kevin.Grittner@wicourts.gov>
2007-08-17 17:28 ` Re: adding a user with the permission to create trimmed users Ben Kim <bkim@tamu.edu>
0 siblings, 2 replies; 5+ messages in thread
From: Peter Elmers @ 2007-08-17 09:50 UTC (permalink / raw)
To: pgsql-admin
Hi!
I want to know whether there exist the possibility to create a user
who has the permission to create users with preset limited permissions.
In detail:
I do not want that the admin user (a seperate limited one for the
customer) is able to create full admin users or adequate ones.
Thank you for reading an answering,
Peter
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: adding a user with the permission to create trimmed users
2007-08-17 09:50 adding a user with the permission to create trimmed users Peter Elmers <p.elmers@gmx.de>
@ 2007-08-17 17:05 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
1 sibling, 0 replies; 5+ messages in thread
From: Kevin Grittner @ 2007-08-17 17:05 UTC (permalink / raw)
To: Peter Elmers <p.elmers@gmx.de>; pgsql-admin
>>> On Fri, Aug 17, 2007 at 4:50 AM, in message
<389EEEE3-3B4E-4B0C-B16B-7F61E8EDE76C@gmx.de>, Peter Elmers <p.elmers@gmx.de>
wrote:
> I want to know whether there exist the possibility to create a user
> who has the permission to create users with preset limited permissions.
>
> In detail:
> I do not want that the admin user (a seperate limited one for the
> customer) is able to create full admin users or adequate ones.
Without more detail it is hard to give a specific answer, but it is possible
to grant a user the right to grant specific permissions. See:
http://www.postgresql.org/docs/8.2/interactive/sql-grant.html
http://www.postgresql.org/docs/8.2/interactive/sql-revoke.html
Pay particular attention to "WITH GRANT OPTION" and "WITH ADMIN OPTION", as
well as the discussion of the public group. (You will probably need to
revoke some of the default rights of the public group.)
If you can't get it to work as desired, please post again with more
specifics.
-Kevin
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: adding a user with the permission to create trimmed users
2007-08-17 09:50 adding a user with the permission to create trimmed users Peter Elmers <p.elmers@gmx.de>
@ 2007-08-17 17:28 ` Ben Kim <bkim@tamu.edu>
2007-08-17 17:39 ` Re: adding a user with the permission to create trimmed users Ben Kim <bkim@tamu.edu>
2007-08-21 14:17 ` Re: adding a user with the permission to create trimmed users Peter Elmers <p.elmers@gmx.de>
1 sibling, 2 replies; 5+ messages in thread
From: Ben Kim @ 2007-08-17 17:28 UTC (permalink / raw)
To: Peter Elmers <p.elmers@gmx.de>; +Cc: pgsql-admin
> I want to know whether there exist the possibility to create a user who has
> the permission to create users with preset limited permissions.
>
> In detail:
> I do not want that the admin user (a separate limited one for the customer)
> is able to create full admin users or adequate ones.
It's likely possible. There might be a better reference but you might want
to read this: (Look for Tom Lane's post)
http://archives.devshed.com/forums/databases-124/anyone-know-a-good-
opensource-crm-that-actually-installs-with-2217243-2.html
Following his example:
create or replace function makeuser(text, text) returns void as $$
begin execute 'create role ' || quote_ident($1) || ' login password '
|| quote_literal($2) ; end$$ language plpgsql security definer;
then you can
create role a_group;
grant select,insert,update on a_table to a_group with grant option;
alter group a_group add user a_user;
so a_user can now
select makeuser('b_user','b_password');
grant select on a_table to b_user;
There might be a better way though.
If you're on 8.1 and upwards, createrole is not separate from superuser.
But it seemed if a user is given createrole, even though he's not given
the superuser, s/he can drop other users (who s/he didn't create.) So
alter user...createuser may not fit here. Please correct me if I'm wrong.
HTH
Ben K.
Developer
http://benix.tamu.edu
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: adding a user with the permission to create trimmed users
2007-08-17 09:50 adding a user with the permission to create trimmed users Peter Elmers <p.elmers@gmx.de>
2007-08-17 17:28 ` Re: adding a user with the permission to create trimmed users Ben Kim <bkim@tamu.edu>
@ 2007-08-17 17:39 ` Ben Kim <bkim@tamu.edu>
1 sibling, 0 replies; 5+ messages in thread
From: Ben Kim @ 2007-08-17 17:39 UTC (permalink / raw)
To: Peter Elmers <p.elmers@gmx.de>; +Cc: pgsql-admin
> If you're on 8.1 and upwards, createrole is not separate from superuser. But
Sorry, please read "createrole is not separate" as "createrole is NOW
separate". I also wanted to add that a good testing is needed.
Regards,
Ben K.
Developer
http://benix.tamu.edu
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: adding a user with the permission to create trimmed users
2007-08-17 09:50 adding a user with the permission to create trimmed users Peter Elmers <p.elmers@gmx.de>
2007-08-17 17:28 ` Re: adding a user with the permission to create trimmed users Ben Kim <bkim@tamu.edu>
@ 2007-08-21 14:17 ` Peter Elmers <p.elmers@gmx.de>
1 sibling, 0 replies; 5+ messages in thread
From: Peter Elmers @ 2007-08-21 14:17 UTC (permalink / raw)
To: Ben Kim <bkim@tamu.edu>; +Cc: pgsql-admin
Hi Ben!
Thanks for your answer! It hits the spot.
Peter
Am 17.08.2007 um 19:28 schrieb Ben Kim:
>> I want to know whether there exist the possibility to create a
>> user who has the permission to create users with preset limited
>> permissions.
>>
>> In detail:
>> I do not want that the admin user (a separate limited one for the
>> customer) is able to create full admin users or adequate ones.
>
> It's likely possible. There might be a better reference but you
> might want to read this: (Look for Tom Lane's post)
>
> http://archives.devshed.com/forums/databases-124/anyone-know-a-
> good- opensource-crm-that-actually-installs-with-2217243-2.html
>
> Following his example:
>
> create or replace function makeuser(text, text) returns void as $$
> begin execute 'create role ' || quote_ident($1) || ' login
> password '
> || quote_literal($2) ; end$$ language plpgsql security definer;
>
> then you can
>
> create role a_group;
> grant select,insert,update on a_table to a_group with grant option;
> alter group a_group add user a_user;
>
> so a_user can now
>
> select makeuser('b_user','b_password');
> grant select on a_table to b_user;
>
>
> There might be a better way though.
>
> If you're on 8.1 and upwards, createrole is not separate from
> superuser. But it seemed if a user is given createrole, even though
> he's not given the superuser, s/he can drop other users (who s/he
> didn't create.) So alter user...createuser may not fit here. Please
> correct me if I'm wrong.
>
>
> HTH
>
>
> Ben K.
> Developer
> http://benix.tamu.edu
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2007-08-21 14:17 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-08-17 09:50 adding a user with the permission to create trimmed users Peter Elmers <p.elmers@gmx.de>
2007-08-17 17:05 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2007-08-17 17:28 ` Ben Kim <bkim@tamu.edu>
2007-08-17 17:39 ` Ben Kim <bkim@tamu.edu>
2007-08-21 14:17 ` Peter Elmers <p.elmers@gmx.de>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox