☰

Postgres 远程连接超时

tag-icon • tag-icon iptables redhat postgresql remote-access
Postgres 远程连接超时

我一直在广泛阅读教程来帮助解决我的问题，但无济于事。

我有一台 Redhat VM，上面安装了 Postgres，我想让它可以进行远程连接。当我在机器上时，如果我运行以下命令，我就可以连接到我想要的表。

psql -U philhouse -d pwap

但是，当我尝试运行这个时：

psql -U philhouse -d pwap -h servername.nu.edu

我总是超时。

我读了多个教程和指南，但还是搞不懂。我编辑了 pg_hba.conf 文件、我的 postgres.conf 文件，以及尝试使用 iptables。下面是以下输出，仅供您查看：

配置文件为了测试目的，我已将其更改为允许所有连接

# TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD

# "local" is for Unix domain socket connections only
local   all         all                               trust
# IPv4 local connections:
host    all         all         0.0.0.0/0             trust
# IPv6 local connections:
host    all         all         ::1/128               trust

postgres.conf- 在这里我将 listen_addresses 改为 * 只是为了测试目的

#------------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#------------------------------------------------------------------------------

# - Connection Settings -

#listen_addresses = '*'         # what IP address(es) to listen on;
                                        # comma-separated list of addresses;
                                        # defaults to 'localhost', '*' = all
                                        # (change requires restart)
#port = 5432                            # (change requires restart)
max_connections = 100                   # (change requires restart)
# Note:  Increasing max_connections costs ~400 bytes of shared memory per 
# connection slot, plus lock space (see max_locks_per_transaction).
#superuser_reserved_connections = 3     # (change requires restart)
#unix_socket_directory = ''             # (change requires restart)
#unix_socket_group = ''                 # (change requires restart)
#unix_socket_permissions = 0777         # begin with 0 to use octal notation
                                        # (change requires restart)
#bonjour_name = ''                      # defaults to the computer name
                                        # (change requires restart)

# - Security and Authentication -

#authentication_timeout = 1min          # 1s-600s
#ssl = off                              # (change requires restart)
#ssl_ciphers = 'ALL:!ADH:!LOW:!EXP:!MD5:@STRENGTH'      # allowed SSL ciphers
                                        # (change requires restart)
#ssl_renegotiation_limit = 512MB        # amount of data between renegotiations
#password_encryption = on
#db_user_namespace = off

** iptables ** 我刚刚尝试过这个...不是 100% 确定这里该做什么。

# Firewall configuration written by system-config-securitylevel
# Manual customization of this file is not recommended.
*filter
:INPUT ACCEPT [0:0]
:FORWARD ACCEPT [0:0]
:OUTPUT ACCEPT [0:0]
:NETBKP - [0:0]
:RH-Firewall-1-INPUT - [0:0]
-A INPUT -p tcp -m multiport --dports 13720,13721,13782,13724,13783,13722,13723 -j NETBKP
-A INPUT -s 127.0.0.1 -d 127.0.0.1 -j ACCEPT
#-A INPUT -s 129.105.214.0/255.255.255.0 -p tcp -m tcp --dport 22 -j ACCEPT 
#-A INPUT -s 129.105.106.0/255.255.255.128 -p tcp -m tcp --dport 22 -j ACCEPT 
#-A INPUT -s 165.124.200.32/255.255.255.240 -p tcp -m tcp --dport 22 -j ACCEPT
-A INPUT -s 129.105.106.0/255.255.255.128 -p udp -m udp --dport 161 -j ACCEPT
#
#FP 121688
-A INPUT -s 129.105.0.0/255.255.0.0 -p tcp -m tcp --dport 22 -j ACCEPT
-A INPUT -s 165.124.0.0/255.255.0.0 -p tcp -m tcp --dport 22 -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 tcp --dport 3000 -j ACCEPT
-A INPUT -p tcp -m tcp --dport 5432 -j ACCEPT
#
#
#-A INPUT -s 129.105.119.86 -j ACCEPT
-A INPUT -j RH-Firewall-1-INPUT
-A NETBKP -s 129.105.106.0/255.255.255.128 -j ACCEPT
-A NETBKP -s 129.105.208.18 -j ACCEPT
-A NETBKP -s 129.105.208.20 -j ACCEPT
-A NETBKP -s 129.105.208.82 -j ACCEPT
-A NETBKP -s 129.105.208.115 -j ACCEPT
-A NETBKP -s 129.105.208.116 -j ACCEPT
-A NETBKP -s 129.105.215.131 -j ACCEPT
-A NETBKP -s 165.124.61.0/255.255.255.128 -j ACCEPT
-A NETBKP -j REJECT --reject-with icmp-port-unreachable
-A FORWARD -j RH-Firewall-1-INPUT
-A RH-Firewall-1-INPUT -i lo -j ACCEPT
-A RH-Firewall-1-INPUT -p icmp --icmp-type any -j ACCEPT
-A RH-Firewall-1-INPUT -p 50 -j ACCEPT
-A RH-Firewall-1-INPUT -p 51 -j ACCEPT
-A RH-Firewall-1-INPUT -p udp --dport 5353 -d 224.0.0.251 -j ACCEPT
-A RH-Firewall-1-INPUT -p udp -m udp --dport 631 -j ACCEPT
-A RH-Firewall-1-INPUT -m state --state ESTABLISHED,RELATED -j ACCEPT
#-A RH-Firewall-1-INPUT -j LOG
-A RH-Firewall-1-INPUT -j REJECT --reject-with icmp-host-prohibited
COMMIT

我遗漏了什么想法或事情吗？我已经为此工作了 3 天，但没有取得任何进展……

谢谢您的帮助！！

答案1

适当地设置您的 listen_address（即删除已有 listen_address 前面的注释字符）。默认情况下，postgres 仅监听本地套接字和 localhost（环回接口）。

答案2

我来把整个过程告诉你，请你一步一步再检查一下：

  1. 添加以下行'/etc/postgres/10/main/postgres.conf'：

    listen_addresses = '*'

  2. 添加以下几行到'/etc/postgres/10/main/pg_hba.conf'：

    host  all  all  0.0.0.0/0  md5
host  replication  all  0.0.0.0/0 md5

  3. 运行以下命令：

    sudo ufw allow 5432/tcp

相关内容