PostgreSQL通过SSH隧道

本文介绍了PostgreSQL通过SSH隧道的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我想使用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:5432 myuser@remote.server

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

The following SSH command connects to remote.example 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

如果这是预期的作为长期运行的隧道，我建议使用 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