Rails & Postgresql on AWS Cloud 9 : role "ec2-user" does not exist

Question

I am trying to get my Rails environment up and running with a Postgres database using AWS Cloud9 and have run into a problem when trying run rails db:migrate.

Initially I created the project by running:

1. rails new app_name -d postgresql
2. bundle install

Bundler had a problem finding gem 'pg' so I ran:

1. sudo yum install postgresql-devel
2. sudo yum install postgresql-server
3. sudo postgresql initdb
4. sudo service postgresql start

The server fired up fine afterwards and I thought all was well until running rails db:migrate which returned the error:

PG::ConnectionBad: FATAL: role "ec2-user" does not exist

I am unsure how to fix this.

It has been suggested that I may need to get into my psql shell and alter or create a new role, but I'm unsure how to alter the ec2-user.

It has also been suggested that my pg_hba.conf file may need some alterations. I have the path to that file, but am not sure how to edit it or if that's something that I really want to do.

Any suggestions? I'm including my database.yml below:

default: &default
  adapter: postgresql
  encoding: unicode
  pool: <%= ENV.fetch("RAILS_MAX_THREADS") { 5 } %>

development:
  <<: *default
  database: my_app_development

test:
  <<: *default
  database: my_app_test

production:
  <<: *default
  database: my_app_production
  username: my_app
  password: <%= ENV['MY_APP_DATABASE_PASSWORD'] %>

Endure

When you log into an ec2 instance, AWS brings you in as ec2-user. You are executing the rails db:migrate command as that user. PG cannot find that user in the DB. I believe you can create that user in the DB as you started here It has been suggested that I may need to get into my psql shell and alter or create a new role or I believe you can create a new linux user that matches the username in the DB. If I could recall the exact steps, I would have posted the answer, but I cannot remember them. – Razid

@NicholasMartinez ok, that makes sense I think. So I would need to get into psql and create a user named "ec2-user"? And..if AWS "brings you in as ec2-user", wouldn't there be a password attached to that user as well? How would I find that? Much appreciated Nicholas. – Endure

You can run something like this in your console to create the user sudo -u postgres createuser -s ec2-user. – Razid

Hmm.. that gave me createuser: creation of new role failed: ERROR: role "ec2-user" already exists I wonder what the conflict is. – Endure

Give this one a shot rake db:migrate RAILS_ENV=production – Razid

So I'm not sure what happened. I ran rake db:migrate RAILS_ENV=development and the migration went through successfully. BUT when I run rake db:migrate RAILS_ENV=production i get a peer authentication error. I think that I might just need to create a user/role for veggie_app. Thanks again for your help. I can hopefully figure it out from here.... maybe. At least everything is working fine in development : ) – Endure

Sounds great. Good luck. – Razid

Just curious if you got it figured out. – Razid

Yeah. Thanks again for the help. It was a pretty obvious oversight on my part. The Peer authentication error (and probably the previous original errors) came from the password & username being set for the production db in my database.yml file. I removed those for now and all is well. – Endure

STEP 3 for me was: sudo service postgresql initdb – Sarraute

Answer 1

Every user of psql also needs a corresponding database that matches their name.

At the bash command line:

sudo -u postgres createuser -s ec2-user
sudo -u postgres createdb ec2-user

The above should allow your user to access psql, but it won't let rails make migrations yet. You'll have to do the following first:

sudo su postgres
psql
ALTER USER "ec2-user" WITH SUPERUSER;
\q
exit

I put that together quite quickly so let me know if you run into any problems.

If your config/database.yml is using a different login user to your bash user, you should repeat all above steps for that user as well.

Lastly, although you are on Cloud9, this is just a simple Rails/Postgres issue, rather than an AWS issue.

Chauchaucer

Thanks for the info. I had already managed to get it sorted out, but I believe this is similar to what I ended up doing. And to add to the above answer, from what I gather, it is probably best to not to create the db for ec2-user (the root user) but instead create a new role in in AWS/IAM settings and create the project with that role (?)... – Endure

Yep, you are right. Root should really be used mainly for IAM stuff - Users with specific permissions should be used when creating and working on AWS resources. – Chauchaucer