How to Enable Remote Connection in PostgreSQL

To enable remote connections in PostgreSQL, edit the postgresql.conf file to listen on all IP addresses by setting listen_addresses = '*'. Then, update pg_hba.conf to allow remote client IPs with appropriate authentication. Finally, restart the PostgreSQL service to apply changes.

Syntax

To enable remote connections, you need to modify two main configuration files:

• postgresql.conf: Controls which IP addresses PostgreSQL listens on.
• pg_hba.conf: Controls which clients can connect and how they authenticate.

Key settings:

• listen_addresses = '*' in postgresql.conf allows PostgreSQL to listen on all network interfaces.
• Adding a line like host all all 0.0.0.0/0 md5 in pg_hba.conf allows all IPs to connect with password authentication.

plaintext

# postgresql.conf
listen_addresses = '*'

# pg_hba.conf
host    all             all             0.0.0.0/0               md5

Example

This example shows how to enable remote connections for all users from any IP address using password authentication.

bash

# Step 1: Edit postgresql.conf
# Locate and set:
listen_addresses = '*'

# Step 2: Edit pg_hba.conf
# Add this line at the end:
host    all             all             0.0.0.0/0               md5

# Step 3: Restart PostgreSQL service
sudo systemctl restart postgresql

Output

PostgreSQL service restarted successfully.

Common Pitfalls

Common mistakes when enabling remote connections include:

• Forgetting to set listen_addresses to include remote IPs (default is localhost only).
• Not adding proper entries in pg_hba.conf to allow remote IPs.
• Not restarting PostgreSQL after changes.
• Firewall blocking PostgreSQL port (default 5432).

Example of a wrong and right pg_hba.conf entry:

plaintext

# Wrong (only local connections allowed):
host    all             all             127.0.0.1/32            md5

# Right (allow all IPs):
host    all             all             0.0.0.0/0               md5

Quick Reference

Setting	Description	Example Value
listen_addresses	IP addresses PostgreSQL listens on	'*' to listen on all
pg_hba.conf entry	Client IP and authentication method	host all all 0.0.0.0/0 md5
PostgreSQL port	Default port for connections	5432
Service restart	Apply configuration changes	sudo systemctl restart postgresql

Key Takeaways

Set listen_addresses = '*' in postgresql.conf to allow remote IPs.

Add appropriate host entries in pg_hba.conf to permit remote clients.

Restart PostgreSQL service after configuration changes.

Ensure firewall allows incoming connections on port 5432.

Use secure authentication methods like md5 or scram-sha-256.