When your PostgreSQL server (app06) refuses connections from app05 despite proper pg_hba.conf configuration, there are typically three main culprits:
- PostgreSQL binding to wrong network interface
- Operating system firewall blocking traffic
- Network-level restrictions
The netstat output shows PostgreSQL only listening on 127.0.0.1. This needs to be changed in postgresql.conf:
# Locate this setting in postgresql.conf
listen_addresses = 'localhost' # Change to:
listen_addresses = '*' # Listen on all interfaces
# Or be specific:
listen_addresses = '10.55.129.31,localhost'
After modifying, restart PostgreSQL:
sudo systemctl restart postgresql
While ping works, test the specific port using more advanced tools:
# From app05:
nc -zv 10.55.129.31 5432
# Or:
telnet 10.55.129.31 5432
Even with lokkit disabled, check all potential firewall layers:
# Check iptables:
sudo iptables -L -n -v
sudo iptables -F # Temporary flush rules for testing
# For firewalld:
sudo firewall-cmd --list-all
sudo firewall-cmd --add-port=5432/tcp --permanent
sudo firewall-cmd --reload
Your current configuration looks correct, but let's make it more secure:
# TYPE DATABASE USER CIDR-ADDRESS METHOD
host all all 10.55.129.30/32 md5
host all all 10.55.129.31/32 md5
Remember to reload after changes:
sudo -u postgres psql -c "SELECT pg_reload_conf();"
For deeper troubleshooting, consider these commands:
# Check kernel routing:
ip route show
# Check interface configuration:
ip addr show
# Check for network-level ACLs:
traceroute 10.55.129.31
# Check SELinux status (if applicable):
sestatus
setsebool -P httpd_can_network_connect_db 1
If standard TCP/IP fails, try these approaches:
# SSH tunneling:
ssh -L 5433:localhost:5432 user@app06
# Then connect locally:
psql -h localhost -p 5433 -U postgres
# Or use connection strings:
psql "host=10.55.129.31 port=5432 user=postgres dbname=postgres"
After making all changes, verify with:
# On app06:
sudo netstat -tuln | grep 5432
# Should show 0.0.0.0:5432 or your specific IP
# From app05:
psql -h 10.55.129.31 -U postgres -c "SELECT inet_server_addr();"
When setting up PostgreSQL for remote access, several configuration files and network settings need to be properly adjusted. The key files involved are:
/var/lib/pgsql/data/pg_hba.conf # Host-based authentication
/var/lib/pgsql/data/postgresql.conf # Main configuration file
First, let's verify the listening address in postgresql.conf:
# In postgresql.conf
listen_addresses = '*' # Allows connections from all IPs
# or specify specific IPs: listen_addresses = 'localhost,10.55.129.31'
port = 5432 # Default PostgreSQL port
After making changes, always restart PostgreSQL:
sudo systemctl restart postgresql
# Or for older systems:
sudo /etc/init.d/postgresql restart
Even with lokkit indicating the firewall is off, we should double-check:
sudo iptables -L # Check firewall rules
sudo netstat -tulnp | grep postgres # Verify listening ports
From the client machine (APP05), we can perform these diagnostic tests:
# Test basic network connectivity
ping 10.55.129.31
# Test port accessibility
nc -zv 10.55.129.31 5432
# or
telnet 10.55.129.31 5432
# Test PostgreSQL connection directly
psql -h 10.55.129.31 -U postgres -d postgres
If connections are still failing, these commands can help identify the issue:
# Check if port is open on server
sudo ss -tulnp | grep 5432
# Check routing between servers
traceroute 10.55.129.31
# Check for packet filtering
sudo tcpdump -i any port 5432 -n
While troubleshooting, you might use 'trust' authentication, but for production:
# Recommended pg_hba.conf entry for production:
host all all 10.55.129.30/32 md5
After implementing these changes, test the connection again from the remote server:
psql -h app06 -U your_username -d your_database