0

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
Sirach Matthews
  • 31
  • 7
  • **exact error messages** please. Also your *full* `pg_hba.conf` file, with IP addresses masked. It sounds like you may be confused and thinking that `.pgpass` is server-side configuration. It is not. It is a saved-password file for the client. – Craig Ringer Dec 30 '16 at 06:04
  • @CraigRinger I have added the information requested in an edit to the original post. Since then I have tried the same `psql` command line with the addition of `-W` which created a password prompt. After entering the password, the result is the same; the connection times out. The server is listening on the specified port. – Sirach Matthews Dec 31 '16 at 01:12
  • If the problem simply was that the server is not listening on the port you would get `Connection refused`, and if the IP address was incorrect you should be getting `No route to host`. `Connection timed out` on the other hand indicates that the packets send to the server are silently lost without any error message being sent back with an error code. Possibly an administrator thought debugging network problems was too easy and configured a firewall to drop all error messages used for debugging. I would inspect a packet capture from both ends of the connection to find additional information. – kasperd Dec 31 '16 at 12:56

2 Answers2

0

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

Craig Ringer
  • 10,553
  • 9
  • 38
  • 59
0

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.

Sirach Matthews
  • 31
  • 7
  • At a guess it runs `iptables-save` on shutdown and `iptables-restore` on startup. Check your initscripts. BTW, if you can *telnet* to your server it's about 10 years out of date on basic security, I really hope you mean ssh. – Craig Ringer Jan 01 '17 at 06:11
  • @CraigRinger SSH from WAN to machine on the router's LAN, then telnet from there to router. I don't allow direct telnet from the WAN, only the LAN. – Sirach Matthews Jan 01 '17 at 19:43