PostgreSQL配置listen_addresses='*'及pg_hba.conf后仍无法远程登录求助

Since you've already set up listen_addresses = '*' in postgresql.conf and added the host all all 0.0.0.0/0 md5 rule in pg_hba.conf, plus confirmed no firewall and open port 5432, let's dig deeper into the possible issues:

• Verify PostgreSQL service was restarted after config changes
  Modifications to postgresql.conf and pg_hba.conf won't take effect until you restart the PostgreSQL service. Run the appropriate command for your system:

  # For systemd-based systems (like RHEL 7+, Ubuntu 16.04+)
  sudo systemctl restart postgresql
  # For SysVinit systems
  sudo service postgresql restart
  

  Then check the service status to ensure it's running properly:

  sudo systemctl status postgresql
  

• Check actual listening addresses/ports
  Sometimes config changes don't apply because you edited the wrong file (e.g., multiple PostgreSQL instances, incorrect path). Use this command to see what PostgreSQL is actually listening on:

  ss -tulpn | grep postgres
  

  You should see output similar to LISTEN 0 128 0.0.0.0:5432 0.0.0.0:* users:(("postgres",pid=XXXX,fd=XX)). If you only see 127.0.0.1:5432, that means the listen_addresses config didn't take effect—double-check you edited the correct postgresql.conf (you can find its path with psql -U postgres -c 'SHOW config_file;' locally).

• Validate pg_hba.conf rules are loaded
  Log into the local PostgreSQL instance and run this query to see the active pg_hba.conf rules:

  SELECT * FROM pg_hba_file_rules;
  

  Look for your 0.0.0.0/0 rule, and ensure it's not being overridden by an earlier rule (pg_hba.conf processes rules top-to-bottom). If your rule has an entry in the error column, fix the syntax issue.

• Test raw network connectivity
  Even if you say there's no firewall, confirm the port is reachable from the client machine:

  nc -zv 10.54.48.59 5432
  # Or use telnet if nc isn't available
  telnet 10.54.48.59 5432
  

  If this fails, there might be a network routing issue between client and server, or the server isn't actually listening on the public IP.

• Check PostgreSQL logs for details
  The logs will give you precise info about why connections are being refused. Locate your log directory (find it with psql -U postgres -c 'SHOW log_directory;' locally) and look for recent entries around the time you tried to connect. Common issues here include config parsing errors or permission issues with the config files.

内容的提问来源于stack exchange，提问作者Madh