pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedRe: SUPERUSER vs CREATEUSER causes foul 'code smell'
5+ messages / 5 participants
[nested] [flat]
* Re: SUPERUSER vs CREATEUSER causes foul 'code smell'
@ 2007-08-16 15:35 Michael Glaesemann <grzm@seespotcode.net>
parent: Joshua_Kramer <josh@globalherald.net>
2 siblings, 0 replies; 5+ messages in thread
From: Michael Glaesemann @ 2007-08-16 15:35 UTC (permalink / raw)
To: Joshua_Kramer <josh@globalherald.net>; +Cc: pgsql-sql
On Aug 16, 2007, at 10:36 , Joshua_Kramer wrote:
> In the pg_users view - is there a way to differentiate between a
> role with SUPERUSER priveleges, and a user who merely has the
> CREATEUSER flag?
>
> If I want to create a role who can create other roles, but not have
> other SUPERUSER priveleges - how can I do that?
I think you may be confusing CREATEROLE and CREATEUSER. AIUI,
CREATEUSER is a holdover from the pre-role PostgreSQL days, when
CREATEUSER implied SUPERUSER. (I may very well be wrong with the
explanation, but the effects look the same.) CREATEROLE privilege
does not imply SUPERUSER however.
test=# create role user_creator with createuser;
CREATE ROLE
test=# create role role_creator with createrole;
CREATE ROLE
test=# select rolname, rolsuper, rolcreaterole from pg_roles;
rolname | rolsuper | rolcreaterole
-----------------------------+----------+---------------
postgres | t | t
...
user_creator | t | f
role_creator | f | t
(By the way, I don't see a pg_users view in v8.2.4. There's a pg_user
view and a pg_roles view however. What version are you using?)
Michael Glaesemann
grzm seespotcode net
^ permalink raw reply [nested|flat] 5+ messages in thread
* SUPERUSER vs CREATEUSER causes foul 'code smell'
@ 2007-08-16 15:36 Joshua_Kramer <josh@globalherald.net>
0 siblings, 3 replies; 5+ messages in thread
From: Joshua_Kramer @ 2007-08-16 15:36 UTC (permalink / raw)
To: pgsql-sql
Hello,
In the pg_users view - is there a way to differentiate between a role with
SUPERUSER priveleges, and a user who merely has the CREATEUSER flag?
If I want to create a role who can create other roles, but not have other
SUPERUSER priveleges - how can I do that?
Cheers,
-J
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: SUPERUSER vs CREATEUSER causes foul 'code smell'
@ 2007-08-16 15:41 Rodrigo De León <rdeleonp@gmail.com>
parent: Joshua_Kramer <josh@globalherald.net>
2 siblings, 1 reply; 5+ messages in thread
From: Rodrigo De León @ 2007-08-16 15:41 UTC (permalink / raw)
To: Joshua_Kramer <josh@globalherald.net>; +Cc: pgsql-sql
On 8/16/07, Joshua_Kramer <josh@globalherald.net> wrote:
> In the pg_users view - is there a way to differentiate between a role with
> SUPERUSER priveleges, and a user who merely has the CREATEUSER flag?
select * from pg_roles;
> If I want to create a role who can create other roles, but not have other
> SUPERUSER priveleges - how can I do that?
create role foo createrole login password 'foo';
See:
http://www.postgresql.org/docs/8.2/static/sql-createrole.html
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: SUPERUSER vs CREATEUSER causes foul 'code smell'
@ 2007-08-16 15:47 Peter Eisentraut <peter_e@gmx.net>
parent: Joshua_Kramer <josh@globalherald.net>
2 siblings, 0 replies; 5+ messages in thread
From: Peter Eisentraut @ 2007-08-16 15:47 UTC (permalink / raw)
To: pgsql-sql; +Cc: Joshua_Kramer <josh@globalherald.net>
Am Donnerstag, 16. August 2007 17:36 schrieb Joshua_Kramer:
> In the pg_users view - is there a way to differentiate between a role with
> SUPERUSER priveleges, and a user who merely has the CREATEUSER flag?
No, because they are the same.
> If I want to create a role who can create other roles, but not have other
> SUPERUSER priveleges - how can I do that?
See CREATEROLE privilege.
--
Peter Eisentraut
http://developer.postgresql.org/~petere/
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: SUPERUSER vs CREATEUSER causes foul 'code smell'
@ 2007-08-16 16:43 Tom Lane <tgl@sss.pgh.pa.us>
parent: Rodrigo De León <rdeleonp@gmail.com>
0 siblings, 0 replies; 5+ messages in thread
From: Tom Lane @ 2007-08-16 16:43 UTC (permalink / raw)
To: Rodrigo De León <rdeleonp@gmail.com>; +Cc: Joshua_Kramer <josh@globalherald.net>; pgsql-sql
"=?UTF-8?Q?Rodrigo_De_Le=C3=B3n?=" <rdeleonp@gmail.com> writes:
> On 8/16/07, Joshua_Kramer <josh@globalherald.net> wrote:
>> If I want to create a role who can create other roles, but not have other
>> SUPERUSER priveleges - how can I do that?
> create role foo createrole login password 'foo';
CREATEROLE privilege is not exactly a superuser, but it implies a lot of
administrative privileges anyway --- for instance the ability to change
the password of another account. So this may not be exactly what the OP
wants. We intended CREATEROLE as a way that a DBA could do all his
routine account-management duties without being a "real" superuser (with
the attendant ability to completely destroy the system with a slipup).
Think of it as sudo that lets you do anything you want to /etc/passwd,
but not "rm -rf /"...
If you want exactly what was stated --- the ability to create new roles,
and nothing else --- the best way is to create a function that does only
that one thing, make it superuser-owned and SECURITY DEFINER, and grant
execute privilege on it to whomever you want to be able to do that.
(Don't forget to revoke the default public execute privilege.) Gateway
functions of this sort are the standard solution whenever the system's
classification of privileges isn't quite what you want.
regards, tom lane
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2007-08-16 16:43 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-08-16 15:36 SUPERUSER vs CREATEUSER causes foul 'code smell' Joshua_Kramer <josh@globalherald.net>
2007-08-16 15:35 ` Michael Glaesemann <grzm@seespotcode.net>
2007-08-16 15:41 ` Rodrigo De León <rdeleonp@gmail.com>
2007-08-16 16:43 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-08-16 15:47 ` Peter Eisentraut <peter_e@gmx.net>
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