Home / server / Questions / 823323
Accepted
Sirach Matthews
Asked: 2016-12-30 21:15:18 +0800 CST

Unable to connect to postgresql 9.6 server from 9.4 client

  • 772

Specs

Server

  • Environment: FreeBSD (FreeNAS Jail)
  • Postgresql version: 9.4
  • Listening_address: '*'
  • Listening_port: 8096
  • pg_hba.conf entry: host all all 0.0.0.0/0 md5

Client

  • Environment: Linux Ubuntu 16.0.4 LTS
  • Postgresql version: 9.6

Issue

I am trying to host my company's database on a server which can be accessed by employees remotely. The employee environments will vary between Linux flavors and Windows. My test environment is with a Linux client. So far, I have been unable to successfully connect the client to the server.

What I've Tried

I created a postgresql password file, ".pgpass" with the pertinent information entered in the format host:port:database:username:password. When I runpsql -h host -p port -U username database from the client, I receive a timeout error. However, when I run the same command on the server, it prompts me for my password and I am connected to my database.

Questions

What am I doing wrong here? Am I missing a step, or is there an issue with the different versions of postgresql? Does the problem lie in the difference between the Linux and BSD environments?

--EDIT--

Exact Error Message

psql: could not connect to server: Connection timed out
        Is the server running on host "host.domain.com" (###.###.###.###) and accepting
        TCP/IP connections on port [port]?

pg_hba.conf

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     trust

# IPv4 local connections:
host    all             all             127.0.0.1/32            trust

# IPv6 local connections:
host    all             all             ::1/128                 trust

# IPv4 remote connections:
host    all             all             0.0.0.0/0               md5
linux freebsd postgresql truenas ubuntu-16.04

2 Answers

  1. I expect you have a firewall blocking incoming connections on port 5432.

    • 0
  2. Best Answer

    The problem ended up being an issue with the server-side router's routing tables. The GUI showed everything as it should be, and the port was reported as being open, and pointing to the postgresql server.

    The Solution

    I connected to the server side router via telnet and manually setup the routing tables and forced a restart.
    Once connected to the router, I issued the following commands:

    iptables -t nat -I PREROUTING -p tcp --dport <PORT> -j DNAT --to <SERVER_IP>:<SERVER_PORT>
iptables -I FORWARD -p tcp -d <SERVER_IP> --dport <SERVER_PORT> -j ACCEPT
reboot

    I acknowledge that normally reboot would undo the effects of the previous two commands. However, this worked in my case. Previous to running the iptables commands, I attempted a number of reboot sequences to no avail. I am uncertain why mapping with iptables failed to work until after a reboot. Feel free to comment.

    In the end the issue was not with postgresql or my setup thereof, but an issue with the server side routing tables.

    I hope this answer helps someone struggling with similar issues. Even if the router setup LOOKS fine, does not mean that it is fine.

    • 0