如何让 Rails 使用 SSL 连接到 PostgreSQL？

How do I make Rails use SSL to connect to PostgreSQL?

提问by pupeno

When I try to connect to the remote PostgreSQL database with a Rails 3.2 project I get this error:

当我尝试使用 Rails 3.2 项目连接到远程 PostgreSQL 数据库时，出现此错误：

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

My configuration on Rails looks like this:

我在 Rails 上的配置如下所示：

staging:
  adapter: postgresql
  database: projectx
  username: projectx
  password: 123456
  host: 10.0.0.3
  encoding: utf8
  template: template0
  min_messages: warning

and on PostgreSQL looks like this:

在 PostgreSQL 上看起来像这样：

hostssl    all             all             0.0.0.0/0            md5
hostssl    all             all             ::/0                 md5

Both machines are running on an Ubuntu 12.04.

两台机器都在 Ubuntu 12.04 上运行。

I found posts saying that it should work automatically, which clearly doesn't happen. I found some saying that libpq didn't have SSL enabled and enabling it solved the problem, but no explanation on how to enable it. I can see when I look at the dependencies of libpq that it depends on the some SSL packages, so I would assume SSL support is compiled.

我发现帖子说它应该自动工作，这显然不会发生。我发现有人说 libpq 没有启用 SSL 并启用它解决了问题，但没有解释如何启用它。当我查看 libpq 的依赖项时，我可以看到它依赖于某些 SSL 包，因此我假设已编译 SSL 支持。

Some posts recommended adding this:

一些帖子建议添加这个：

sslmode: require

or this:

或这个：

sslmode: enabled

to enable ssl mode, but it had no effect for me. I read that it's silently ignored.

启用 ssl 模式，但它对我没有影响。我读到它被默默地忽略了。

I also tried the database string approach, ending up with:

我还尝试了数据库字符串方法，结果是：

staging:
  adapter: postgresql
  database: "host=10.0.0.3 dbname=projectx user=projectx password=123456 sslmode=require"

and then I got the error:

然后我得到了错误：

could not connect to server: No such file or directory
Is the server running locally and accepting
connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?

which seems to indicate that Rails was trying to connect to localhost or rather, the local PostgreSQL (there's none) instead of 10.0.0.3.

这似乎表明 Rails 试图连接到 localhost 或更确切地说，本地 PostgreSQL（没有）而不是 10.0.0.3。

Any ideas?

有任何想法吗？

采纳答案by Daniel Vérité

As you wrote, normally the Ubuntu 12.x packages are set up so that SSL is activated, works out of the box, and in addition is the first methodtried by rails, or any client that lets libpqdeal with this stuff, which means almost all clients.

正如你所写的，通常 Ubuntu 12.x 包被设置为激活 SSL，开箱即用，此外是尝试的第一种方法rails，或者任何可以libpq处理这些东西的客户端，这意味着几乎所有客户。

This automatic enabling is not necessarily true with other PostgreSQL packages or with a self-compiled server, so the answers or advice applying to these other contexts don't help with yours.

这种自动启用不一定适用于其他 PostgreSQL 包或自编译服务器，因此适用于这些其他上下文的答案或建议对您没有帮助。

As your setup should work directly, this answer is a list of things to check to find out what goes wrong. Preferably, use psqlfirst to test a connection setup rather than rails, so that generic postgresql issues can be ruled out first.

由于您的设置应该直接工作，因此此答案是要检查以找出问题所在的列表。最好使用psqlfirst 来测试连接设置而不是rails，这样可以首先排除一般的 postgresql 问题。

Client-side

客户端

The client-side sslmodeparameter controls the sequence of connect attempts.

客户端sslmode参数控制连接尝试的顺序。

To voluntarily avoid SSL, a client would need to put sslmode=disablesomewhere in the connection string, or PGSSLMODE=disablein the environment, or mess up with one of the other PGSSL*variables. In the unlikely case your rails process had this in its environment, that would explain the error you're getting, given that pg_hba.confdoes not allow non-SSL connections.

为了主动避免 SSL，客户端需要sslmode=disable在连接字符串或PGSSLMODE=disable环境中的某处放置，或者与其他PGSSL*变量之一混淆。在不太可能的情况下，您的 rails 进程在其环境中具有此功能，这将解释您遇到的错误，因为它pg_hba.conf不允许非 SSL 连接。

Another reason to not try SSL is obviously when libpqis not compiled with SSL support but that's not the case with the Ubuntu packages.

另一个不尝试 SSL 的原因显然libpq是没有使用 SSL 支持进行编译，但 Ubuntu 软件包并非如此。

The default for sslmodeis prefer, described as:

默认sslmode就是prefer，被描述为：

prefer (default)

first try an SSL connection; if that fails, try a non-SSL connection

喜欢（默认）

first try an SSL connection; if that fails, try a non-SSL connection

The SSL=offat the end of your error message relates to the last connect attempt that fails. It may be that SSL was tried and failed, or not tried at all, we can't know from this message alone. The connect attempt with SSL=offis rejected normally by the server per the policy set in pg_hba.conf(hostsslin the first column).

在SSL=off你的错误消息的末尾涉及失败的最后一个连接尝试。可能是 SSL 尝试过但失败了，或者根本没有尝试过，我们无法仅从该消息中得知。根据（在第一列中）中SSL=off设置的策略，服务器通常会拒绝连接尝试。pg_hba.confhostssl

It's more plausible that the problem is server-side, because there are more things than can go wrong.

问题出在服务器端的可能性更大，因为出错的地方太多了。

Server-side

服务器端

Here are various things to check server-side:

以下是检查服务器端的各种事项：

• There should be ssl=onin postgresql.conf(default location: /etc/postgresql/9.1/main/)

• when connecting to localhost with psql, you should be greeted with a message like this:

• 应该有ssl=on在postgresql.conf（默认位置：/etc/postgresql/9.1/main/）

• 使用 连接到 localhost 时psql，您应该收到如下消息：

psql (9.1.13)
SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256)
Type "help" for help.

