Thread: 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 rolesRole name | Superuser | Createrole | 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 rolesRole 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
Stéphane Schildknecht <stephane.schildknecht@postgresqlfr.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 : > Stéphane Schildknecht <stephane.schildknecht@postgresqlfr.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 : > > Stéphane Schildknecht > > <stephane.schildknecht@postgresqlfr.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 : > > Stéphane Schildknecht > <stephane.schildknecht@postgresqlfr.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 : >> >>> Stéphane Schildknecht >>> >> <stephane.schildknecht@postgresqlfr.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 rolesRole name | Superuser | Create role | Create DB | Connections | Memberof -----------+-----------+-------------+-----------+-------------+----------- testrole | yes | no | no | no limit | test=# alter role testrole nosuperuser; ALTER ROLE test=# \du List of rolesRole name | Superuser | Create role | Create DB | Connections | Memberof -----------+-----------+-------------+-----------+-------------+----------- 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