Thread: SUPERUSER vs CREATEUSER causes foul 'code smell'

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

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

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

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/

"Rodrigo De León" <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