Problems Connecting to Remote PSQL on Linux Server

I have a linux server with PSQL installed (psql (15.2 (Ubuntu 15.2-1.pgdg22.04+1))). This is installed on Oracle Cloud.

I am trying to connect using the command

psql -h 129.213.17.88 -p 5432 -d breedingdb -U postgres

Where 129.213.17.88 is the public IP of the server in Oracle.

Error message:

psql: error: connection to server at "129.213.17.88", port 5432 failed: No route to host
	Is the server running on that host and accepting TCP/IP connections?

sudo systemctl status postgresql

I have changed postgresql.conf to include:

listen_addresses = '*'
port = 5432

I have changed pg_hba.conf to include:

host    all             all             0.0.0.0/0                md5
host    all             all             ::1/128                  md5

After that sudo systemctl restart postgresql

inbound rules on Oracle cloud

netstat -an | grep -i listen
tcp        0      0 127.0.0.53:53           0.0.0.0:*               LISTEN     
tcp        0      0 0.0.0.0:22              0.0.0.0:*               LISTEN     
tcp        0      0 0.0.0.0:111             0.0.0.0:*               LISTEN     
tcp        0      0 0.0.0.0:5432            0.0.0.0:*               LISTEN     
tcp6       0      0 :::22                   :::*                    LISTEN     
tcp6       0      0 :::111                  :::*                    LISTEN     
tcp6       0      0 :::5432                 :::*                    LISTEN

I have no problems connecting
locally

sudo nmap -sS 129.213.17.88 -p 5432

Starting Nmap 7.80 ( https://nmap.org ) at 2023-02-22 18:19 UTC
Nmap scan report for 129.213.17.88
Host is up (0.00042s latency).

PORT     STATE  SERVICE
5432/tcp closed postgresql

Nmap done: 1 IP address (1 host up) scanned in 0.13 seconds

ping 129.213.17.88

PING 129.213.17.88 (129.213.17.88) 56(84) bytes of data.
64 bytes from 129.213.17.88: icmp_seq=1 ttl=63 time=0.508 ms
64 bytes from 129.213.17.88: icmp_seq=2 ttl=63 time=0.498 ms
64 bytes from 129.213.17.88: icmp_seq=3 ttl=63 time=0.483 ms
^C
--- 129.213.17.88 ping statistics ---
3 packets transmitted, 3 received, 0% packet loss, time 2043ms

OCI support team has jump into a call with me and they were able to provide me with the solution.

As shown in the question, network level changes to allow connection to port 5432 were made previously in OCI (inbound rules).

I was missing changes at the firewall level:

Edit /etc/iptables/rules.v4

Add the following line where 5432 is PSQL port:

-A INPUT -p tcp -m state --state NEW -m tcp --dport 5432 -j ACCEPT

Save and Exit

Then, we need to restart iptables netfilter-persistent restart
To see if rules were applied:

iptables -L

It should have there:

ACCEPT     tcp  --  anywhere             anywhere             state NEW tcp dpt:postgresql

I have to mention this is unsecure. Do it at your own risk. It works for my purposes.

Then use the command psql -h ip_address -d dbname -U username -W to connect to a password protected remote database.

I tried connecting using the private and the public ip and they both work. Both intances were on the same VCN.

Successfully connected to breedingdb:

Did you check/add iptables rules for that port?

https://blogs.oracle.com/developers/post/enabling-network-traffic-to-ubuntu-images-in-oracle-cloud-infrastructure

Does sudo nmap -sS <private ip> -p 5432 show the port as open?

I had a similar problem and was able to solve it. In the process of solving it I was able to track specific errors, what caused them, and the solutions to those errors. First I installed telnet to help me debug connections to the server. I ran the following command:-

telnet <server IP eg.88.222.333.11> 5432

One of the errors that I encounter was

telnet: Unable to connect to remote host: No route to host

This error occurs when you have not configured your iptables to accept traffic from the device that you are working on. This can be solved by adding an entry to the IP Table

# update iptables and allow traffic from target IP eg. 105.222.22.222
sudo nano /etc/iptables/rules.v4
-A INPUT -p tcp -s 105.222.22.222/32 --dport 5432 -m state --state NEW,ESTABLISHED -j ACCEPT

# effect change to the iptables
sudo iptables-restore < /etc/iptables/rules.v4
sudo iptables -L

The other error that I encountered is

telnet: Unable to connect to remote host: Connection refused

This error occurs when you have not configured your postgresql.conf to listen for traffic from the device that you are working on. This can be solved by finding the listen_addresses entry and set to a specific IP or using a wildcard to listen for traffic from any device.

open the postgresql.conf

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

and modify the listen_addresses entry

...
listen_addresses = '*'
...

The last error that I encountered was when I tried to connect to the db:

psql -h 88.222.333.11 -p 5432 -U username db_name -W
> password

was

psql: error: connection to server at "88.222.333.11", port 5432 failed: FATAL:  no pg_hba.conf entry for host "105.222.22.222", user "blabla", database "foobar", SSL encryption
connection to server at "88.222.333.11", port 5432 failed: FATAL:  no pg_hba.conf entry for host "105.222.22.222", user "blabla", database "foobar", no encryption

This error occurs when you have not configured your pg_hba.conf to accept traffic from the device that you are working on. This can be solved by adding an entry to the pg_hba.conf file.
Open the file

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

and add an entry for your device at the end of the file

# TYPE  DATABASE        USER            ADDRESS                 METHOD
host    all             all             105.222.22.222/32       md5

then remember to restart your postgres sudo /etc/init.d/postgresql restart.

Something else to look out for is when adding an IP remember to append the /32 for it to match a specific IP for good security. Also, remember to add an ingress rule in your network security configuration to allow traffic from your device

sudo ufw allow 5432

try this command, it solves my problem