PostgreSQL can't create user

Question

Issue: I can create users or databases from the shell (bash, OSX) but not postgres cli. From bash I get no confirmation if successful.

If I try to CREATE ROLE in psql then I get no response and it doesn't generate any error. If I try to createuser from bash then if successful it reports back nothing, if unsuccessful then it does generate the error: "role username already exists".

Example:

Yunti-# CREATE ROLE testuser
Yunti-# \du
                             List of roles
 Role name |                   Attributes                   | Member of 
-----------+------------------------------------------------+-----------
 Yunti     | Superuser, Create role, Create DB, Replication | {}
 anything  |                                                | {}
 monkey    |                                                | {}

Yunti-# CREATE DATABASE testdb
Yunti-# \l
                              List of databases
   Name    | Owner | Encoding |   Collate   |    Ctype    | Access privileges 
-----------+-------+----------+-------------+-------------+-------------------
 Yunti     | Yunti | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 postgres  | Yunti | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0 | Yunti | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/Yunti         +
           |       |          |             |             | Yunti=CTc/Yunti
 template1 | Yunti | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/Yunti         +
           |       |          |             |             | Yunti=CTc/Yunti
 test      | Yunti | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 test5     | Yunti | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
(6 rows)

Yunti-#

A similar thing happens when using createdb.

How can I create users and databases in postgres cli? And is this normal to get no response to most postgres commands in bash?

Info: users and their privileges:

Yunti-# \du
                             List of roles
 Role name |                   Attributes                   | Member of 
-----------+------------------------------------------------+-----------
 Yunti     | Superuser, Create role, Create DB, Replication | {}
 anything  |                                                | {}
 monkey    |                                                | {}

Thanks it was as simple as that, needed the ; (must do less python and more sql/javascript for ; practice ) — Yunti, Aug 27 '15 at 11:47

score 31 · Accepted Answer · 2015-08-27T11:51:20.643

Your statements are not executed, because you don't terminate them properly using a ;.

Quote from the manual:

A command is composed of a sequence of tokens, terminated by a semicolon (";").

And in the manual for psql:

At the prompt, the user can type in SQL commands. Ordinarily, input lines are sent to the server when a command-terminating semicolon is reached. An end of line does not terminate a command. Thus commands can be spread over several lines for clarity. If the command was sent and executed without error, the results of the command are displayed on the screen.

If you do that, you get an output like this:

psql (9.4.4)
Type "help" for help.

postgres=# CREATE ROLE testuser;
CREATE ROLE  ---<<<< this tells you the statement was executed

postgres=#

score 0 · Answer 2 · answered Aug 21 '15 at 19:55

0

I dont know if the postgres client is using other commands in OSX as it is in Linux but I assume it is the same.

This docs link shows some options for the postgres client:

It seems like "\l" lists the databases while the option you would like to see is roles and their access which is "\du".

When creating a database from within the client you should get a response in the form of "CREATING DATABASE". Maybe you are having some sort of syntax error?

I don't think users is created elsewhere.

I hope this solves some of your problems.

answered Aug 21 '15 at 19:55

cervyvin

1,533
2
9
9

Thanks \qu helps to confirm what users there are (you are right \l is the databases). I'm still having issues creating users though. If I create users from the shell (bash) via createuser, this works but gives no confirmation if created (only if an error). If I do this via postgres cli then createuser does not work (and give no error message) and CREATE ROLE name - doesn't work either (and give no error message either); (Note there is not default postgres role/user created mentioned elsewhere - is this just on Linux - the default user on OSX seems to be the shell user) – Yunti Aug 23 '15 at 10:36
Updated my question to be clearer - still can't create users or databases as same issue on 2 different computers. – Yunti Aug 27 '15 at 11:39

PostgreSQL can't create user

2 Answers2