Rails and PostgreSQL: Role postgres does not exist

Question

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.

For the closely related problem where the DB user (= role) `postgres` has not been created, which can lead to the same error message, see this related question: http://stackoverflow.com/questions/11919391/postgresql-error-fatal-role-username-does-not-exist — Erwin Brandstetter, May 11 '15 at 06:50

score 294 · Answer 1 · edited Dec 26 '12 at 12:09

294

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.

edited Dec 26 '12 at 12:09

jibiel

8,175
7
51
74

answered Oct 08 '12 at 13:29

Chris Sherlock

3,112
2
13
9

8

I had to use `sudo su - postgres` before this command worked for me. Thanks! – Connor Leech Nov 08 '13 at 09:51
2

Don't know how my set-up came about but I had postgresql created without postgres user and role, myUser as only user, but refused to let me in on pretext that the database named myUser was inexistent! this unraveled part of the mayhem! – Jerome Aug 28 '14 at 19:12
@StuartNelson: This seems to be the solution to a common problem ([**same here**](http://stackoverflow.com/questions/11919391/postgresql-error-fatal-role-username-does-not-exist)). But it's obviously *not* the answer to this question. The OP clearly states that the role existed in his DB. – Erwin Brandstetter Sep 23 '14 at 20:51
Except that you are conflating roles with users. The user existed, the role did not - he says clearly that the *user* exists, not the role. – Chris Sherlock Sep 24 '14 at 21:36
The `-r` option isn't necessary with the `-s` option. Superuser includes the "Create role" attribute. – Dennis Mar 02 '15 at 14:36
Database `user` is an alias of `role` with login in Postgres. [Why did PostgreSQL merge users and groups into roles?](http://stackoverflow.com/a/8487886/939860) You are addressing a related, (very common) issue where the DB user (= role) `postgres` is missing, which is *not* the answer to this question and [also has been answered before](http://stackoverflow.com/a/11919677/939860). The OP clearly states in his comment to my answer that he only had to change the port to make it work. Also, ident authentication method is just *one* of multiple ways how PostgreSQL maintains database access. – Erwin Brandstetter May 11 '15 at 06:47
Fair point - I evidently didn't read the documentation very clearly. Sorry about that. – Chris Sherlock May 12 '15 at 07:39
please bump this to the top. best answer – user2954587 Apr 17 '20 at 01:09

Prem · Answer 2 · 2012-12-05T05:02:29.747

Recently i got this problem immediately after installing postgres. If it comes immediately after installation, you might be missing the default user, postgres. In that case, you can create default user postgres using below command.

createuser -s -U $USER

Ex: createuser -s -U $USER
enter your required role name: postgres
enter password for your the user:

It will prompt you to enter required database role name and password Once you complete the process, you can login to the postgres console using below command

psql -U 'your_database_name'

Ex: psql -U postgres
Here, You need to enter the password if you have given any, while creating the user.

Hope it helps :)

score 23 · Answer 3 · answered Jun 16 '13 at 16:06

23

I was on OSX 10.8, and everything I tried would give me the FATAL: role "USER" does not exist. Like many people said here, run createuser -s USER, but that gave me the same error. This finally worked for me:

$ sudo su
# su postgres
# createuser -s --username=postgres MYUSERNAME

The createuser -s --username=postgres creates a superuser (-s flag) by connecting as postgres (--username=postgres flag).

I see that your question has been answered, but I want to add this answer in for people using OSX trying to install PostgreSQL 9.2.4.

answered Jun 16 '13 at 16:06

Jack

5,264
7
34
43

2

You can shorten the first two lines to `sudo su postgres`. – 6ft Dan May 15 '14 at 02:25
This also was the solution for me on ubuntu, don't forget to update your database.yml to use the same username as above – crobicha Jul 20 '14 at 16:23

score 14 · Accepted Answer · answered Oct 23 '11 at 05:09

14

This message pops up, when the database user does not exist. Compare the manual here.
Multiple local databases cannot be the explanation. Roles are valid cluster-wide. The manual again:

Note that roles are defined at the database cluster level, and so are valid in all databases in the cluster.

You must be ending up in another database-cluster. That would be another server running on the same machine, listening to a different port. Or, more likely, on a different machine.

Could it be that the message comes, in fact, from the remote server?

answered Oct 23 '11 at 05:09

Erwin Brandstetter

605,456
145
1,078
1,228

That was it - I have been having difficulty getting postgresql to work period so I did a fresh install so I must have a couple of instance on here now. Changed the port in the database.yml file though and it all worked fine. Thanks for your help! – Adam Oct 23 '11 at 17:03
did not work even after cleaning up all images - `docker rmi $(docker images -q) --force`. but issue was on `volumes` i guess – prayagupa Oct 24 '19 at 23:05

score 10 · Answer 5 · answered Feb 25 '13 at 03:31

I met this issue right on when I first install the Heroku's POSTGRES.app thing. After one morning trial and error i think this one line of code solved problem. As describe earlier, this is because postgresql does not have default role the first time it is set up. And we need to set that.

sovanlandy=# CREATE ROLE postgres LOGIN;

You must log in to your respective psql console to use this psql command.

Also noted that, if you already created the role 'postgre' but still get permission errors, you need to alter with command:

sovanlandy=# ALTER ROLE postgres LOGIN;

Hope it helps!

score 5 · Answer 6 · answered Nov 21 '13 at 07:15

In the Heroku documentation; Getting started whit rails 4, they say:

You will also need to remove the username field in your database.yml if there is one so: In file config/database.yml remove: username: myapp

Then you just delete that line in "development:", if you don't pg tells to the database that works under role "myapp"

This line tells rails that the database myapp_development should be run under a role of myapp. Since you likely don’t have this role in your database we will remove it. With the line remove Rails will try to access the database as user who is currently logged into the computer.

Also remember to create the database for development:

$createdb myapp_development

Repleace "myapp" for your app name

Commenting out `username` in `config/database.yml` helped in my case with new rails/spree server setup. — Andrew Mykhalchuk, Feb 24 '23 at 11:19

Mayur · Answer 7 · 2020-02-28T10:48:23.187

The installation procedure creates a user account called postgres that is associated with the default Postgres role. In order to use Postgres, you can log into that account. But if not explicitly specified the rails app looks for a different role, more particularly the role having your unix username which might not be created in the postgres roles.

To overcome that, you can create a new role, first by switching over to the default role postgres which was created during installation

sudo -i -u postgres

After you are logged in to the postgres account, you can create a new user by the command:

createuser --interactive

This will prompt you with some choices and, based on your responses, execute the correct Postgres commands to create a user.

Pass over a role name and some permissions and the role is created, you can then migrate your db

score 2 · Answer 8 · answered Oct 23 '11 at 04:35

2

Could you have multiple local databases? Check your database.yml and make sure you are hitting the pg db that you want. Use rails console to confirm.

answered Oct 23 '11 at 04:35

Jonathan Julian

12,163
2
42
48

score 1 · Answer 9 · answered Mar 18 '14 at 12:43

1

My answer was much more simple. Just went to the db folder and deleted the id column, which I had tried to forcefully create, but which is actually created automagically. I also deleted the USERNAME in the database.yml file (under the config folder).

answered Mar 18 '14 at 12:43

maudulus

10,627
10
78
117

score 1 · Answer 10 · answered Jun 21 '15 at 20:51

In Ubuntu local user command prompt, but not root user, type

sudo -u postgres createuser username

username above should match the name indicated in the message "FATAL: role 'username' does not exist."

Enter password for username.

Then re-enter the command that generated role does not exist message.

score 0 · Answer 11 · answered Oct 21 '13 at 21:47

I ended up here after attempting to follow Ryan Bate's tutorial on deploying to AWS EC2 with rubber. Here is what happened for me: We created a new app using "

rails new blog -d postgresql

Obviosuly this creates a new app with pg as the database, but the database was not made yet. With sqlite, you just run rake db:migrate, however with pg you need to create the pg database first. Ryan did not do this step. The command is rake db:create:all, then we can run rake db:migrate

The second part is changing the database.yml file. The default for the username when the file is generated is 'appname'. However, chances are your role for postgresql admin is something different (at least it was for me). I changed it to my name (see above advice about creating a role name) and I was good to go.

Hope this helps.

score 0 · Answer 12 · edited Nov 06 '13 at 19:34

After a bunch of installing and uninstalling of Postgres, here's what now seems to work consistently for me with Os X Mavericks, Rails 4 and Ruby 2.

In the database.yml file, I change the default usernames to my computer's username which for me is just "admin".
In the command line I run rake db:create:all
Then I run rake db:migrate
When I run the rails server and check the local host it says "Welcome aboard".

score 0 · Answer 13 · answered Oct 09 '14 at 17:33

0

You might be able to workaround this by running initdb -U postgres -D /path/to/data or running it as user postgres, since it defaults to the current user. GL!

answered Oct 09 '14 at 17:33

rogerdpack

62,887
36
269
388

Rails and PostgreSQL: Role postgres does not exist

13 Answers13

Linked