Postgres Error: Role <username> doesn't exist, role <username> already exists

Question

I am having a serious issue trying to connect to my postgres database. I run 'psql' then I get this error "psql: FATAL: role "" does not exist". Then I log into postgres using psql -U postgres, tried creating a role with my username, then I get this error "role already exists". I searched all over for a solution. Does anyone know what's going on? I'm on Windows 10.

Maybe a case difference? psql parameters are case-sensitive, but the name in a CREATE ROLE statement is folded to lowercase (unless you double-quote it). — Nick Barnes
Could you quote the exact psql command line you tried and the exact CREATE ROLE statement you ran? — Laurenz Albe
I have the exact same issue. sudo -u postgres createuser jonny returns createuser: creation of new role failed: ERROR: role "jonny" already exists but psql returns psql: FATAL: role "jonny" does not exist It is SCHRODINGERs role. Postgres is a trash system, it doesn't even know if a role exists or doesn't. — Jonathan

Jonathan Jonathan · Accepted Answer · 2019-06-19T01:11:19

You may have two versions of postgres installed. At least, on Ubuntu, that's the problem I had. I had to (warning this may delete datbases, I do not know: sudo apt remove postgres\*) which will select all versions of Postgres and uninstall them.

On Windows you'd verify no other postgres servers are running after uninstalling with the graphical interface.

Then reinstall the correct version of Postgres (for me it was 9.4 for Odoo 11)

sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt/ $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
sudo apt-get update
sudo apt-get install postgresql-9.4

Then, I did: (sudo/su is required only for POSIX systems)

sudo su - postgres -c "createuser -s $USER"
sudo -u postgres psql -c "ALTER USER $USER WITH SUPERUSER;"
sudo -u postgres psql -c "GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO $USER;"
sudo -u postgres psql -c "GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO $USER;"
sudo -u postgres psql -c "CREATE DATABASE $USER;"
psql

And if you want a backup user

sudo su - postgres -c "createuser -s backup"
sudo -u postgres psql -c "GRANT SELECT ON ALL TABLES IN SCHEMA public TO backup;"
sudo -u postgres psql -c "ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO backup;"

Alternatively you could try to use pgadmin3, but I found that interface to be difficult

Postgres Error: Role <username> doesn't exist, role <username> already exists

1 Answers