Postgres: psql: FATAL: role "postgres" does not exist

Question

I have been using PostgreSQL for a couple of days and it has been working fine. I have been using it through both the default postgres database user as well as another user with permissions.

In the middle of the day today (after everything had been working fine) it stopped working and I could no longer get back into the database. I would try: psql and it would come up with psql: FATAL: role "postgres" does not exist.

Similarly, if I tried from my other user, psql -d postgres it would come up with psql: FATAL: role "otheruser" does not exist. (Note that I have a database named postgres and it has been working up until now).

UPDATE

It appears that one of the computers accessing the server crashed and may have somehow deleted all of the users of the database. I will try a fresh reinstall.

score 0 · Answer 1 · edited May 23 '17 at 12:32

0

Roles postgres and other user might be dropped. If you have at least one user with superuser permissions you can create those roles again. Similar question referred here

edited May 23 '17 at 12:32

Community

1
1

answered Jul 16 '15 at 21:34

Shreyas Chavan

1,079
1
7
17

Every time I try `createuser --interactive` it only comes back up with the same FATAL error. – Marcus Gladir Jul 16 '15 at 21:37
Do `\du` in psql console and paste the output here please – Shreyas Chavan Jul 16 '15 at 21:59
I see. If you cant get into the psql console and cant run `createuser` or `createdb` that means probably you dont have any superuser roles left (or any roles for that matter). TBH I dont know what to do in this case. Try posting your question to [dba](http://dba.stackexchange.com/) . You will get better results there – Shreyas Chavan Jul 16 '15 at 22:18

score 0 · Answer 2 · answered Jul 17 '15 at 10:57

To check what users exist without being able to log in with psql, you can use the following method:

stop postgres
restart it in single user mode (specifying the data directory)
query the pg_user global table
restart postgres normally

Example:

$ sudo -i -u postgres
...
postgres@u64:/usr/local/pg95a1$ bin/postgres --single -D $PWD/data

PostgreSQL stand-alone backend 9.5alpha1
backend> select usename from pg_user;
     1: usename (typeid = 19, len = 64, typmod = -1, byval = f)
    ----
     1: usename = "postgres"    (typeid = 19, len = 64, typmod = -1, byval = f)
    ----
     1: usename = "daniel"  (typeid = 19, len = 64, typmod = -1, byval = f)
    ----
     1: usename = "joe" (typeid = 19, len = 64, typmod = -1, byval = f)
    ----
     1: usename = "bob" (typeid = 19, len = 64, typmod = -1, byval = f)
    ----

ctrlD to terminate the single-user session.

Postgres: psql: FATAL: role "postgres" does not exist

2 Answers2