PostgreSQL and Rails with Puppet problems

[embario]

Hi there,

I'm part of the Boulder CFA Brigade, and we are interested in using Trailsy for the City of Boulder with OpenTrails Data of the trails in Boulder County (and beyond).

First off, nice work! The app looks really nice, and it's great to have separation of concerns with client and backend. My problem however, is about configuring a postgreSQL database on local setup. I can't seem to connect to the database from the development config under the database.yml file. Here's my config:

development:
  adapter: postgis
  encoding: unicode
  database: trails_db
  pool: 5
  username: trails_login
  password: password
  schema_search_path: "public,postgis"
  postgis_extension: true

It should be said that I am currently using a Vagrant virtual machine to bootstrap the provisioning of nginx, PostgreSQL, Rails, Ruby, and other such stuff so that future development on Trailsy can be done within an encapsulated development environment. That's a pull request in the future.

Here's the problem I run into: when I go to run any of the rake commands to migrate/create seed/create DB, I get a "Peer Authentication Failed for "trails_login" error. I know this is outside of the project scope because it has everything to do with how the DB is set up, but I was wondering if anyone here had expertise on setting up PostgreSQL properly with Rails.

vagrant@precise64:/vagrant$ rake db:seed
rake aborted!
PG::ConnectionBad: FATAL:  Peer authentication failed for user "trails_login"
/home/vagrant/.rvm/gems/ruby-2.0.0-p481/gems/activerecord-4.0.0/lib/active_record/connection_adapters/postgresql_adapter.rb:825:in `initialize'
/home/vagrant/.rvm/gems/ruby-2.0.0-p481/gems/activerecord-4.0.0/lib/active_record/connection_adapters/postgresql_adapter.rb:825:in `new'
/home/vagrant/.rvm/gems/ruby-2.0.0-p481/gems/activerecord-4.0.0/lib/active_record/connection_adapters/postgresql_adapter.rb:825:in `connect'
/home/vagrant/.rvm/gems/ruby-2.0.0-p481/gems/activerecord-4.0.0/lib/active_record/connection_adapters/postgresql_adapter.rb:542:in `initialize'
/home/vagrant/.rvm/gems/ruby-2.0.0-p481/gems/activerecord-postgis-adapter-2.2.0/lib/active_record/connection_adapters/postgis_adapter/main_adapter.rb:7:in `initialize'
/home/vagrant/.rvm/gems/ruby-2.0.0-p481/gems/activerecord-postgis-adapter-2.2.0/lib/active_record/connection_adapters/postgis_adapter/create_connection.rb:33:in `new'
/home/vagrant/.rvm/gems/ruby-2.0.0-p481/gems/activerecord-postgis-adapter-2.2.0/lib/active_record/connection_adapters/postgis_adapter/create_connection.rb:33:in `postgis_connection'
/home/vagrant/.rvm/gems/ruby-2.0.0-p481/gems/activerecord-4.0.0/lib/active_record/connection_adapters/abstract/connection_pool.rb:440:in `new_connection'
/home/vagrant/.rvm/gems/ruby-2.0.0-p481/gems/rgeo-activerecord-1.2.0/lib/rgeo/active_record/ar_factory_settings.rb:138:in `new_connection'
/home/vagrant/.rvm/gems/ruby-2.0.0-p481/gems/activerecord-4.0.0/lib/active_record/connection_adapters/abstract/connection_pool.rb:450:in `checkout_new_connection'
/home/vagrant/.rvm/gems/ruby-2.0.0-p481/gems/activerecord-4.0.0/lib/active_record/connection_adapters/abstract/connection_pool.rb:421:in `acquire_connection'
/home/vagrant/.rvm/gems/ruby-2.0.0-p481/gems/activerecord-4.0.0/lib/active_record/connection_adapters/abstract/connection_pool.rb:356:in `block in checkout'
/home/vagrant/.rvm/gems/ruby-2.0.0-p481/gems/activerecord-4.0.0/lib/active_record/connection_adapters/abstract/connection_pool.rb:355:in `checkout'
/home/vagrant/.rvm/gems/ruby-2.0.0-p481/gems/activerecord-4.0.0/lib/active_record/connection_adapters/abstract/connection_pool.rb:265:in `block in connection'
/home/vagrant/.rvm/gems/ruby-2.0.0-p481/gems/activerecord-4.0.0/lib/active_record/connection_adapters/abstract/connection_pool.rb:264:in `connection'
/home/vagrant/.rvm/gems/ruby-2.0.0-p481/gems/activerecord-4.0.0/lib/active_record/connection_adapters/abstract/connection_pool.rb:546:in `retrieve_connection'
/home/vagrant/.rvm/gems/ruby-2.0.0-p481/gems/activerecord-4.0.0/lib/active_record/connection_handling.rb:79:in `retrieve_connection'
/home/vagrant/.rvm/gems/ruby-2.0.0-p481/gems/activerecord-4.0.0/lib/active_record/connection_handling.rb:53:in `connection'
/home/vagrant/.rvm/gems/ruby-2.0.0-p481/gems/activerecord-4.0.0/lib/active_record/migration.rb:863:in `initialize'
/home/vagrant/.rvm/gems/ruby-2.0.0-p481/gems/activerecord-4.0.0/lib/active_record/migration.rb:779:in `new'
/home/vagrant/.rvm/gems/ruby-2.0.0-p481/gems/activerecord-4.0.0/lib/active_record/migration.rb:779:in `open'
/home/vagrant/.rvm/gems/ruby-2.0.0-p481/gems/activerecord-4.0.0/lib/active_record/railties/databases.rake:164:in `block (2 levels) in <top (required)>'
/home/vagrant/.rvm/gems/ruby-2.0.0-p481/gems/activerecord-4.0.0/lib/active_record/railties/databases.rake:180:in `block (2 levels) in <top (required)>'
/home/vagrant/.rvm/gems/ruby-2.0.0-p481/bin/ruby_executable_hooks:15:in `eval'
/home/vagrant/.rvm/gems/ruby-2.0.0-p481/bin/ruby_executable_hooks:15:in `<main>'
Tasks: TOP => db:abort_if_pending_migrations
(See full trace by running task with --trace)

If anyone has knowledge in Vagrant/Puppet, that would be really helpful, too. Here's the part of my manifest dealing with PostgreSQL config:

#PostgreSQL
class { "postgresql::server":
  listen_addresses => "*"
}

postgresql::server::db { 'trails_db':
  user     => 'trails_login',
  password => postgresql_password('trails_login', 'password'),
}

include postgresql::server::postgis

postgresql::server::pg_hba_rule { 'allow application network to access app database':
  description => "Open up postgresql for access from 192.168.50.5/24",
  type => 'host',
  database => 'trails_db',
  user => 'trails_login',
  address => 'localhost',
  auth_method => 'md5',
}

Thanks in advance!
Mario B.

[monfresh]

Hi Mario. To use Postgres with Rails on a Linux machine, follow the instructions here:
https://github.com/codeforamerica/howto/blob/master/PostgreSQL.md#linux

If you're using Puppet, I think it automatically takes care of the pg_hba.confsettings in the aforementioned link. Here is an example Vagrant setup for Postgres (although without postgis stuff):
https://github.com/codeforamerica/ohana-api-dev-box/blob/master/puppet/manifests/default.pp#L79-113