I’m trying to connect pgAdmin to the PostgreSQL databases on my VPS, but I’m not sure what changes I need to make or what settings to enable to achieve this. Here are some details that might be useful:

my_pc_ip_address (my private IP address)
my_public_ip_address
my_VPS_ip_address

I tried to configure the /etc/postgresql/14/main/postgresql.conf file to allow connections from localhost and my local PC using my public IP address instead of my private IP address:

listen_addresses = ‘localhost,my_public_ip_address’

I also added two lines to the /etc/postgresql/14/main/pg_hba.conf file to allow connections from my public IP:

# Database administrative login by Unix domain socket
local   all             postgres                                peer

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     peer
# IPv4 local connections:
host    all             all             127.0.0.1/32            scram-sha-256
# IPv6 local connections:
host    all             all             ::1/128                 scram-sha-256

# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     peer
host    replication     all             127.0.0.1/32            scram-sha-256
host    replication     all             ::1/128                 scram-sha-256
host    all             all             127.0.0.1/32            md5
host    all             all             my_public_ip_address/32        md5

However, after saving the changes and checking the listening ports, I noticed that my public IP address is not listed:

tcp        0      0 127.0.0.1:5432          0.0.0.0:*               LISTEN      42968/postgres      
tcp6       0      0 ::1:5432                :::*                    LISTEN      42968/postgres 

Could this be related to my UFW rules?

To                         Action      From
--                         ------      ----
Nginx Full                 ALLOW       Anywhere                  
22                         ALLOW       Anywhere                  
27017                      ALLOW       Anywhere                  
8686                       ALLOW       Anywhere                  
5432                       ALLOW       my_public_ip_address            
Nginx Full (v6)            ALLOW       Anywhere (v6)             
22 (v6)                    ALLOW       Anywhere (v6)             
27017 (v6)                 ALLOW       Anywhere (v6)             
8686 (v6)                  ALLOW       Anywhere (v6) 

I tried to connect using the command:

 psql -h my_vps_ip_address -U postgresql_user -d database_name

But it took a while and then returned an error saying, “Is the server running and accepting TCP/IP connections?”

So my questions are:

Do I need to change the public_ip_address to my_private_ip_address?
What files do I need to modify to allow these connections, and what should I add to them?
Am I missing something?