postgresql query hangs indefinitely over VPN - new firewall

Question

We have a product where a local linux machine "SCANNER" does some polling on a local network and then stuffs information into a database in our cloud server over an OpenVPN connection, using pg_query (server is running PostgreSQL 9.5.5).

There is a PHP (5.5.9) daemon on SCANNER that checks the database in a 'while' loop for work to be done. This has always worked great, and continues to work great on all of our client networks, except for one, which has recently developed a bizarre problem.

After they upgraded their firewall (a Checkpoint 5200, and as far as we can tell, all the rules are correct to allow traffic from SCANNER to our cloud server over the VPN), one query in one script hangs indefinitely. Here are the symptoms we have noted:

---

Most of the time, the query works fine and the script continues. Every once in a while tough, the pg_query() call blocks and never returns. It's not that there's an error; the call literally blocks forever (or many hours until we manually restart).

This query has been the same for a long time, and we have never had this problem at any of our other clients, nor at this client until they changed their firewall.

We can tell from watching the pg_stat_activity table on the cloud server that the query does make it to the cloud, and then sits in that table forever. In every case, pg_stat_activity.state='idle' and pg_stat_activity.waiting=false

During this time, we can still ping the cloud server from SCANNER over the VPN, and we can continue to successfully query its remote database from other scripts on SCANNER, and from SCANNER's command line.

This client happens to have two different SCANNER machines, on different subnets but behind the same firewall. This problem can occur at any time on either one, but doesn't necessarily occur at the same time on both (at least not with any statistical significance).

If we restart the daemon, the problem is temporarily resolved. But it usually recurs sometime between 2 seconds and a few hours later.

---

We are looking for any input that might solve the problem, whether it is related to our application or the firewall itself (which we have been given permission to modify as needed). Feel free to ask any clarifying questions.

Thanks in advance!

Answer 1

We solved the problem. Technically, we never figured out exactly why it was happening, but UDP packets were getting received by the VPN server in an incorrect order, or sometimes not at all. The firewall didn't give us any indication that it was doing this, so as best we can figure it was an unlogged side-effect of CheckPoint's multiple layers of threat protection.

We switched over to using OpenVPN over TCP instead of UDP, and that seems to have solved the problem. Hopefully we don't suffer any adverse effects from that in the long-term!