Question 37784 in Ubuntu Question posted in
The official Ubuntu documentation can be found here.

Problems Connecting to Remote PSQL on Linux Server – Ubuntu

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
Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    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: breedingdb


  2. 0

    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?

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search