--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