Ruby-on-rails – Rails and PostgreSQL: Role postgres does not exist

postgresqlrakeruby-on-rails

I have installed PostgreSQL on my Mac OS Lion, and am working on a rails app. I use RVM to keep everything separate from my other Rails apps.

For some reason when I try to migrate the db for the first time rake cannot find the postgres user. I get the error

 FATAL:  role "postgres" does not exist

I have pgAdmin so I can clearly see there is a postgres user in the DB – the admin account in fact – so I'm not sure what else to do.

I read somewhere about people having issues with PostgreSQL because of which path it was installed in, but then I don't think I would have gotten that far if it couldn't find the db.

Best Answer

Actually, for some unknown reason, I found the issue was actually because the postgresql role hadn't been created.

Try running:

createuser -s -r postgres

Note that roles are the way that PostgreSQL maintains database permissions. If there is no role for the postgres user, then it can't access anything. The createuser command is a thin wrapper around the commands CREATE USER, CREATE ROLE, etc.

Related Solutions

Linux – How to configure postgresql for the first time

The other answers were not completely satisfying to me. Here's what worked for postgresql-9.1 on Xubuntu 12.04.1 LTS.

Connect to the default database with user postgres:

sudo -u postgres psql template1
Set the password for user postgres, then exit psql (Ctrl-D):

ALTER USER postgres with encrypted password 'xxxxxxx';
Edit the pg_hba.conf file:

sudo vim /etc/postgresql/9.1/main/pg_hba.conf

and change "peer" to "md5" on the line concerning postgres:

local all postgres ~~peer~~ md5

To know what version of postgresql you are running, look for the version folder under /etc/postgresql. Also, you can use Nano or other editor instead of VIM.
Restart the database :

sudo /etc/init.d/postgresql restart

(Here you can check if it worked with psql -U postgres).
Create a user having the same name as you (to find it, you can type whoami):

sudo createuser -U postgres -d -e -E -l -P -r -s <my_name>

The options tell postgresql to create a user that can login, create databases, create new roles, is a superuser, and will have an encrypted password. The really important ones are -P -E, so that you're asked to type the password that will be encrypted, and -d so that you can do a createdb.

Beware of passwords: it will first ask you twice the new password (for the new user), repeated, and then once the postgres password (the one specified on step 2).
Again, edit the pg_hba.conf file (see step 3 above), and change "peer" to "md5" on the line concerning "all" other users:

local all all ~~peer~~ md5
Restart (like in step 4), and check that you can login without -U postgres:

psql template1

Note that if you do a mere psql, it will fail since it will try to connect you to a default database having the same name as you (i.e. whoami). template1 is the admin database that is here from the start.
Now createdb <dbname> should work.

Postgresql – Postgres: INSERT if does not exist already

Postgres 9.5 (released since 2016-01-07) offers an "upsert" command, also known as an ON CONFLICT clause to INSERT:

INSERT ... ON CONFLICT DO NOTHING/UPDATE

It solves many of the subtle problems you can run into when using concurrent operation, which some other answers propose.

Best Answer

Related Solutions

Linux – How to configure postgresql for the first time

Postgresql – Postgres: INSERT if does not exist already

Related Topic