Postgres: Troubleshooting Remote Access

Here's steps to work through, to ensure remote access to a PostgreSQL instance.

From a terminal, open the postgresql.conf file, with this:

sudo nano /etc/postgresql/<version>/main/postgresql.conf

NOTE: Replace <version> with the version you installed. Will be '16' as of this writing.

Locate the line with 'listen_addresses' and ensure it is set to listen on all adapters.
Or, to listen on a specific one for the host.

It is commented out, by default.
So, you will need to open up the listener to at least one adapter, or, all, like above.

Save and close the config file, and restart the postgres instance with this:

sudo systemctl restart postgresql

Open the pg_hba.conf file, to verify allowed source subnets and protocols, with this:

sudo nano /etc/postgresql/<version>/main/pg_hba.conf

NOTE: Same as before, replace <version> with the installed version. '16' as of this writing.

Make sure the allowed sources includes the subnet of your source hosts.

For this, you may need to add lines for each allowed subnet, like this:

host    all             all             192.168.1.0/24          md5

The above example allows access from several subnets.

Save and close the config.

And, reload it, with this:

sudo systemctl reload postgresql

Run this on the Postgres host, to see what ports the database engine is listening on:

sudo ss -tnlp | grep 5432

You will see something like this for a default install:

NOTE: By default, the engine listens on 5432.

Make sure the host firewall allows incoming connections, with this:

sudo ufw allow 5432/tcp

Now, verify connectivity from your remote client.