1

I am creating an app that uses a PostgreSQL database server, which I run on a Linux(20.1 Ubuntu) machine. However when the Linux machine is not on the same network as the client that is trying to connect to it, I get the error:

Is the server running on host xxx.xxx.xx.xx and accepting TCP/IP connections on port xxxx?

I have an exception in my firewall for the port, and since the fact that it all works on the same network implies to me, that my configurations are good, I don't understand what I'm getting wrong. I already tried everything here.

My pg_hba.conf looks like this:

local   replication     all                                     peer
host    replication     all             127.0.0.1/32            md5
host    replication     all             ::1/128         md5
host    all all 0.0.0.0/0   md5
host    all all ::/0        md5

And my postgresql.conf looks like this:

# - Connection Settings -

listen_addresses = '*'      # what IP address(es) to listen on;
                    # comma-separated list of addresses;
                    # defaults to 'localhost'; use '*' for all
                    # (change requires restart)
port = 5432             # (change requires restart)
max_connections = 100           # (change requires restart)

Can someone help me/ guide me how they managed this? I already vetted all the answers, but if I missed something, apologies for the duplication. Any help is appreciated.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
leocsi
  • 11
  • 3

3 Answers3

0

You must have made a basic mistake somewhere. Based on your report, that could be:

  • you didn't reload PostgreSQL after changing pg_hba.conf

  • you didn't restart PostgreSQL after changing listen_addresses

You can verify that by running

SHOW listen_addresses;
TABLE pg_hba_file_rules;

and checking if the result is what you expect.

Apart from that, there are the usual possibilities:

  • you are connecting to the wrong server or using the wrong port

  • you didn't get all the firewalls (there could be one on both client and server, as well as in between)

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
0

Don't forget to add 5432 to Firewall allowed ports.

root@vmi543210:~# ufw status numbered
Status: active

     To                         Action      From
     --                         ------      ----
[ 1] 888/tcp                    ALLOW IN    Anywhere
[ 2] 20/tcp                     ALLOW IN    Anywhere
[ 3] 21/tcp                     ALLOW IN    Anywhere
[ 4] 22/tcp                     ALLOW IN    Anywhere
[ 5] 80/tcp                     ALLOW IN    Anywhere
[ 6] 39000:40000/tcp            ALLOW IN    Anywhere
[ 7] 8888/tcp                   ALLOW IN    Anywhere
[ 8] 8443/tcp                   ALLOW IN    Anywhere
[ 9] 8443/udp                   ALLOW IN    Anywhere
[10] 443/tcp                    ALLOW IN    Anywhere
[11] 40678/tcp                  ALLOW IN    Anywhere
[12] 888/tcp (v6)               ALLOW IN    Anywhere (v6)
[13] 20/tcp (v6)                ALLOW IN    Anywhere (v6)
[14] 21/tcp (v6)                ALLOW IN    Anywhere (v6)
[15] 22/tcp (v6)                ALLOW IN    Anywhere (v6)
[16] 80/tcp (v6)                ALLOW IN    Anywhere (v6)
[17] 39000:40000/tcp (v6)       ALLOW IN    Anywhere (v6)
[18] 8888/tcp (v6)              ALLOW IN    Anywhere (v6)
[19] 8443/tcp (v6)              ALLOW IN    Anywhere (v6)
[20] 8443/udp (v6)              ALLOW IN    Anywhere (v6)
[21] 443/tcp (v6)               ALLOW IN    Anywhere (v6)
[22] 40678/tcp (v6)             ALLOW IN    Anywhere (v6)

As you can see, port 5432 is missing.
Add it with following command:

root@vmi543210:~# ufw allow 5432/tcp
Rule added
Rule added (v6)

Now it's allowed from both ipv4 and ipv6:

...
[11] 40678/tcp                  ALLOW IN    Anywhere
[12] 5432/tcp                   ALLOW IN    Anywhere
[13] 888/tcp (v6)               ALLOW IN    Anywhere (v6)
[14] 20/tcp (v6)                ALLOW IN    Anywhere (v6)
[15] 21/tcp (v6)                ALLOW IN    Anywhere (v6)
[16] 22/tcp (v6)                ALLOW IN    Anywhere (v6)
[17] 80/tcp (v6)                ALLOW IN    Anywhere (v6)
[18] 39000:40000/tcp (v6)       ALLOW IN    Anywhere (v6)
[19] 8888/tcp (v6)              ALLOW IN    Anywhere (v6)
[20] 8443/tcp (v6)              ALLOW IN    Anywhere (v6)
[21] 8443/udp (v6)              ALLOW IN    Anywhere (v6)
[22] 443/tcp (v6)               ALLOW IN    Anywhere (v6)
[23] 40678/tcp (v6)             ALLOW IN    Anywhere (v6)
[24] 5432/tcp (v6)              ALLOW IN    Anywhere (v6)

PS: Allowing connection to Postgresql port from outside, will expose it to bruteforce attack. Use Fail2Ban to create a police to ban this kind of abusive attacks.

Luiz Vaz
  • 1,669
  • 1
  • 19
  • 32
0

After some idle time I have finally found the problem, and it had nothing to do with psql. My network has a double router set-up, and I was only opening ports to the inner router instead of both of them. Thanks for those who tried to help anyway!

leocsi
  • 11
  • 3