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