PostgreSQL服务器远程连接 [英] Postgresql server remote connection

问题描述

我正在尝试在ubuntu上设置Postgresql 9.1服务器，以远程访问数据。我已经正确安装了postgres，服务器进程正在运行，我正在尝试对其进行配置，以便可以从LAN以外的其他几台计算机通过Internet远程访问服务器。

I am trying to set up a postgresql 9.1 server on ubuntu, for remote access of data. I have postgres properly installed, the server process is running and I am trying to configure it so that I can access the server remotely over the internet from a few other computers outside my LAN.

我已经用以下命令修改了pg_hba.conf：

I have already modified my pg_hba.conf with:

host    all     all     0.0.0.0     trust

和带有以下内容的postgresql.conf：

and the postgresql.conf with:

listen_addresses = '*'
port = 5432

我还修改了我的iptables接受端口5432上的连接。

I have additionally modified my iptables to accept connections on port 5432.

当我尝试在python上使用psycopg2进行连接时：

When I try to connect using psycopg2 on python:

conn=psycopg2.connect('host=XX.XX.XX.XX port=5432 dbname=postgres user=myUser password=mypassword')

我收到以下错误：

Traceback (most recent call last):
File "<stdin>", line 1, in <module>
File "/usr/local/lib/python2.7/dist-packages/psycopg2/__init__.py", line 179, in connect
connection_factory=connection_factory, async=async) 
psycopg2.OperationalError: could not connect to server: Connection refused
Is the server running on host "XX.XX.XX.XX" and accepting
TCP/IP connections on port 5432?

我不确定是否插入了正确的IP地址，但我很好奇如何确定确切地说，在这里使用什么IP地址连接到我的服务器。我使用了运行Postgres服务器的计算机的公共IP，但不确定是否正确。还是还有其他步骤我仍然想念？我知道这是不好的安全风格，但目前我只想建立连接。另外，我的计算机在路由器后面，所以我将如何专门访问服务器？

I am not sure if I inserted the correct IP address, and I am curious how I would figure out exactly what IP address to use here to connect to my server. I used the public IP of my computer which is running the postgres server, but I'm not sure that is correct. Or is there a whole other step I am still missing? I know this is bad security style, but for the moment I would like to just establish a connection. Also my computer is behind a router, so how would I access my server specifically?

任何帮助都非常感谢。

推荐答案

您的 pg_hba.conf 不应使用 trust ！！！ trust 表示不需要密码，我认为这不是您想要的。

Your pg_hba.conf should NOT use trust!!! trust means no password is required and I don't think that's what you want.

这是正确的配置

host    all             all             0.0.0.0/0               md5

注意 0.0.0.0 后面的 / 0 。

完整的 pg_hba.conf 应该是：-

local   all             all                                     trust
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
# IPv6 local connections:
host    all             all             ::1/128                 md5
host    all             all             0.0.0.0/0               md5

注意 trust 仅适用于本地连接。即适用于在也运行您的postgresql服务器的计算机上的本地主机IP 127.0.0.1 上运行的应用程序。

Notice that trust is only applicable for local connections. i.e. for applications running on localhost IP 127.0.0.1 on the machine which also runs your postgresql server.

这篇关于PostgreSQL服务器远程连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！