ruby-on-rails - 无法建立 Postgres SSL 连接

Question

我正在尝试建立一个 vagrant 环境来运行连接到 Heroku 上托管的远程 Postgres 数据库的 Rails 应用程序。从我的主机 (Mac OS X) 运行该应用程序工作正常，但在我的 vagrant box 中全新安装 Ubuntu 时失败，即使它们指向相同的源目录。这是我在尝试启动 Web 服务器时得到的结果:

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

我认为问题与 pg gem 没有成功建立与 Postgres 的 SSL 连接有关。是否需要在 Ubuntu 服务器上配置一些特殊的东西才能建立 SSL 连接？

这是数据库.yml:

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

此配置适用于我的 Mac，但不适用于 Ubuntu VM。我也试过添加 sslmode: require，但也没有用。

Answer 1

关键错误部分是:

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

这告诉您您没有尝试建立 SSL 连接。您需要通过找到一种方法来传递 sslmode 参数到 libpq，值为 require。

This question建议修改 Pg gem 以自动协商 SSL。所以猜测，你的 Mac 上的版本比你的 Ubuntu 盒子上的版本更新。对于旧版本，看起来可能没有一种简单的方法来指定需要 SSL，但是带有 ssl=true 的连接字符串的 JDBC URL 格式可以用来解决这个问题，per那篇文章的第一个答案，但这有点难看。

你的评论表明你的 Pg gem 不理解 sslmode 作为 database.yml 中的一个选项，默默地忽略它。

在这种情况下，我会尝试使用连接字符串:

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

一般来说，您可以在任何可以用libpq 放置数据库名称的地方提供connstring。如果这样做，您应该在 database.yml 中省略 user、host 等键。我没有用 Rails 测试它，但这种方法适用于大多数情况。

或者您可以将您的 Pg gem 更新为正确支持 SSL 的。看起来像 the current version should support sslmode作为连接选项。

更新:

事实证明，Ubuntu 机器上的 libpq 不支持 SSL。