Createuser error could not connect to database postgres fatal role postgres does not exist

I’m a postgres novice.

I installed the postgres.app for mac. I was playing around with the psql commands and I accidentally dropped the postgres database. I don’t know what was in it.

I’m currently working on a tutorial: http://www.rosslaird.com/blog/building-a-project-with-mezzanine/

And I’m stuck at sudo -u postgres psql postgres

ERROR MESSAGE: psql: FATAL: role "postgres" does not exist

$ which psql

/Applications/Postgres.app/Contents/MacOS/bin/psql

This is what prints out of psql -l

                                List of databases
    Name    |   Owner    | Encoding | Collate | Ctype |     Access privileges     
------------+------------+----------+---------+-------+---------------------------
 user       | user       | UTF8     | en_US   | en_US | 
 template0  | user       | UTF8     | en_US   | en_US | =c/user                  +
            |            |          |         |       | user      =CTc/user      
 template1  | user       | UTF8     | en_US   | en_US | =c/user                  +
            |            |          |         |       | user      =CTc/user      
(3 rows)

So what are the steps I should take? Delete an everything related to psql and reinstall everything?

Thanks for the help guys!

This article helped me to solve same issue psql: FATAL: role “postgres” does not exist.

I am using mac, so I entered this command in terminal:

createuser -s postgres

And it worked for me.

answered May 12, 2021 at 16:03

This worked for me

createuser -s postgres

note: I’m using mac catalina

answered Oct 14, 2021 at 4:38

If you’re using docker, make sure you’re NOT using POSTGRES_USER=something_else, as this variable is used by the standard image to know the name of the PostgreSQL admin user (default as postgres).

In my case, I was using this variable with the intent to set another user to my specific database, but it ended up of course changing the main PostgreSQL user.

answered Jul 23, 2020 at 9:30

For m1 chips, if you have not installed postgresql package by homebrew, install it in terminal with:

brew install postgre

then create a username manually by:

/opt/homebrew/bin/createuser -s <username> 

your error is probably fixed; but if you occur the error

FATAL: database «databasename» does not exist

then you have to create your database manually by:

/opt/homebrew/bin/createdb -U <username>  <databasename>    

answered Jun 27, 2022 at 10:43

For what it is worth, i have ubuntu and many packages installed and it went in conflict with it.

For me the right answer was:

sudo -i -u postgres-xc
psql

answered Jan 11, 2017 at 15:37

If you are experiencing this problem right after running a docker container try destroying the container and recreating it. That solved it for me:

docker-compose down
docker-compose up --force-recreate

This should recreate the db with postgresuser as default user

answered Oct 27, 2021 at 7:16

If you are a MAC (M1) user and installed the Postgres using HomeBrew then follow these steps:

1. Check your Postgres location using which psql
2. then run the command /opt/homebrew/bin/createuser -s postgres if the output for the first command is /opt/homebrew/bin/psql

The idea is to create a user named ‘postgres’ using the Postgres installation location. So you may need to change the command based on the location of your Postgres.

answered Dec 14, 2022 at 6:02

On Ubuntu system, I purged the PostgreSQL and re-installed it. All the databases are restored.
This solved the problem for me.

Advice — Take the backup of the databases to be on the safer side.

answered Nov 20, 2018 at 5:52

Context

I am adding an answer for a case I have not seen here, which is an edge case if you have multiple users on the same machine and the user who is trying to use postgres services is not the user who installed postgres on the machine.

What I have tried

Among other similar commands, for me all these commands failed:

createuser -s [your username]
# createuser: error: connection to server on socket "/tmp/.s.PGSQL.5432" failed: FATAL:  role "[your username]" does not exist

createuser -s postgres
# createuser: error: connection to server on socket "/tmp/.s.PGSQL.5432" failed: FATAL:  role "[your username]" does not exist

sudo -u postgres createuser --superuser [your username]
# sudo: unknown user: postgres
# sudo: error initializing audit plugin sudoers_audit

psql -U postgres
# psql: error: connection to server on socket "/tmp/.s.PGSQL.5432" failed: FATAL:  role "postgres" does not exist

Reason

The reason is because neither postgres role nor [your username] (aka whoami on your command line) are in postgres.

Solution

In such edge case I had to first login with the user who installed postgres:

sudo su - [username that installed postgres]

And then create a role for my new user:

createuser -s [your username]

answered Jan 8 at 18:42

I became stuck on this issue having executed brew services stop postgresql the day prior.
The day following: brew services start postgresql would not work. This is because as is shown when you install using homebrew. postgresql uses a launchd … which loads when your computer is powered on.

resolution:
brew services start postgresql
Restart your computer.

answered Jun 26, 2017 at 12:33

The du command return:

Role name = postgres@implicit_files

And that command postgres=# password postgres return error:

ERROR: role «postgres» does not exist.

But that postgres=# password postgres@implicit_files run fine.

Also after sudo -u postgres createuser -s postgres the first variant also work.

answered Jun 28, 2018 at 8:32

answered May 2, 2022 at 6:56

level 1

level 2

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

level 1

asked Mar 8 ’13 at 19:31

Note that the error message does NOT talk about a missing database, it talks about a missing role. Later in the login process it might also stumble over the missing database.

But the first step is to check the missing role: What is the output within psql of the command du ? On my Ubuntu system the relevant line looks like this:

                              List of roles
 Role name |            Attributes             | Member of 
