Troubleshooting and Configuration Optimization for PostgreSQL Remote Connections

Problem Description and Initial Diagnosis

When deploying PostgreSQL 9.3 on Ubuntu Server 14.04, local connections via the psql command work correctly, but remote clients like pgAdmin III fail to establish connections, returning the error: "could not connect to server: Connection refused". Service status checks confirm that the PostgreSQL instance is running normally with port 5432 in a listening state.

Analyzing network connections with the netstat -na command reveals crucial information:

tcp        0      0 127.0.0.1:5432          0.0.0.0:*               LISTEN

This indicates that the PostgreSQL service is bound only to the local loopback address 127.0.0.1 and not listening on the server's physical network interfaces, which is the root cause of remote connection refusals.

Core Configuration Parameters

PostgreSQL's network connection behavior is governed by two key configuration files: postgresql.conf and pg_hba.conf. The former defines basic operational parameters, while the latter manages client authentication rules.

In the postgresql.conf file, the listen_addresses parameter determines which network interfaces the service listens on. The default configuration is typically:

#listen_addresses = 'localhost'

This configuration restricts the service to local access, with the comment symbol # rendering the line inactive. To enable remote connections, the parameter must be uncommented and modified:

listen_addresses = '*'

The wildcard * signifies listening on all available network interfaces, including both loopback and physical interfaces.

Access Control Configuration

After modifying the listen address, client access rules must be configured in the pg_hba.conf file. This file employs host-based authentication to control which clients can connect to which databases.

A typical remote access configuration example is:

host    all         all         192.168.1.0/24        md5

This rule permits all hosts in the 192.168.1.0/24 network segment to connect to all databases using MD5 password authentication. Adjust the IP range and security policies according to the actual network environment.

Configuration Implementation and Verification

After modifying the configuration files, the PostgreSQL service must be restarted for changes to take effect:

sudo service postgresql restart

Steps to verify correct configuration include:

1. Check service status: service postgresql status
2. Confirm network listening: netstat -na | grep 5432
3. Test remote connection: Attempt connection using pgAdmin or other client tools

After re-running netstat -na, output similar to the following should appear:

tcp        0      0 0.0.0.0:5432            0.0.0.0:*               LISTEN

This confirms that the service is now listening on all network interfaces, and remote connections should establish successfully.

Additional Considerations

In practical deployments, firewall configuration must also be considered. Ubuntu systems typically use UFW for firewall management, requiring that port 5432 be opened for remote clients:

sudo ufw allow 5432/tcp

Referencing similar cases, even with correct PostgreSQL configuration, connection failures can occur if the firewall blocks requests. Therefore, comprehensive troubleshooting should include checks at both the network and application layers.

Security Best Practices

While setting listen_addresses to * resolves connectivity issues, the principle of least privilege should be followed in production environments. It is advisable to specify particular IP addresses or network segments based on actual requirements to mitigate unnecessary security risks.

Additionally, authentication rules in pg_hba.conf should incorporate strong password policies and appropriate access controls to ensure overall database service security.