Ubuntu 重启后 Postgresql 拒绝 TCP/IP 连接

Question

这让我很困惑。

我重新启动了我的 ubuntu 服务器，现在我尝试连接 pgadmin，并得到以下信息：

could not connect to server: Connection timed out (0x0000274C/10060)
Is the server running on host "<ip.address>" and accepting
TCP/IP connections on port 5432?

在我的 postgresql.conf 文件中

listen_addresses = '*'

在我的 pg_hba.conf 中

# DO NOT DISABLE!
# If you change this first entry you will need to make sure that the
# database superuser can access the database using some other method.
# Noninteractive access to all databases is required during automatic
# maintenance (custom daily cronjobs, replication, and similar tasks).
#
# Database administrative login by Unix domain socket
local   all             postgres                                peer

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     peer
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
# IPv6 local connections:
host    all             all             ::1/128                 md5
# Allow replication connections from localhost, by a user with the
# replication privilege.
#local   replication     postgres                                peer
#host    replication     postgres        127.0.0.1/32            md5
#host    replication     postgres        ::1/128                 md5



host    all     all     129.125.179.201/32      trust
host    all     all     86.81.139.21/24         trust

我在最后添加了以下行

host    all     all     0.0.0.0/0       md5

仍然无法连接

每次更改后我都会重新启动 postgresql。

我可以通过shh通过命令行登录postgresql。

我沒主意了！

编辑

iptables

sudo iptables -S
-P INPUT ACCEPT
-P FORWARD DROP
-P OUTPUT ACCEPT
-N DOCKER
-N DOCKER-ISOLATION
-A INPUT -i lo -j ACCEPT
-A INPUT -d 127.0.0.0/8 -j REJECT --reject-with icmp-port-unreachable
-A INPUT -m state --state RELATED,ESTABLISHED -j ACCEPT
-A INPUT -p tcp -m tcp --dport 80 -j ACCEPT
-A INPUT -p tcp -m tcp --dport 443 -j ACCEPT
-A INPUT -p tcp -m state --state NEW -m tcp --dport 22 -j ACCEPT
-A INPUT -p icmp -m icmp --icmp-type 8 -j ACCEPT
-A INPUT -m limit --limit 5/min -j LOG --log-prefix "iptables denied: " --log-level 7
-A INPUT -j DROP
-A FORWARD -j DOCKER-ISOLATION
-A FORWARD -o docker0 -j DOCKER
-A FORWARD -o docker0 -m conntrack --ctstate RELATED,ESTABLISHED -j ACCEPT
-A FORWARD -i docker0 ! -o docker0 -j ACCEPT
-A FORWARD -i docker0 -o docker0 -j ACCEPT
-A FORWARD -j DROP
-A OUTPUT -j ACCEPT
-A DOCKER-ISOLATION -j RETURN

Netstat 结果

netstat -lt4
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address           Foreign Address         State
tcp        0      0 localhost:mysql         *:*                     LISTEN
tcp        0      0 *:8787                  *:*                     LISTEN
tcp        0      0 *:ssh                   *:*                     LISTEN
tcp        0      0 *:postgresql            *:*                     LISTEN

iptables-保存

sudo iptables-save
# Generated by iptables-save v1.6.0 on Sat Sep 23 18:23:36 2017
*nat
:PREROUTING ACCEPT [48958:12725477]
:INPUT ACCEPT [2847:159736]
:OUTPUT ACCEPT [70:6309]
:POSTROUTING ACCEPT [70:6309]
:DOCKER - [0:0]
-A PREROUTING -m addrtype --dst-type LOCAL -j DOCKER
-A OUTPUT ! -d 127.0.0.0/8 -m addrtype --dst-type LOCAL -j DOCKER
-A POSTROUTING -s 172.17.0.0/16 ! -o docker0 -j MASQUERADE
-A DOCKER -i docker0 -j RETURN
COMMIT
# Completed on Sat Sep 23 18:23:36 2017
# Generated by iptables-save v1.6.0 on Sat Sep 23 18:23:36 2017
*filter
:INPUT ACCEPT [0:0]
:FORWARD DROP [0:0]
:OUTPUT ACCEPT [0:0]
:DOCKER - [0:0]
:DOCKER-ISOLATION - [0:0]
-A INPUT -i lo -j ACCEPT
-A INPUT -d 127.0.0.0/8 -j REJECT --reject-with icmp-port-unreachable
-A INPUT -m state --state RELATED,ESTABLISHED -j ACCEPT
-A INPUT -p tcp -m tcp --dport 80 -j ACCEPT
-A INPUT -p tcp -m tcp --dport 443 -j ACCEPT
-A INPUT -p tcp -m state --state NEW -m tcp --dport 22 -j ACCEPT
-A INPUT -p icmp -m icmp --icmp-type 8 -j ACCEPT
-A INPUT -m limit --limit 5/min -j LOG --log-prefix "iptables denied: " --log-level 7
-A INPUT -j DROP
-A FORWARD -j DOCKER-ISOLATION
-A FORWARD -o docker0 -j DOCKER
-A FORWARD -o docker0 -m conntrack --ctstate RELATED,ESTABLISHED -j ACCEPT
-A FORWARD -i docker0 ! -o docker0 -j ACCEPT
-A FORWARD -i docker0 -o docker0 -j ACCEPT
-A FORWARD -j DROP
-A OUTPUT -j ACCEPT
-A DOCKER-ISOLATION -j RETURN
COMMIT
# Completed on Sat Sep 23 18:23:36 2017

Answer 1

我认为问题与授权（例如 pg_hba.conf）无关，因为您的错误表明端口无法访问。尝试指出尽可能多的连接参数。还可以尝试使用一些简单的工具（如 psql）进行连接（给出所有连接参数，例如 -h localhost -U user -W -p 5432 等）。这应该会给您一些提示，让您了解发生了什么以及在哪里可以进一步查找。

特别是给他-h localhost，否则它将使用本地 unix 套接字而不是 TCP

编辑：您确定这是好的 iptable 规则吗：-A INPUT -d 127.0.0.0/8 -j REJECT --reject-with icmp-port-unreachable 我不是这方面的专家，但请尝试暂时删除此条目并重新连接。同样担心：-A INPUT -j DROP

编辑 2：如果什么都不起作用，请使用“iptables-save”而不是“iptables -S”转储所有 iptables。“iptables-save”转储所有可用表（例如过滤器、nat 等）。更多信息请参阅手册

Answer 2

您是否有可能将其从 /etc/services 中删除

你应该看到如下的一行

    postgresql      5432/tcp        postgres        # PostgreSQL Database