-----------+-----------------------------------+-----------
 postgres  | Superuser, Create role, Create DB | {}        

If there is not at least one role with superuser, then you have a problem

If there is one, you can use that to login. And looking at the output of your l command: The permissions for user on the template0 and template1 databases are the same as on my Ubuntu system for the superuser postgres. So I think your setup simple uses user as the superuser. So you could try this command to login:

sudo -u user psql user

If user is really the DB superuser you can create another DB superuser and a private, empty database for him:

CREATE USER postgres SUPERUSER;
CREATE DATABASE postgres WITH OWNER postgres;

But since your postgres.app setup does not seem to do this, you also should not. Simple adapt the tutorial.

answered Mar 9 ’13 at 10:13

The key is «I installed the postgres.app for mac.» This application sets up the local PostgresSQL installation with a database superuser whose role name is the same as your login (short) name.

When Postgres.app first starts up, it creates the $USER database,
which is the default database for psql when none is specified. The
default user is $USER, with no password.

Some scripts (e.g., a database backup created with pgdump on a Linux systsem) and tutorials will assume the superuser has the traditional role name of postgres.

You can make your local install look a bit more traditional and avoid these problems by doing a one time:

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

which will make those FATAL: role «postgres» does not exist go away.

answered Jul 23 ’13 at 14:25

For MAC:

1. Install Homebrew
2. brew install postgres
3. initdb /usr/local/var/postgres
4. /usr/local/Cellar/postgresql/<version>/bin/createuser -s postgres or /usr/local/opt/postgres/bin/createuser -s postgres which will just use the latest version.
5. start postgres server manually: pg_ctl -D /usr/local/var/postgres start

To start server at startup

• mkdir -p ~/Library/LaunchAgents
• ln -sfv /usr/local/opt/postgresql/*.plist ~/Library/LaunchAgents
• launchctl load ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist

Now, it is set up, login using psql -U postgres -h localhost or use PgAdmin for GUI.

By default user postgres will not have any login password.

Check this site for more articles like this: https://sites.google.com/site/nitinpasumarthy/blog/installingpostgresonmac

createuser postgres --interactive

or make a superuser postgresl just with

createuser postgres -s

answered Aug 10 ’17 at 20:10

First you need create a user:

sudo -u postgres createuser --superuser $USER

After you create a database:

sudo -u postgres createdb $USER

Change $USER to your system username.

You can see the the complete solution here.

answered Jul 29 ’15 at 22:54

Running this on the command line should fix it

/Applications/Postgres.app/Contents/Versions/9.4/bin/createdb <Mac OSX Username Here>

answered Feb 13 ’15 at 21:41

I needed to unset $PGUSER:

$ unset PGUSER
$ createuser -s postgres

answered Jan 2 at 6:54

Dropping the postgres database doesn’t really matter. This database is initially empty and its purpose is simply for the postgres user to have a kind of «home» to connect to, should it need one.

Still you may recreate it with the SQL command CREATE DATABASE postgres;

Note that the tutorial mentioned in the question is not written with postgres.app in mind.
Contrary to PostgreSQL for Unix in general, postgres.app tries to look like a normal application as opposed to a service that would be run by a dedicated postgres user having different privileges than your normal user. postgres.app is run and managed by your own account.

So instead of this command: sudo -u postgres psql -U postgres, it would be more in the spirit of postgres.app to just issue: psql, which automatically connects to a database matching your users’s name, and with a db account of the same name that happens to be superuser, so it can do anything permissions-wise.

answered Mar 8 ’13 at 20:53

For what it is worth, i have ubuntu and many packages installed and it went in conflict with it.

For me the right answer was:

sudo -i -u postgres-xc
psql

answered Jan 11 ’17 at 15:37

I don’t think that sudo is needed here because psql -l returns a list of databases. This tells me that initdb was run under the user’s current user, not under the postgres user.

You can just:

psql

And continue the tutorial.

I would suggest A.H’s general points of creating the postgres user and db because many applications may expect this to exist.

A brief explanation:

PostgreSQL will not run with administrative access to the operating system. Instead it runs with an ordinary user, and in order to support peer authentication (asking the OS who is trying to connect) it creates a user and db with the user that runs the initialization process. In this case it was your normal user.

answered Mar 9 ’13 at 14:36

This is the only one that fixed it for me :

createuser -s -U $USER

answered Mar 2 ’16 at 0:10

I became stuck on this issue having executed brew services stop postgresql the day prior.
The day following: brew services start postgresql would not work. This is because as is shown when you install using homebrew. postgresql uses a launchd … which loads when your computer is powered on.

resolution:
brew services start postgresql
Restart your computer.

answered Jun 26 ’17 at 12:33

The du command return:

Role name = postgres@implicit_files

And that command postgres=# password postgres return error:

ERROR: role «postgres» does not exist.

But that postgres=# password postgres@implicit_files run fine.

Also after sudo -u postgres createuser -s postgres the first variant also work.

answered Jun 28 at 8:32

JonasJonas

26.1k47 gold badges104 silver badges125 bronze badges

From http://www.postgresql.org/docs/8.1/static/tutorial-createdb.html

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.

I met the same problem, now I do it with

createdb -U postgres dbname

answered Jan 30, 2014 at 20:07