pg gem sslmode = verify-full，在哪里放置证书？ [英] pg gem sslmode=verify-full, where to place certificates?

问题描述

pg-gem是否可以通过sslmode = verify-full来实现其postgres连接？就像传递该字符串一样简单吗？我有一个Rails应用，我想在外部数据库上建立完全由ca保护的ssl连接，但我真的不知道如何设置ca的一部分。在sslmode上使用require会默认使用ssl流量，但我不知道我应该将证书放在何处（或在哪个用户下，等等）。我是否应该像普通的psql客户端工作流中那样将它们放置在〜/ .postgresql /下，以获取名为root.crt和postgresql.cert和postgresql.key的已验证ssl证书？

Is there a way for the pg-gem to use sslmode = verify-full for it's postgres connection? Would that be as simple as just passing that string in? I have a Rails app that I want to do full ca protected ssl connections on to an external db and I don't really know how to setup the ca part of that. Using require on sslmode defaults it to use ssl traffic but I don't know where (or under what user, etc.) I should be placing my certificates to do verification. Should I just place them under ~/.postgresql/ like in the normal psql client workflow for verified ssl certs with the names root.crt and postgresql.cert and postgresql.key?

如果有帮助的话，我正在使用Postgres 9.1。

I'm using Postgres 9.1 if that helps at all.

为以后的编辑：

以下database.yml文件似乎可以在我的开发机上进行测试。我肯定会写一篇关于这个问题的博客文章，因为它是如此的PITA能够找出问题所在。

The following database.yml file seems to work as a test on my dev machine. I'll be writing a blog post on this issue for sure since it was such a PITA to figure out what was going wrong.

  host: 127.0.0.1
  sslcert: <%= Rails.root.join('config', 'client.crt') %>
  sslkey: <%= Rails.root.join('config', 'client.key') %>
  sslrootcert: <%= Rails.root.join('config', 'root.crt') %>
  sslmode: verify-full
  database: pg-test_development
  username: postgres
  password:

推荐答案

Pg gem使用 libpq 在内部，与PostgreSQL工具（如 psql ）相同的客户端库。

The Pg gem uses libpq internally, the same client library as the PostgreSQL tools like psql.

默认情况下 libpq 在〜/ .postgresql / 中查找CA证书。

By default libpq looks in ~/.postgresql/ for the CA certificate.

来自< a href = http://www.postgresql.org/docs/current/static/libpq-ssl.html rel = noreferrer>手册：

要允许服务器证书验证，必须将一个或多个受信任CA的证书放在文件〜/ .postgresql / root.crt 在用户的主目录中。 （在Microsoft Windows上，文件名为％APPDATA％\postgresql\root.crt 。）

To allow server certificate verification, the certificate(s) of one or more trusted CAs must be placed in the file ~/.postgresql/root.crt in the user's home directory. (On Microsoft Windows the file is named %APPDATA%\postgresql\root.crt.)

...和...

根证书文件和CRL的位置可以通过以下方式更改设置连接参数 sslrootcert 和 sslcrl [...]

AFAIK Rails会将您放入 database.yml 中的任何内容传递给 Pg gem，它将其作为连接参数传递给 libpq 。因此，您应该能够将键/值条目添加到您的 database.yml 节中，例如：

AFAIK Rails passes anything you put in your database.yml to the Pg gem, which passes it to libpq as a connection parameter. So you should be able to add key/value entries to your database.yml stanzas like:

sslmode: verify-full
# and if you don't want to use ~/.postgresq/root.crt for the cert location, set:
sslrootcert: /path/to/my/app/root/cert.crt

---

IMO要求将单个根证书传递给 libpq 是一种设计缺陷。它应该加载受信任的证书数据库。 SSL客户端证书的使用也存在类似的问题，在这种情况下，您不能提供密钥库和证书库，必须为给定主机传递特定文件。听起来好像对您来说还可以，因为您知道上游证书签名机构。

---

IMO the requirement to pass a single root cert to libpq is a design flaw. It should load a trusted certificate database. Similar issues exist for use of SSL client certificates, where you can't supply a keystore and cert store, you must pass specific files for a given host. It sounds like that's probably OK for you since you know the upstream certificate signing authority.

这篇关于pg gem sslmode = verify-full，在哪里放置证书？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！