Postgresql: FATAL: role does not exist

Question

I'm setting up Postgresql for use with a Rails app, but I do not seem to be able to connect to, or properly configure, the database (the errors I get after starting the Rails server are: ActiveRecord::NoDatabaseError and could not translate host name "MyProfile" to address: nodename nor servname provided, or not known).

I gather from the dozens of other questions on this topic that I need to switch to, or create the "MyComputer" role, however I've noticed that they all require using the psql command. When I run even just psql I, again, get the error FATAL: role "MyProfile" does not exist.

So far I've been following Heroku's instal process and am stuck here (or more accurately here, after the installation, where Heroku says that running psql -h localhost should work). Am I missing an obvious step here/doing something wrong?

I've also tried:

sudo su - MyProfile
variations of sudo -u postgres createuser owning_user

and a couple other commands in an effort to create this roll/user, but I can't seem to get done what I need to to resolve the issue.

EDIT
I've also run ps aux | grep postgres and it looks like all the PID's that are associated with anything postgres are running under "MyProfile" (assuming I'm reading it right). But the psql command still returns that the role does not exist. #sadface

EDIT 2
I just opened the Postgres App and clicked the "Open psql" button. It opened the Terminal, ran a command ('/Applications/Postgres.app/Contents/Versions/9.5/bin'/psql -p5432) and then gave me the same error (psql: FATAL: role "MyProfile" does not exist). Which perhaps suggests to me that it's an system issue, and not a Rails issue at all?

Edit 3
This is most certainly a pg issue, not a rails issue. I just uninstalled the app, reinstalled it using SQLite (yucks), ran the local server and got the test landing page to show up. So the problem appears to be with my local machine but not the app itself. Removed RoR tag, and still looking for answers from Postgres gurus on why the role issue persists :)

Answer 1

I ran into similar issues when setting a new Rails application with Postgresql. I got the following error messages below

FATAL:  role "promisepreston" does not exist
Couldn't create 'MyBlog_development' database. Please check your configuration.
rails aborted!
ActiveRecord::NoDatabaseError: FATAL:  role 

Caused by:
PG::ConnectionBad: FATAL:  role "promisepreston" does not exist

To solve this simply follow the solution below

First, we need to login to the postgres user account via the command line interface;

sudo su - postgres

Next, connect to the database server using the psql client, as the postgres role:

psql -U postgres

Welcome to psql 10.6, the PostgreSQL interactive terminal.

postgres@Preston-PC:~$ psql -U postgres
psql (10.6 (Ubuntu 10.6-0ubuntu0.18.04.1))
Type "help" for help

postgres=#

Next, connected with the psql client, we’ll create a role with our desired rolename that has the LOGIN attribute and our desired password, and that can create databases and manage roles (N/B: Please do not type this postgres=#, since it's a placeholder):

postgres=# create role rolename with createdb login password 'password1';

Note the required trailing semicolon ( ; ) at the end of the SQL statement. The single-quotes ( ‘ ‘ ) are not part of the password, but must enclose it.

Did it work? You can check using \du command (N/B: Please do not type this postgres=#, since it's a placeholder):

postgres=# \du

You can now run the command to create the database for your Rails application;

rails db:create

And then also run the command to migrate the database for your Rails application;

rails db:migrate

That's all.

I hope this helps

Answer 2

What a mess...one thing that I forgot to mention that probably would have been astronomically helpful to the community is that I'm taking over someone else's machine. There were lingering settings that just decided they weren't going to play nice.

At the end of the day, I got rid of Postgress.app, installed postgres with Homebrew, and the balance of what I needed was here: http://exponential.io/blog/2015/02/21/install-postgresql-on-mac-os-x-via-brew/

Specifically the line that saved me was createdb 'whoami' (see the actual post...the syntax is a bit different than what I just wrote because of stackoverflow formatting)...in retrospect it seems obvious, but it helped my current logged in user overcome the presets of the other legacy user by actually creating the database that the psql the whole setup was looking for.

Lesson learned!

Thanks for the help @max, helped me avoid another issue I was about to cause as well!

Answer 3

If you are using Postgres.app you should leave out the username and password from config/database.yml. Also that error is telling you that you have entered MyProfile not as the user for (or role in PG parlance) but as the host for the database connection (instead of localhost).

This is really all you need in your config/database.yml to get Postgres.app running:

default: &default
  adapter: postgresql
  encoding: unicode
  # For details on connection pooling, see rails configuration guide
  # http://guides.rubyonrails.org/configuring.html#database-pooling
  pool: <%= ENV.fetch("RAILS_MAX_THREADS") { 5 } %>

development:
  <<: *default
  database: myapp_development

test:
  <<: *default
  database: myapp_test

Its often a good idea to leave it stripped down and let the fool who absolutely has to have a password on his dev postgres server use the DATABASE_URL env var.