1

I'm attempting to get a vagrant environment up to run a Rails application that connects to a remote Postgres database hosted on Heroku. Running the app from my host machine (Mac OS X) works just fine, but fails on a fresh Ubuntu installation in my vagrant box, even though they're pointed to the same source directory. Here's what I get when attempting to start the web server:

00:02:22 web.1  | Connecting to database specified by database.yml
00:02:22 web.1  | /opt/ruby/lib/ruby/gems/2.0.0/gems/activerecord-3.2.13/lib/active_record/connection_adapters/postgresql_adapter.rb:1216:in `initialize': FATAL:  no pg_hba.conf entry for host "207.172.212.123", user "user", database "database", SSL off (PG::ConnectionBad)
00:02:22 web.1  |   from /opt/ruby/lib/ruby/gems/2.0.0/gems/activerecord-3.2.13/lib/active_record/connection_adapters/postgresql_adapter.rb:1216:in `new'
00:02:22 web.1  |   from /opt/ruby/lib/ruby/gems/2.0.0/gems/activerecord-3.2.13/lib/active_record/connection_adapters/postgresql_adapter.rb:1216:in `connect'
00:02:22 web.1  |   from /opt/ruby/lib/ruby/gems/2.0.0/gems/activerecord-3.2.13/lib/active_record/connection_adapters/postgresql_adapter.rb:324:in `initialize'
00:02:22 web.1  |   from /opt/ruby/lib/ruby/gems/2.0.0/gems/activerecord-3.2.13/lib/active_record/connection_adapters/postgresql_adapter.rb:28:in `new'
00:02:22 web.1  |   from /opt/ruby/lib/ruby/gems/2.0.0/gems/activerecord-3.2.13/lib/active_record/connection_adapters/postgresql_adapter.rb:28:in `postgresql_connection'
00:02:22 web.1  |   from /opt/ruby/lib/ruby/gems/2.0.0/gems/activerecord-3.2.13/lib/active_record/connection_adapters/abstract/connection_pool.rb:315:in `new_connection'
00:02:22 web.1  |   from /opt/ruby/lib/ruby/gems/2.0.0/gems/activerecord-3.2.13/lib/active_record/connection_adapters/abstract/connection_pool.rb:325:in `checkout_new_connection'
00:02:22 web.1  |   from /opt/ruby/lib/ruby/gems/2.0.0/gems/activerecord-3.2.13/lib/active_record/connection_adapters/abstract/connection_pool.rb:247:in `block (2 levels) in checkout'
00:02:22 web.1  |   from /opt/ruby/lib/ruby/gems/2.0.0/gems/activerecord-3.2.13/lib/active_record/connection_adapters/abstract/connection_pool.rb:242:in `loop'
00:02:22 web.1  |   from /opt/ruby/lib/ruby/gems/2.0.0/gems/activerecord-3.2.13/lib/active_record/connection_adapters/abstract/connection_pool.rb:242:in `block in checkout'
00:02:22 web.1  |   from /opt/ruby/lib/ruby/2.0.0/monitor.rb:211:in `mon_synchronize'
00:02:22 web.1  |   from /opt/ruby/lib/ruby/gems/2.0.0/gems/activerecord-3.2.13/lib/active_record/connection_adapters/abstract/connection_pool.rb:239:in `checkout'
00:02:22 web.1  |   from /opt/ruby/lib/ruby/gems/2.0.0/gems/activerecord-3.2.13/lib/active_record/connection_adapters/abstract/connection_pool.rb:102:in `block in connection'
00:02:22 web.1  |   from /opt/ruby/lib/ruby/2.0.0/monitor.rb:211:in `mon_synchronize'
00:02:22 web.1  |   from /opt/ruby/lib/ruby/gems/2.0.0/gems/activerecord-3.2.13/lib/active_record/connection_adapters/abstract/connection_pool.rb:101:in `connection'
00:02:22 web.1  |   from /opt/ruby/lib/ruby/gems/2.0.0/gems/activerecord-3.2.13/lib/active_record/connection_adapters/abstract/connection_pool.rb:410:in `retrieve_connection'
00:02:22 web.1  |   from /opt/ruby/lib/ruby/gems/2.0.0/gems/composite_primary_keys-5.0.13/lib/composite_primary_keys/connection_adapters/abstract/connection_specification_changes.rb:47:in `retrieve_connection'
00:02:22 web.1  |   from /opt/ruby/lib/ruby/gems/2.0.0/gems/composite_primary_keys-5.0.13/lib/composite_primary_keys/connection_adapters/abstract/connection_specification_changes.rb:29:in `connection'
00:02:22 web.1  |   from /opt/ruby/lib/ruby/gems/2.0.0/gems/activerecord-3.2.13/lib/active_record/railtie.rb:88:in `block in <class:Railtie>'
00:02:22 web.1  |   from /opt/ruby/lib/ruby/gems/2.0.0/gems/railties-3.2.13/lib/rails/initializable.rb:30:in `instance_exec'
00:02:22 web.1  |   from /opt/ruby/lib/ruby/gems/2.0.0/gems/railties-3.2.13/lib/rails/initializable.rb:30:in `run'
00:02:22 web.1  |   from /opt/ruby/lib/ruby/gems/2.0.0/gems/railties-3.2.13/lib/rails/initializable.rb:55:in `block in run_initializers'
00:02:22 web.1  |   from /opt/ruby/lib/ruby/gems/2.0.0/gems/railties-3.2.13/lib/rails/initializable.rb:54:in `each'
00:02:22 web.1  |   from /opt/ruby/lib/ruby/gems/2.0.0/gems/railties-3.2.13/lib/rails/initializable.rb:54:in `run_initializers'
00:02:22 web.1  |   from /opt/ruby/lib/ruby/gems/2.0.0/gems/railties-3.2.13/lib/rails/application.rb:136:in `initialize!'
00:02:22 web.1  |   from /opt/ruby/lib/ruby/gems/2.0.0/gems/railties-3.2.13/lib/rails/railtie/configurable.rb:30:in `method_missing'
00:02:22 web.1  |   from /vagrant/config/environment.rb:5:in `<top (required)>'
00:02:22 web.1  |   from /opt/ruby/lib/ruby/gems/2.0.0/gems/activesupport-3.2.13/lib/active_support/dependencies.rb:251:in `require'
00:02:22 web.1  |   from /opt/ruby/lib/ruby/gems/2.0.0/gems/activesupport-3.2.13/lib/active_support/dependencies.rb:251:in `block in require'
00:02:22 web.1  |   from /opt/ruby/lib/ruby/gems/2.0.0/gems/activesupport-3.2.13/lib/active_support/dependencies.rb:236:in `load_dependency'
00:02:22 web.1  |   from /opt/ruby/lib/ruby/gems/2.0.0/gems/activesupport-3.2.13/lib/active_support/dependencies.rb:251:in `require'
00:02:22 web.1  |   from config.ru:4:in `block in <main>'
00:02:22 web.1  |   from /opt/ruby/lib/ruby/gems/2.0.0/gems/rack-1.4.5/lib/rack/builder.rb:51:in `instance_eval'
00:02:22 web.1  |   from /opt/ruby/lib/ruby/gems/2.0.0/gems/rack-1.4.5/lib/rack/builder.rb:51:in `initialize'
00:02:22 web.1  |   from config.ru:1:in `new'
00:02:22 web.1  |   from config.ru:1:in `<main>'
00:02:22 web.1  |   from /opt/ruby/lib/ruby/gems/2.0.0/gems/unicorn-4.6.3/lib/unicorn.rb:48:in `eval'
00:02:22 web.1  |   from /opt/ruby/lib/ruby/gems/2.0.0/gems/unicorn-4.6.3/lib/unicorn.rb:48:in `block in builder'
00:02:22 web.1  |   from /opt/ruby/lib/ruby/gems/2.0.0/gems/unicorn-4.6.3/lib/unicorn/http_server.rb:722:in `call'
00:02:22 web.1  |   from /opt/ruby/lib/ruby/gems/2.0.0/gems/unicorn-4.6.3/lib/unicorn/http_server.rb:722:in `build_app!'
00:02:22 web.1  |   from /opt/ruby/lib/ruby/gems/2.0.0/gems/unicorn-4.6.3/lib/unicorn/http_server.rb:140:in `start'
00:02:22 web.1  |   from /opt/ruby/lib/ruby/gems/2.0.0/gems/unicorn-4.6.3/bin/unicorn:126:in `<top (required)>'
00:02:22 web.1  |   from /opt/ruby/bin/unicorn:23:in `load'
00:02:22 web.1  |   from /opt/ruby/bin/unicorn:23:in `<main>'
00:02:22 web.1  | exited with code 1

I think the issue has to do with the pg gem not successfully establishing an SSL connection with Postgres. Is there something special that needs to be configured on the Ubuntu server to make the SSL connection happen?

Here is the database.yml:

  adapter: postgresql
  encoding: unicode
  pool: 5
  database: database
  username: user
  password: password
  host: xxx-xxx-xxx-xxx.compute-1.amazonaws.com
  port: 5432

This config works from my Mac, but not the Ubuntu VM. I've also tried adding sslmode: require, which also didn't help.

Improve this question
7
  • postgresql.org/docs/current/static/ssl-tcp.html Commented Jan 21, 2014 at 1:37
  • @CraigRinger This documentation seems to focus more on the server side requirements as opposed to the client side. I'm connecting to a database hosted through Heroku and it works from my Mac using SSL, just not from Ubuntu. Any thoughts on why the client from the Ubuntu server will not connect using SSL? Commented Jan 21, 2014 at 4:32
  • Ah, ok. The usual issue is people not configuring SSL server-side. Not an issue if you're using Heroku (something you didn't mention anywhere, only "ubuntu server", which suggested you were self-hosting Pg). Commented Jan 21, 2014 at 4:34
  • @CraigRinger I updated the question to be more clear. Commented Jan 21, 2014 at 4:35
  • The problem is in the first line. This has nothing to do with Ruby or gems or Rails. Your Postgres server is rejecting the connection. I don't have an answer for you, but it has to do with Postgres permissions. Commented Jan 21, 2014 at 4:36

1 Answer 1

Reset to default
3

The key error portion is:

FATAL: no pg_hba.conf entry for host "207.172.212.123", user "user", database "database", SSL off

This tells you that you're not attempting to make an SSL connection. You'll want to explicitly tell libpq (which is what the Pg gem, and hence Rails, uses to connect to PostgreSQL) that you require an SSL connection, by finding a way to pass the sslmode parameter through to libpq with value require.

This question suggests that the Pg gem was modified to autonegotiate SSL. So at a guess, the version on your Mac is newer than the version on your Ubuntu box. For older versions it looks like there may not be a simple way to specify that SSL is required, but the JDBC URL format for the connection string with ssl=true may be used to work around the lack, per the first answer on that article, but that's a bit ugly.

Your comments suggest that your Pg gem doesn't understand sslmode as an option in database.yml, silently ignoring it.

In that case, I'd try using a connstring:

 adapter: postgresql
 database: "dbname=database host=207.172.212.123 user=user sslmode=require"
 ...

In general you can supply a connstring anywhere you can put a database name with libpq. You should leave out the user, host, etc keys in your database.yml if you do this. I haven't tested it with Rails, but this approach works with most things.

Or you could update your Pg gem to one that supports SSL properly. It looks like the current version should support sslmode as a connection option.

UPDATE:

It turns out that SSL was not supported by the libpq on the Ubuntu box.

Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

I'll try the changes to the database.yml, but both setups are using version 0.17.1 of the pg gem, as specified in the Gemfile.lock, which they both share.
@ChrisJackson That makes things more interesting. Different compile-time defaults in libpq? Does the libpq on your Ubuntu box support SSL? Try psql "dbname=database host=207.172.212.123 user=user sslmode=require" (with appropriate changes for real details).
Might be on to something here, I get this from the Ubuntu server: "psql: sslmode value "require" invalid when SSL support is not compiled in". However, the same command from my Mac successfully connects, so that's the differentiating factor.
Ok, got it working running these commands: sudo add-apt-repository ppa:pitti/postgresql sudo apt-get update sudo apt-get install postgresql-9.2 -y
@ChrisJackson So it was a build issue. In general I'd recommend using PostgreSQL packages from apt.postgresql.org rather than some random PPA, but glad you got it going.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.