The postgres on VPS cant be accessed

So i had setup a postgres on my vps. But when try to connect it from beekeper studio, it cant connect, error connect refused. So i try to check on my vps and ask the copilot to solve the issue. This is the issue, we need to change the config of the postgresql.conf and pg_hba.conf to permission to public access. After that restart the service. Oh check the firewall to if it allowed or not.

1. So change the postgresql.conf to listen address to all

sudo nano /etc/postgresql/*/main/postgresql.conf

Look for or uncomment:

listen_addresses = '*'
port = 5432

After that restart the postgres service

sudo systemctl restart postgresql

2. Then after that change the pg_hba.conf to be accessed using md5

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

Add a line like:

host    all             all             0.0.0.0/0               md5

or (i am never try this)

host    all             all             <your-ip>/32            md5

Then restart the service postgres again:

sudo systemctl restart postgresql

3. If it still cant connect you can check the firewall, if it allowed or not.

sudo ufw status

If it enabled or active, allow postgresql connection:

sudo ufw allow 5432/tcp

4. Connect to the postgres server in client using psql

psql -h <your-vps_ip> -U <username> -d <database>

it works for me