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
3
  • 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. Dec 30, 2016 at 6: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. Dec 31, 2016 at 1: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, 2016 at 12:56

2 Answers 2

0

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

2
  • I'm not using port 5432. I've re-configured to use a different port. Dec 31, 2016 at 15:54
  • The same advice applies. Jan 1, 2017 at 6:10
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.

2
  • 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. Jan 1, 2017 at 6: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. Jan 1, 2017 at 19:43

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .