Обсуждение: CREATEROLE, CREATEDB

Is it correct that a user with CREATEROLE privilege but without CREATEDB 
privilege can create a user with *CREATEDB* privilege, thus bypassing his 
original restrictions?  This sequence doesn't look right:

pei=# create user foo1 createrole;
CREATE ROLE
pei=# \c - foo1
You are now connected to database "pei" as user "foo1".
pei=> create database test;
ERROR:  permission denied to create database
pei=> create user foo2 createdb;
CREATE ROLE
pei=> \c - foo2
You are now connected to database "pei" as user "foo2".
pei=> create database test;
CREATE DATABASE

-- 
Peter Eisentraut
http://developer.postgresql.org/~petere/

--On Dienstag, Juni 05, 2007 16:04:44 +0200 Peter Eisentraut 
<peter_e@gmx.net> wrote:

> Is it correct that a user with CREATEROLE privilege but without CREATEDB
> privilege can create a user with *CREATEDB* privilege, thus bypassing his
> original restrictions?  This sequence doesn't look right:
>
> pei=# create user foo1 createrole;
> CREATE ROLE
> pei=# \c - foo1
> You are now connected to database "pei" as user "foo1".
> pei=> create database test;
> ERROR:  permission denied to create database
> pei=> create user foo2 createdb;
> CREATE ROLE
> pei=> \c - foo2
> You are now connected to database "pei" as user "foo2".
> pei=> create database test;
> CREATE DATABASE

I had this issue once, too. CREATEROLE doesn't imply any inheritance from a 
role which gots this privilege, thus you are required to treat such roles 
much the same as superuser. This behavior is documented (well, at least in 
8.2, haven't looked in 8.1):

<http://www.postgresql.org/docs/8.2/interactive/sql-createrole.html>

<snip>Be careful with the CREATEROLE privilege. There is no concept of 
inheritance for the privileges of a CREATEROLE-role. That means that even 
if a role does not have a certain privilege but is allowed to create other 
roles, it can easily create another role with different privileges than its 
own (except for creating roles with superuser privileges). For example, if 
the role "user" has the CREATEROLE privilege but not the CREATEDB 
privilege, nonetheless it can create a new role with the CREATEDB 
privilege. Therefore, regard roles that have the CREATEROLE privilege as 
almost-superuser-roles.
</snip>

--  Thanks
                   Bernd

Bernd Helmle <mailings@oopsware.de> writes:
> --On Dienstag, Juni 05, 2007 16:04:44 +0200 Peter Eisentraut 
> <peter_e@gmx.net> wrote:
>> Is it correct that a user with CREATEROLE privilege but without CREATEDB
>> privilege can create a user with *CREATEDB* privilege, thus bypassing his
>> original restrictions?

> I had this issue once, too. CREATEROLE doesn't imply any inheritance from a 
> role which gots this privilege, thus you are required to treat such roles 
> much the same as superuser. This behavior is documented (well, at least in 
> 8.2, haven't looked in 8.1):

This is by design --- the point of CREATEROLE is that you can do
anything you want in the line of account management, without having
all the dangers inherent in being a real superuser.  It's not something
you'd give out to people you didn't trust.
        regards, tom lane

Peter Eisentraut wrote:
> Is it correct that a user with CREATEROLE privilege but without CREATEDB 
> privilege can create a user with *CREATEDB* privilege, thus bypassing his 
> original restrictions?  This sequence doesn't look right:
>
> pei=# create user foo1 createrole;
> CREATE ROLE
> pei=# \c - foo1
> You are now connected to database "pei" as user "foo1".
> pei=> create database test;
> ERROR:  permission denied to create database
> pei=> create user foo2 createdb;
> CREATE ROLE
> pei=> \c - foo2
> You are now connected to database "pei" as user "foo2".
> pei=> create database test;
> CREATE DATABASE
>
>   
that's how its documented:
http://www.postgresql.org/docs/8.2/interactive/sql-createrole.html

Be careful with the CREATEROLE privilege. There is no concept of 
inheritance for the privileges of a CREATEROLE-role. That means that 
even if a role does not have a certain privilege but is allowed to 
create other roles, it can easily create another role with different 
privileges than its own (except for creating roles with superuser 
privileges). For example, if the role "user" has the CREATEROLE 
privilege but not the CREATEDB privilege, nonetheless it can create a 
new role with the CREATEDB privilege. Therefore, regard roles that have 
the CREATEROLE privilege as almost-superuser-roles.

-- 
Chander Ganesan
The Open Technology Group
One Copley Parkway, Suite 210
Morrisville, NC  27560
Phone: 877-258-8987/919-463-0999
http://www.otg-nc.com