Cloud9 postgres

Question

I am trying to set up a postgres database in a Rails app in Cloud9.

I have followed the instructions here: https://docs.c9.io/setting_up_postgresql.html and set up a database called cc_database.

My database.yml file looks like this:

development:
  adapter: postgresql
  encoding: SQL_ASCII
  database: cc_database
  pool: 5
  username: postgres
  password: password

When I run rake db:setup I get the following error:

 PG::ConnectionBad: FATAL:  Peer authentication failed for user "postgres"

I am quite new to all this, so any advice would be much appreciated.

Answer 1

Do the following steps:

1. Create a new username and password for postgresql on cloud9:

   $ sudo service postgresql start
   $ sudo sudo -u postgres psql
   postgres=# CREATE USER username SUPERUSER PASSWORD 'password';
   postgres=# \q
   

2. Create ENV variables on cloud9:

   $ echo "export USERNAME=username" >> ~/.profile
   $ echo "export PASSWORD=password" >> ~/.profile
   $ source ~/.profile
   

   My database.yml for rails 4.2.0 on cloud9:

   default: &default
     adapter: postgresql
     encoding: unicode
     pool: 5
     username: <%= ENV['USERNAME'] %>
     password: <%= ENV['PASSWORD'] %>
     host:     <%= ENV['IP'] %>
   
   development:
     <<: *default
     database: sample_app_development
   
   test:
     <<: *default
     database: sample_app_test
   
   production:
     <<: *default
     database: sample_app_production
   

3. Include the gem pg in Gemfile and install:

   gem 'pg', '~> 0.18.2'

   $ bundle install
   

4. Update template1 postgresql for database.yml on cloud9:

   postgres=# UPDATE pg_database SET datistemplate = FALSE WHERE datname = 'template1';
   postgres=# DROP DATABASE template1;
   postgres=# CREATE DATABASE template1 WITH TEMPLATE = template0 ENCODING = 'UNICODE';
   postgres=# UPDATE pg_database SET datistemplate = TRUE WHERE datname = 'template1';
   postgres=# \c template1
   postgres=# VACUUM FREEZE;
   postgres=# \q
   

5. From command line run:

   bundle exec rake db:create
   

Answer 2

The postgresql in cloud9 is setup to authenticate with peer when localhost connection. So the quickly resolution is change the user in your database.yaml to current user. In my case the name of user is ubuntu. To see your current user use the command

$ echo $USER

So a list of command in terminal is .

$ sudo su - postgres
$ createuser ubuntu -dslP
$ Enter password for new role: **same password from your yaml file**
$ Enter it again:

Set your yaml file like this

development:
  adapter: postgresql
  encoding: SQL_ASCII
  database: cc_database
  pool: 5
  username: ubuntu
  password: password

Now you can run

rake db:create
rake db:migrate