Createuser: could not connect to database postgres: FATAL: role “tom” does not exist

Question

I'm trying to set up Postgres for the first time, and I need to create a user with permissions to read and create databases. However, when I use:

createuser username

in my terminal I get the following message:

createuser: could not connect to database postgres: FATAL: role "tom" does not exist

Tom is my Ubuntu user account that I'm logged into right now. I'm trying to create a username of "postgres" then do a psql -U psql template1 so I can create a database and assign an owner to it for my Rails app.

Answer 1

You mentioned Ubuntu so I'm going to guess you installed the PostgreSQL packages from Ubuntu through apt.

If so, the postgres PostgreSQL user account already exists and is configured to be accessible via peer authentication for unix sockets in pg_hba.conf. You get to it by running commands as the postgres unix user, eg:

sudo -u postgres createuser owning_user
sudo -u postgres createdb -O owning_user dbname

This is all in the Ubuntu PostgreSQL documentation that's the first Google hit for "Ubuntu PostgreSQL" and is covered in numerous Stack Overflow questions.

(You've made this question a lot harder to answer by omitting details like the OS and version you're on, how you installed PostgreSQL, etc.)

Answer 2

See git gist with instructions here

Run this:

 sudo -u postgres psql

OR

psql -U postgres

in your terminal to get into postgres

NB: If you're on a Mac and both of the commands above failed jump to the section about Mac below

postgres=#

Run

CREATE USER new_username;

Note: Replace new_username with the user you want to create, in your case that will be tom.

postgres=# CREATE USER new_username;
CREATE ROLE

Since you want that user to be able to create a DB, you need to alter the role to superuser

postgres=# ALTER USER new_username SUPERUSER CREATEDB;
ALTER ROLE

To confirm, everything was successful,

postgres=# \du
                         List of roles
 Role name |                   Attributes                   | Member of 
-----------+------------------------------------------------+-----------
new_username     | Superuser, Create DB                           | {}
postgres         | Superuser, Create role, Create DB, Replication | {}
root             | Superuser, Create role, Create DB              | {}

postgres=# 

Update/Modification (For Mac):

I recently encountered a similar error on my Mac:

psql: FATAL: role "postgres" does not exist

This was because my installation was setup with a database superuser whose role name is the same as your login (short) name.

But some linux scripts assume the superuser has the traditional role name of postgres

How did I resolve this?

If you installed with homebrew run:

/usr/local/opt/postgres/bin/createuser -s postgres

If you're using a specific version of postgres, say 10.5 then run:

/usr/local/Cellar/postgresql/10.5/bin/createuser -s postgres

OR:

/usr/local/Cellar/postgresql/10.5/bin/createuser -s new_username

OR:

/usr/local/opt/postgresql@11/bin/createuser -s postgres

If you installed with postgres.app for Mac run:

/Applications/Postgres.app/Contents/Versions/10.5/bin/createuser -s postgres

P.S: replace 10.5 with your PostgreSQL version

Answer 3

sudo -u postgres createuser -s tom 

this should help you as this will happen if the administrator has not created a PostgreSQL user account for you. It could also be that you were assigned a PostgreSQL user name that is different from your operating system user name, in that case you need to use the -U switch.

Answer 4

1- Login as default PostgreSQL user (postgres)

sudo -u postgres -i

2- As postgres user. Add a new database user using the createuser command

[postgres]$ createuser --interactive

3-exit

[postgres]$ exit

Answer 5

Your error is posted in the official documentation. You can read this article.

I have copied the reason for you (and hyperlinked the URLs) from that article:

This will happen if the administrator has not created a PostgreSQL user account for you. (PostgreSQL user accounts are distinct from operating system user accounts.) If you are the administrator, see Chapter 20 for help creating accounts. You will need to become the operating system user under which PostgreSQL was installed (usually postgres) to create the first user account. It could also be that you were assigned a PostgreSQL user name that is different from your operating system user name; in that case you need to use the -U switch or set the PGUSER environment variable to specify your PostgreSQL user name

For your purposes, you can do:

1) Create a PostgreSQL user account:

sudo -u postgres createuser tom -d -P

(the -P option to set a password; the -d option for allowing the creation of database for your username 'tom'. Note that 'tom' is your operating system username. That way, you can execute PostgreSQL commands without sudoing.)

2) Now you should be able to execute createdb and other PostgreSQL commands.

Answer 6

I had the same issue, i just do this

sudo su - postgres

createuser odoo -U postgres -dRSP #P for password (odoo or user name that you want o give the postgres access)

Answer 7

On Windows use:

C:\PostgreSQL\pg10\bin>createuser -U postgres --pwprompt <USER>

Add --superuser or --createdb as appropriate.

See https://www.postgresql.org/docs/current/static/app-createuser.html for further options.

Answer 8

If you don't want to change the authentication method (ident) and mess with pg_hba.conf use this:

First login as the default user

 sudo su - postgres

then access psql and create a user with the same name as the one you are login in

postgres=# CREATE USER userOS WITH PASSWORD 'garbage' CREATEDB;

you can verify your user with the corresponding roles with

postgres=#  \du

Afer this you can create your database and verify it with

psql -d dbName
\l
\q

Answer 9

You need to first run initdb. It will create the database cluster and the initial setup

See How to configure postgresql for the first time? and http://www.postgresql.org/docs/8.4/static/app-initdb.html