远程访问PostgreSQL数据库

Question

我需要从DigitalOcean上运行的一个VPS上的远程机器访问一个postgresql数据库，该数据库运行的是12.10版本和postgresql 9.1版本。

我应该如何做到这一点？我注意到端口5432是关闭的，我该如何打开它？

- Øyvind

---

按照http://javabypatel.blogspot.in/2015/07/enable-remote-access-to-postgresql-database-server.html中提到的步骤进行操作，并修改postgresql.conf文件中的端口号。在更改端口后重新启动PostgreSQL服务器。 - Jayesh

@Jayesh发布的网址起了作用。按照说明成功地使我的一台开发电脑连接到另一台电脑（从Windows上的pgAdmin4连接到Ubuntu 18.04上的PostgreSQL 10.9）。 - EAmez

Answer 1

要打开端口5432，请编辑您的/etc/postgresql/9.1/main/postgresql.conf文件并进行更改。

listen_addresses='localhost'

到

listen_addresses='*'

重新启动您的数据库管理系统

invoke-rc.d postgresql restart

现在你可以连接上了

$ psql -h hostname -U username -d database

如果您无法进行身份验证，那么您需要为您的用户授予对数据库的访问权限。

编辑您的内容。

/etc/postgresql/9.1/main/pg_hba.conf

并添加

host all all all md5

（这是为了广泛的开放访问。如果需要更严格的控制，请参考pg_hba.conf文档并根据您的需求进行调整）。

（此外，您还需要重新加载）

invoke-rc.d postgresql reload

我不需要提到这是一个基本配置，现在你应该考虑修改你的防火墙并提高你的数据库管理系统的安全性。

- user224465

---

特别是，您应该启用SSL。 - Craig Ringer

好的，我尝试了这个方法，但是当我在我的电脑上使用pgAdmin尝试连接时，出现了"服务器未监听"的错误。我已经在iptables中添加了规则，并且当我运行iptables -L命令时，显示如下： ACCEPT tcp -- anywhere anywhere tcp dpt:postgresql当我在这个网站（http://www.yougetsignal.com/tools/open-ports/）上检查IP和端口时，显示端口是关闭的。 - Øyvind

服务器在监听吗？用 netstat -nlt|grep :5432 检查一下。 - user224465

我会以更严格的方式插入主机行：host <database> <user> <remote_client_IPaddress>/24 md5。 - gc5

对于Postgresql 9.5版本，你可能需要在listen_addresses生效之前重新启动服务器。 - Heather92065

通过PSQL使用SHOW hba_file;检查服务器上的正确路径，并使用标准命令service postgresql restart重新启动。 - Peter Krauss

在使用Ubuntu 16 LTS和PostgreSQL v10时，我解决了这个问题（感谢DanielVérité和@Kethryweryn！）通过使用host all all all md5。还需要按照ssl-tcp的指示进行操作，从“创建一个简单的自签名证书…”的第一条命令到“最后，创建一个服务器证书…”的最后一条命令。 - Peter Krauss

Answer 2

这个已经不再起作用了，如果它曾经起过作用的话：

host all all * md5

正确的可能行是：

host all all 0.0.0.0/0 md5 #ipv4范围 host all all ::0/0 md5 #ipv6范围 host all all all md5 #所有IP 来源

- Kethryweryn

---

这绝对起了作用。上面的答案绝对不起作用。 - Mike

请 @Mike 表达一下什么是正确的：host all all all md5 是否能正常工作？这是正确的吗？有任何安全问题吗？ - Peter Krauss

@peterkrauss 是的，对我来说，主机上的所有md5都有效。安全问题？当然是有的，但对于我所做的事情来说，这已经足够了。（内部网络） - Mike

Answer 3

最高得票并被接受的答案具有严重的安全隐患。出于良好的原因，此方法在默认情况下已禁用。

最好使用本地端口转发与ssh：

ssh -L local_port:localhost:foreign_port user@server

---

开始端口转发：

ssh -L 5432:localhost:5432 [e-mail]
#or
ssh -L 5432:127.0.0.1:5432 [e-mail]

（根据您的配置更改本地和外部端口）。

然后，您可以直接从本地计算机连接到数据库：

psql -U db_user -p local_port -l

- pLumo

---

除此之外，其他的方法对我都没有起作用！谢谢！ - pcko1

Answer 4

对于消息 "服务器未监听"，发生在我身上的情况是，我没有从 postgresql.conf 文件中删除 # 号，我的意思是：

#listen_addresses='localhost'

改为：

listen_addresses='*'

（对不起我的英文不好）。

- mrlinux

Answer 5

在进行配置之前，您需要设置以下内容：

在 `/etc/postgresql/10/main/postgresql.conf` 文件中：

# Connection Settings -

listen_addresses = '*'          # what IP address(es) to listen on;

在`/etc/postgresql/10/main/pg_hba.conf`中

# IPv4 local connections:
host    all             all             0.0.0.0/0           md5

重新启动您的服务器：

sudo /etc/init.d/postgresql restart

- Ramesh Ponnusamy

---

这是对上述答案的一个简洁而精彩的总结。 - zx485

Answer 6

要打开端口5432，您需要运行以下命令。

sudo ufw allow 5432/tcp

- ysrtymz