PostgreSQL 通过 SSH 隧道 [英] PostgreSQL via SSH Tunnel

问题描述

我想使用 RazorSQL 连接到我在远程服务器上运行的数据库.我使用以下命令在我的本地主机上创建了一个 SSH 隧道:

I'd like to use RazorSQL to connect to my database which is running on a remote server. I create a SSH tunnel on my localhost with the following command:

ssh -L 1111:remote.server.com:5432 [e-mail]

我通过 RazorSQL 的 GUI 配置我的连接，指定 localhost 作为主机和 1111 作为端口.当我点击连接"时，出现以下错误信息:

I configure my connection via RazorSQL's GUI, specifying localhost as the host and 1111 as the port. When I click on "Connect", the following error message appears:

ERROR: An error occurred while trying to make a connection to
the database: 

JDBC URL: jdbc:postgresql://localhost:1111/myuser

FATAL:
no pg_hba.conf entry for host "aaa.bbb.ccc.ddd",
user "myuser", database "mydatabase", SSL off

其中 aaa.bbb.ccc.ddd 是远程服务器的 IP 地址.

where aaa.bbb.ccc.ddd is a remote server's IP address.

此外，我不允许更改我的 pg_hba.conf 文件的内容.这就是现在的样子:

What is more, I am not allowed to change the contents of my pg_hba.conf file. That's how it look like at the moment:

# TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD

@remove-line-for-nolocal@# "local" is for Unix domain socket connections only
@remove-line-for-nolocal@local   all         all                               @authmethod@
# IPv4 local connections:
host    all         all         127.0.0.1/32          @authmethod@
# IPv6 local connections:
host    all         all         ::1/128               @authmethod@

是否可以使用我当前的设置通过 SSH 隧道连接到数据库服务器，而无需修改服务器的配置?

Is it possible to connect to the database server via SSH tunnel using my current setup and without modifying the server's configuration?

推荐答案

您的 pg_hba.conf 似乎允许来自 localhost 的连接.使您的 SSH 隧道连接出现在 localhost 的最简单方法是使它们到 localhost.

Your pg_hba.conf appears to permit connections from localhost. The easiest way of causing your SSH tunnel connections to appear from localhost is to make them to localhost.

以下 SSH 命令以用户user"的身份连接到 remote.example.com，并使您的 ssh 客户端侦听 localhost，端口 1111/tcp.与该端口建立的任何连接都将通过 ssh 隧道转发，并且在 ssh 服务器端，连接将建立到 localhost，端口 5432/tcp.由于我们正在连接到 localhost，因此连接似乎也来自 localhost，并且应该与您现有的 pg_hba.conf 行匹配.

The following SSH command connects to remote.example.com as user "user", and causes your ssh client to listen on localhost, port 1111/tcp. Any connections made to that port will be forwarded over the ssh tunnel, and on the ssh server side the connections will be made to localhost, port 5432/tcp. Since we're connecting to localhost, the connections will appear to be from localhost also, and should match your existing pg_hba.conf line.

ssh -L 1111:localhost:5432 [e-mail]

如果预计这是一个长时间运行的隧道，我建议使用 自动ssh

If this is expected to be a long-running tunnel, I would recommend using autossh

要在运行 ssh 客户端的主机上使用 psql 客户端进行连接，请使用以下内容:

To connect using the psql client on the host where you are running the ssh client, use something like this:

psql -h localhost -p 1111 -U your-db-username database-name

然后应该会提示您输入数据库用户的密码.

You should then be prompted for your database user's password.

或者，您可以在运行 psql 的客户端上的主目录中将以下行添加到名为 .pgpass 的文件中:

Alternately, you can add a line line the following to a file called .pgpass in your home directory on the client where you're running psql:

localhost:1111:database-name:your-db-user:your-db-password

这篇关于PostgreSQL 通过 SSH 隧道的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！