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似乎允许来自本地主机的连接。使SSH隧道连接从本地主机出现的最简单方法是使它们到本地主机。

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命令连接到远程。 example.com作为用户 user，并导致您的ssh客户端在本地主机端口1111 / tcp上侦听。与该端口建立的任何连接都将通过ssh隧道转发，并且在ssh服务器端，连接将建立至本地主机（端口5432 / tcp）。由于我们正在连接到本地主机，因此连接似乎也来自本地主机，并且应该与您现有的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]

如果这是预期的作为长期运行的隧道，我建议使用 autossh

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.

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

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屋！