通过 SSH 隧道 PostgreSQL

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

ssh -L 1111:remote.server.com:5432 myuser@remote.server.com

我通过 RazorSQL 的 GUI 配置连接，指定 localhost作为主机，指定 1111作为端口。当我点击“连接”，出现以下错误消息:

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 地址。

更重要的是，我不允许更改我的 pg_hba.conf文件的内容。现在看起来是这样的:

# 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 隧道连接到数据库服务器，而不需要修改服务器的配置？

100994

小开

最佳答案

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.

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 user@remote.example.com

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

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.

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