PostgreSQL How to allow remote connections in PostgreSQL

By default, PostgreSQL is listening only for local connections (bounded to localhost). Additionally, running the web server and the database on the same machine is a common practice for small applications.

However, in order to scale your application, a more distributed approach is needed and one of the simplest things to do is to move the database on its own machine. After doing that, you will have to configure PostgreSQL to allow remote connections.

1. Find postgresql.conf

The first thing is to find the postgresql.conf file. Since its location differs based on the operating system, let's search for it.

find / -name "postgresql.conf"
# It will output the path of the file
/var/lib/pgsql/14.4/data/postgresql.conf

Once found, open it using your favorite text editor (we use nano).

2. Change the value of listen_addresses

Search for listen_addresses in postgresql.conf and replace its value with '*'.

listen_addresses = '*'

3. Configure pg_hba.conf

Now it's time to add new entries in pg_hba.conf.

nano sudo nano /etc/postgresql/14/main/pg_hba.conf 

If you don't know where your pg_hba.conf file is located, run the following statement in your psql instance to see its location:

SHOW hba_file;

Add the following lines at the end of the pg_hba.conf file:

# TYPE  DATABASE        USER            ADDRESS                 METHOD
host    all             all             0.0.0.0/0               md5
host    all             all             :/0                     md5

4. Restart PostgreSQL

For the changes to take effect, you have to restart PostgreSQL.

sudo systemctl restart postgresql 

5. Firewall access (optional)

This step is optional and should be done only if you have an active firewall. The port "5432" has to be open. To make sure it is, run the following command:

sudo ufw allow 5432

From now on, your PostgreSQL server will listen for connections coming from anywhere.