psql (9.1.13)
SSL 连接（密码：DHE-RSA-AES256-SHA，位：256）输入
“help”以获得帮助。

• The ca-certificatespackage should be installed and up-to-date.

• The ssl-certpackage should be installed and up-to-date.

• Inside the postgres data directory (/var/lib/postgresql/9.1/mainby default), there should be soft links:
  server.crt -> /etc/ssl/certs/ssl-cert-snakeoil.pemor another valid certificate, and
  server.key -> /etc/ssl/private/ssl-cert-snakeoil.keyor another valid key.

• /etc/ssl/certsand parent directories should be readable and cd'able by postgres.

• The postgresunix user should be in the ssl-certunix group (check with id -a postgres) otherwise it can't read the private key.

• If changing postgresql.conf, be sure that postgresql gets restarted before doing any other test.

• There shouldn't be any suspicious message about SSL in /var/log/postgresql/postgresql-9.1-main.logat startup time or at the time of the failed connection attempt.

• 该ca-certificates软件包应已安装并且是最新的。

• 该ssl-cert软件包应已安装并且是最新的。

• 在 postgres 数据目录内（/var/lib/postgresql/9.1/main默认情况下），应该有软链接：
  server.crt -> /etc/ssl/certs/ssl-cert-snakeoil.pem或另一个有效证书，和/
  server.key -> /etc/ssl/private/ssl-cert-snakeoil.key或另一个有效密钥。

• /etc/ssl/certs和父目录应该是可读的并且可以被 postgres 读取。

• 在postgresUNIX用户应该是在ssl-certUNIX组（检查用id -a postgres），否则就不能读取私钥。

• 如果更改postgresql.conf，请确保在进行任何其他测试之前重新启动 postgresql。

• /var/log/postgresql/postgresql-9.1-main.log在启动时或连接尝试失败时，不应有任何关于 SSL 的可疑消息。

回答by ahmeij

Rails uses the PG gem for postgres to connect see here for the implementation:

Rails 使用 PG gem 进行 postgres 连接，请参见此处的实现：

https://github.com/rails/rails/blob/02a3c0e771b3e09173412f93d8699d4825a366d6/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb#L881

https://github.com/rails/rails/blob/02a3c0e771b3e09173412f93d8699d4825a366d6/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb#L881

The PG gem uses libpg (c library) and the documentation on "PG::Connection.new" found here: http://deveiate.org/code/pg/PGconn.html

PG gem 使用 libpg（c 库）和“PG::Connection.new”的文档在这里找到：http: //deveiate.org/code/pg/PGconn.html

Suggests the following options:

建议以下选项：

host
server hostname

hostaddr
server address (avoids hostname lookup, overrides host)

port
server port number

dbname
connecting database name

user
login user name

password
login password

connect_timeout
maximum time to wait for connection to succeed

options
backend options

tty
(ignored in newer versions of PostgreSQL)

sslmode
(disable|allow|prefer|require)

krbsrvname
kerberos service name

gsslib
GSS library to use for GSSAPI authentication

service
service name to use for additional parameters

So this would indicate that the connection string will not work (since it is not recognised by the adapter, this might be a mysql adapter option)

所以这将表明连接字符串将不起作用（因为它不被适配器识别，这可能是一个 mysql 适配器选项）

Also this indicates that the sslmode=requiredoption should work, as this is a basic feature of libpg.

这也表明该sslmode=required选项应该有效，因为这是 libpg 的基本功能。

So:

所以：

database.yml

数据库.yml

staging:
  ...
  sslmode: "require"
  ...

should definitely do the trick, are you sure you use staging mode? // add sslmode to the other environments too to be sure.

绝对应该做到这一点，您确定使用登台模式吗？// 也将 sslmode 添加到其他环境中以确保。

Also libpg uses SSL by default as first try, maybe you see the error with SSL Off because SSL mode failed first, then libpq retried without ssl and eventually raised an error.

此外，libpg 默认使用 SSL 作为第一次尝试，也许您会看到 SSL Off 错误，因为 SSL 模式首先失败，然后 libpq 在没有 ssl 的情况下重试并最终引发错误。