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.

Headrick

Are you really trying to create a 'postgres' user? Or just a user for your Rails app? Normally the 'postgres' user already exists and the problem is just connecting to it in order to do what you need. – Hyponitrite

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.)

Hoebart

well after creating owning_user, How do yo connect with this user? I mean to connect default user i use , sudo -u postgres psql postgres . same way how to connect owning_user? – Immemorial

Three options. (a) Set a password for that user and edit pg_hba.conf to use md5 auth for that user (see the manual); (b) Create an OS user by the same name and keep on using peer auth; or (c) more advanced, create a pg_ident.conf mapping to allow your OS user to log in as the new user. – Hoebart

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

Weylin

This worked to get into postgres: psql -U postgres – Specter

In case someone faces the same issue. The capital words like CREATE USER or ALTER USER must (!) be capital in psql, unlike in any database IDE. – Saury

Thanks, I fixed a bug with a backup with this – Traditional

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.

Analects

Answer 4

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.

Catnip

Answer 5

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

Inexact

Answer 6

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

Preliminary

Answer 7

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)

Dorthydortmund

Answer 8

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.

Hyponitrite

Answer 9

For Linux, you can activate SUPERUSER to enable to create databases like this:

1. Go to terminal/shell

2. Enter inside postgres using sudo -u postgres -i

Now your terminal will be like "postgres@anish-Latitude-E7450:~$"

3. Enter postgres terminal using psql

Now your terminal will be like "postgres=#"

4. Enter alter user [postgres_user_name] createdb;

Now the user will be having access to create db.

5. Exit psql using '\q'

6. Logout postgres using exit

Hope this helps you.

Hypothermal

Answer 10

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

Absorb

This is completely incorrect. If the user hadn't run initdb they wouldn't have a database server running and accepting connections in order to produce the error message reported. Please remove this misleading answer. (BTW, in future, please link to /docs/current/static/ to avoid stale links accumulating) – Hoebart