Re: CREATE USER and createuser not working the same

Hi,

It seems the shell command createuser and the SQL CREATE USER don't act
the same way, unless the documentation says they do.

In fact, asking to see the role created with the external command let
see that a superuser created that way hase rights of superuser AND
'create role', 'create DB' rights, while a superuser created with an SQL
query does have superuser rights, but it is said it doesn't have create
role and create db role.

The superuser created the second way can create a role or a DB
has the code do bypass these rights check, has the first test lokks for
superuser privileges.

I wonder then where this bug takes place, if ever you think it is a bug.
Is it just a undocumented difference?
I'm afraid it is just a bit more than that as it shows a superuser does
not have all the rights one would think he has. Despite the fact he
actually has them.

The difference comes from the fact that the external command "createuser
foo" translates into "CREATE ROLE foo SUPERUSER CREATEDB CREATEROLE
INHERIT LOGIN;"

Is it wrong to wonder that a superuser should have createdb and
createrole by default ?

I'm working with PostgreSQL8.2.4 on Kubuntu 7.04. PostgreSQL was
compiled. Seems to be the same with 8.2.5 version.

Let's create a superuser named 'superdba' with the shell command:

$ LANGUAGE=en_US createuser superdba
Shall the new role be a superuser? (y/n) y
CREATE ROLE

$ LANGUAGE=en_US psql postgres postgres -c '\du'
List of roles
Role name | Superuser | Create role | Create DB | Connections | Member of
-----------+-----------+-------------+-----------+-------------+-----------
superdba | yes | yes | yes | no limit |

The superuser has Create Role and Create DB rights.

Now let's do the same with a SQL query:

postgres=# create role superdba3 superuser login;
CREATE ROLE
postgres=# \du
List of roles
Role name | Superuser | Create role | Create DB | Connections | Member of
-----------+-----------+-------------+-----------+-------------+-----------
superdba3 | yes | no | no | no limit |

Best regards,

--
Stéphane SCHILDKNECHT
Président de PostgreSQLFr
http://www.postgresqlfr.org

=?ISO-8859-1?Q?St=E9phane_Schildknecht?= <stephane(dot)schildknecht(at)postgresqlfr(dot)org> writes:
> It seems the shell command createuser and the SQL CREATE USER don't act
> the same way,

They aren't really claimed to. But the difference you point to is
irrelevant, since a superuser has createrole and createdb privilege
(and every other privilege) independently of what those columns say.

regards, tom lane

Le jeudi 13 septembre 2007, Tom Lane a écrit :
> =?ISO-8859-1?Q?St=E9phane_Schildknecht?=
<stephane(dot)schildknecht(at)postgresqlfr(dot)org> writes:
> > It seems the shell command createuser and the SQL CREATE USER don't act
> > the same way,
>
> They aren't really claimed to.

But the man say :
" createuser is a wrapper around the SQL command CREATE ROLE [create_role(7)].
There is no effective difference between creating users via this utility and
via other methods for accessing the server."

> But the difference you point to is
> irrelevant, since a superuser has createrole and createdb privilege
> (and every other privilege) independently of what those columns say.

It is right, but look at this scenario :

CREATE ROLE super SUPERUSER;
ALTER ROLE super NOSUPERUSER;

No RIGHT to CREATEDB.

If superuser is created using commandline, he can still CREATEDB after the
same ALTER ROLE

I think there is 2 options:

- change the manual and keep the actual method.
- don't stop asking privilege on createuser (it actually break after 'yes' to
superuser)

or do nothing...

--
Cédric Villemain
Administrateur de Base de Données
Cel: +33 (0)6 74 15 56 53
http://dalibo.com - http://dalibo.org

Le vendredi 14 septembre 2007, Cédric Villemain a écrit :
> Le jeudi 13 septembre 2007, Tom Lane a écrit :
> > =?ISO-8859-1?Q?St=E9phane_Schildknecht?=
>
> <stephane(dot)schildknecht(at)postgresqlfr(dot)org> writes:
> > > It seems the shell command createuser and the SQL CREATE USER don't act
> > > the same way,
> >
> > They aren't really claimed to.
>
> But the man say :
> " createuser is a wrapper around the SQL command CREATE ROLE
> [create_role(7)]. There is no effective difference between creating users
> via this utility and via other methods for accessing the server."
>
> > But the difference you point to is
> > irrelevant, since a superuser has createrole and createdb privilege
> > (and every other privilege) independently of what those columns say.
>

The superuser has no createrole and createdb privilege, he has superuser
privilege, which is enought to bypass createrole and createdb privilege.

There where no real answer about that.
What do we do ?

> It is right, but look at this scenario :
>
> CREATE ROLE super SUPERUSER;
> ALTER ROLE super NOSUPERUSER;
>
> No RIGHT to CREATEDB.
>
> If superuser is created using commandline, he can still CREATEDB after the
> same ALTER ROLE
>
> I think there is 2 options:
>
> - change the manual and keep the actual method.
> - don't stop asking privilege on createuser (it actually break after 'yes'
> to superuser)
>
> or do nothing...

--
Cédric Villemain
Administrateur de Base de Données
Cel: +33 (0)6 74 15 56 53
http://dalibo.com - http://dalibo.org

Cédric Villemain wrote:
> Le jeudi 13 septembre 2007, Tom Lane a écrit :
> > =?ISO-8859-1?Q?St=E9phane_Schildknecht?=
> <stephane(dot)schildknecht(at)postgresqlfr(dot)org> writes:
> > > It seems the shell command createuser and the SQL CREATE USER don't act
> > > the same way,
> >
> > They aren't really claimed to.
>
> But the man say :
> " createuser is a wrapper around the SQL command CREATE ROLE [create_role(7)].
> There is no effective difference between creating users via this utility and
> via other methods for accessing the server."

Note that CREATE ROLE behaves slightly differently from CREATE USER.

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

Alvaro Herrera a écrit :
> Cédric Villemain wrote:
>
>> Le jeudi 13 septembre 2007, Tom Lane a écrit :
>>
>>> =?ISO-8859-1?Q?St=E9phane_Schildknecht?=
>>>
>> <stephane(dot)schildknecht(at)postgresqlfr(dot)org> writes:
>>
>>>> It seems the shell command createuser and the SQL CREATE USER don't act
>>>> the same way,
>>>>
>>> They aren't really claimed to.
>>>
>> But the man say :
>> " createuser is a wrapper around the SQL command CREATE ROLE [create_role(7)].
>> There is no effective difference between creating users via this utility and
>> via other methods for accessing the server."
>>
>
> Note that CREATE ROLE behaves slightly differently from CREATE USER.
>
>
The result is exactly the same, here...

test=# create role testrole superuser;
CREATE ROLE
test=# \du
List of roles
Role name | Superuser | Create role | Create DB | Connections | Member of
-----------+-----------+-------------+-----------+-------------+-----------
testrole | yes | no | no | no limit |
test=# alter role testrole nosuperuser;
ALTER ROLE
test=# \du
List of roles
Role name | Superuser | Create role | Create DB | Connections | Member of
-----------+-----------+-------------+-----------+-------------+-----------
testrole | no | no | no | no limit |

So a superuser which has not been explicitly created with "createuser"
and "create DB" privileges will become a no privilege while losing the
status superuser.

If created with the Unix command createuser, he won't.

Best regards,

SAS