Keywords: PostgreSQL | Remote Connection | Connection refused | pg_hba.conf | listen_addresses
Abstract: This article provides a comprehensive analysis of the "psql: could not connect to server: Connection refused" error when establishing remote connections to PostgreSQL. Through configuration of listen_addresses and pg_hba.conf files, combined with firewall settings and network diagnostic tools, it offers a complete troubleshooting workflow from basic configuration to advanced diagnostics. The article includes specific configuration examples and code demonstrations to help users quickly identify and resolve connection issues.
Problem Background and Error Analysis
When establishing remote database connections to PostgreSQL, users frequently encounter the "psql: could not connect to server: Connection refused" error. This error indicates that the client cannot establish a TCP/IP connection to the PostgreSQL server. The error message typically suggests checking whether the server is running on the specified host and accepting TCP/IP connections on port 5432.
Core Configuration Parameters
To resolve remote connection issues, it's essential to understand two key PostgreSQL configuration files: postgresql.conf and pg_hba.conf.
In the postgresql.conf file, the listen_addresses parameter controls which network interfaces the server listens on. The default value is 'localhost', meaning the server only accepts local connections. To allow remote connections, this parameter must be set to '*', indicating that the server should listen on all available network interfaces:
listen_addresses = '*'
In the pg_hba.conf file, client authentication rules need to be configured. The default configuration typically only allows local connections. To permit remote connections from any IP address, add the following rule:
host all all 0.0.0.0/0 md5
This rule allows all databases and all users to connect from any IP address using MD5 encrypted passwords.
Detailed Configuration Steps
First, navigate to the PostgreSQL configuration directory. On Ubuntu systems, this is typically located at:
cd /etc/postgresql/9.x/main/
Open the postgresql.conf file using a text editor:
sudo vi postgresql.conf
Locate the listen_addresses parameter and ensure its value is set to '*'. If the line is commented out, uncomment it and modify the value.
Next, open the pg_hba.conf file:
sudo vi pg_hba.conf
Add the remote connection rule at the end of the file. Ensure the rule's position is correct, as PostgreSQL applies rules in the order they appear in the file.
After configuration, restart the PostgreSQL service to apply the changes:
sudo /etc/init.d/postgresql restart
Network and Firewall Configuration
Beyond PostgreSQL configuration, network and firewall settings must be checked. Use the nmap tool to verify port status:
nmap -p 5432 <host_ip>
If port 5432 shows as closed, it indicates that connection requests are not reaching the PostgreSQL service at all. This could be due to firewall blocking or PostgreSQL not properly listening on remote interfaces.
On Ubuntu systems, check firewall status using:
sudo ufw status
If PostgreSQL port needs to be opened:
sudo ufw allow 5432/tcp
Docker Environment Considerations
When running PostgreSQL in Docker containers, network configuration becomes more complex. The PostgreSQL service inside the container typically only listens on the container's internal network. To access from the host machine, ports must be properly exposed in Docker configuration:
expose:
- "127.0.0.1:5432:5432"
This configuration maps the container's port 5432 to the host's 127.0.0.1:5432. For external network access, SSH tunneling or Docker network adjustments may be necessary.
Troubleshooting Workflow
When encountering connection refusal errors, follow this systematic troubleshooting approach:
- Verify PostgreSQL service is running
- Check listen_addresses configuration
- Confirm pg_hba.conf contains appropriate remote connection rules
- Restart PostgreSQL service to apply configuration
- Check firewall and network policies
- Use network diagnostic tools to verify port accessibility
- In Docker environments, verify port mapping configuration
Security Considerations
While setting listen_addresses to '*' and pg_hba.conf to 0.0.0.0/0 resolves connection issues, it introduces security risks. In production environments, consider:
- Restricting listen_addresses to specific IP addresses or network segments
- Using stricter IP range limitations in pg_hba.conf
- Implementing SSH tunnels or VPN for secure connections
- Regularly updating PostgreSQL versions to patch security vulnerabilities
Code Examples and Best Practices
Here's a complete configuration example demonstrating secure remote access setup:
# postgresql.conf
listen_addresses = '192.168.1.100,localhost'
# pg_hba.conf
# Allow connections from specific network segment
host all all 192.168.1.0/24 md5
# Allow connections from specific IP
host all all 203.0.113.45/32 md5
Through proper configuration and strict security policies, reliable remote database connections can be established while maintaining security.