ruby-on-rails - ActiveRecord 可以远程连接到 PostgreSQL 并保护数据库密码吗？

Question

我在远程 VPS 服务器 (CentOS 5) 上有一个 PostgreSQL 数据库，我想通过连接让 Rails 应用程序从我的本地 Mac 笔记本电脑连接到它。在我的笔记本电脑上，我安装了 ActiveRecord PostgreSQL 适配器——postgres (0.7.9.2008.01.28)。

我在 PostgreSQL docs 中读到:

The password-based authentication methods are md5, crypt, and password. These methods operate similarly except for the way that the password is sent across the connection: respectively, MD5-hashed, crypt-encrypted, and clear-text.

[...]

If you are at all concerned about password "sniffing" attacks then md5 is preferred...Plain password should be avoided especially for connections over the open Internet (unless you use SSL, SSH, or another communications security wrapper around the connection).

在标准的 Rails database.yml 中，对于本地主机连接会有这样的东西......

development:
  adapter: postgresql
  database: journalapp_development
  username: xxx
  password: yyy
  host: localhost

但是 PostgreSQL 文档中没有讨论身份验证方法。是否有类似“auth_method: md5”之类的选项？

Answer 1

无论 Postgres 是否允许此功能，您都可以使用 SSH 隧道启用与远程数据库的安全连接。这是来自 Web 文档的无偿 Stack Overflow 粘贴:

First make sure that an SSH server is running properly on the same machine as the PostgreSQL server and that you can log in using ssh as some user. Then you can establish a secure tunnel with a command like this from the client machine:

ssh -L 3333:foo.com:5432 joe@foo.com The first number in the -L argument, 3333, is the port number of your end of the tunnel; it can be chosen freely. The second number, 5432, is the remote end of the tunnel: the port number your server is using. The name or IP address between the port numbers is the host with the database server you are going to connect to. In order to connect to the database server using this tunnel, you connect to port 3333 on the local machine:

psql -h localhost -p 3333 postgres To the database server it will then look as though you are really user joe@foo.com and it will use whatever authentication procedure was configured for connections from this user and host. Note that the server will not think the connection is SSL-encrypted, since in fact it is not encrypted between the SSH server and the PostgreSQL server. This should not pose any extra security risk as long as they are on the same machine.

如果您需要更多，可以通过搜索“SSL 隧道”或“postgres SSL 隧道”在线找到它。这是我获得上述内容的 Postgres 站点:

http://www.postgresql.org/docs/current/static/ssh-tunnels.html

总结一下 Rails，您将执行以下操作:

1) 在终端窗口中，运行上面的第一个 ssh 命令以建立隧道。

2) 像这样设置你的数据库属性:

development:
  adapter: postgresql
  database: journalapp_development
  username: xxx
  password: yyy
  host: localhost
  port: 3333