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
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
2
Answers
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:
Save and Exit
Then, we need to restart iptables
netfilter-persistent restart
To see if rules were applied:It should have there:
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:![breedingdb](data:image/svg+xml,%3Csvg%20xmlns='http://www.w3.org/2000/svg'%20viewBox='0%200%200%200'%3E%3C/svg%3E)
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?