Introduction

Enabling remote connections to your PostgreSQL database can be useful for development, data analysis, or administrative tasks. However, it's essential to configure it securely to prevent unauthorized access.

Prerequisites

1. A PostgreSQL server installed and running.
2. Administrative access to the server where PostgreSQL is installed.

Steps

1. Edit the PostgreSQL Configuration File

First, you need to edit the postgresql.conf file to allow PostgreSQL to listen on all IP addresses or a specific IP address.

Locate the postgresql.conf File

The location of the postgresql.conf file depends on your installation. Common locations include /etc/postgresql/{version}/main/postgresql.conf, /var/lib/pgsql/{version}/data/, or a custom directory specified during installation.

Modify the postgresql.conf File

Open the postgresql.conf file with a text editor, such as nano or vim:

Find the line that starts with listen_addresses and modify it:

# Listen on all interfaces listen_addresses = '*' 

Alternatively, you can specify a single IP address:

# Listen on a specific IP 
address listen_addresses = '192.168.1.100' 

You can add multiple ip addressess and seperate them with comma

2. Update the Client Authentication Configuration

Next, you need to update the pg_hba.conf file to allow connections from specific IP addresses or IP ranges.

Locate the pg_hba.conf File

This file is usually located in the same directory as postgresql.conf.

Modify the pg_hba.conf File

Open the pg_hba.conf file with a text editor:

Add a line to allow connections from a specific IP address or subnet:  

# TYPE DATABASE USER ADDRESS METHOD

The md5 method specifies password authentication. You can choose other methods like scram-sha-256 if your PostgreSQL version supports it.

3. Restart PostgreSQL

After making these changes, restart the PostgreSQL service to apply the new configuration:

Or, for older versions or systems using init.d,(depending on your Ubuntu version):

sudo service postgresql restart 

4. Configure the Firewall

Ensure your firewall allows connections to the PostgreSQL port (default is 5432).

Using ufw (Uncomplicated Firewall)

If you're using ufw, you can allow connections to port 5432:

sudo ufw allow 5432/tcp 

5. Test the Remote Connection

From a remote machine, you can use psql or any PostgreSQL client to test the connection:

Conclusion

Enabling remote connections to your PostgreSQL database involves editing the postgresql.conf and pg_hba.conf files, restarting the PostgreSQL service, and configuring your firewall. By following these steps, you can securely allow remote access to your PostgreSQL database, making it accessible for various remote applications and users. Always remember to secure your database with strong passwords and consider additional security measures such as SSL connections.