TCP connection issues between HTTP and Postgres DB in different networks

[IOMadness]

I'm unsure if this is an issue, or if I've just not configured it properly. I am having an issue w/ the FROST HTTP container contacting Postgres on the backend. What happens is, after a period of downtime, when a POST is sent, the initial POST doesn't get written to the DB, but subsequent POST's will write just fine. This also happens if POSTing a batch of requests.

We are running FROST in docker containers, with the HTTP container behind a proxy (NGINX, in another container) on a server in our DMZ, and the DB on a separate, bare-metal server on our internal network. The firewall between the server running the HTTP container and the DB has a timeout for connections after an hour, which I believe is causing the connection between FROST and the DB to be severed, while FROST still thinks the connection is active.

Doing a tcpdump, I can see when this happens that NGINX sends a SYN packet to the HTTP container, but then the HTTP container immediately sends a PSH to the DB using an existing open port as the source, and the DB never responds. Then, once you send another POST, a SYN is properly sent from FROST to the DB on a new source port, and the POST is successful.

My question is, how do I configure FROST to reset these connections before our firewall terminates the connection if its inactive? I've been sorting through the documentation, and none of the settings appear to have an impact on the behavior.

I tried setting the persistence.queryTimeout to 3600 (seconds), restarted the container, and the behavior was still observed.

Additionally, I have tried updating /etc/sysctl.conf within the HTTP container as well as on the localhost to the following:
net.ipv4.tcp_keepalive_intvl = 75
net.ipv4.tcp_keepalive_probes = 9
net.ipv4.tcp_keepalive_time = 1800

After updating the values (restarted the container, ran sysctl --system on the localhost), the behavior was still observed.

Is this an issue with FROST, or is there a configuration option I'm missing?

EDIT: I've verified that in the HTTP container, it keeps an existing connection open to the DB, even tho no data is sent. However, tcp keepalive doesn't appear to be doing its job, as the connection remains after an hour has gone by with no activity.

[hylkevds]

Having an internal firewall between your servers, that kills connections, sounds like a bad idea...
You could try the connection option tcpKeepAlive in the PostgreSQL connection string.
Or have a simple client that regularly does a GET on v1.1/Things...

[IOMadness]

The server that runs the FROST HTTP container is in a DMZ, that is exposed to the public internet. The researchers we're setting this up for use it to send data from sensors out in the field, so it must be reachable through the public internet. The DB is on a separate, internal network, with a firewall to protect from anything in the DMZ, since we don't want hosts to have access to our internal network in case they're compromised. The firewall kills idle connections after an hour, since there's no data being sent... why wouldn't you want to kill the connection?

For the PostgreSQL connection string, where would that be used? Is that something I'd need to recompile FROST to do? Can I do it in the Docker compose file?

Setting up a cronjob to do a GET on v1.1/Things every so often may be an option.

[hylkevds]

Well, you just found out why you shouldn't just kill idle connections :)
Dropping connections in this way is bad because both ends are not notified of the connection being dropped. Just because no packets have gone over the connection doesn't mean it's not being used. There may still be state associated with the connection.

Anyway, the connection string is where you configured your external DB to be used, as described in the documentation.

[IOMadness]

Point of clarification, does the tcpKeepAlive parameter in the connection string turn on keep alive for the DB host, or the HTTP container, (or both)?

[hylkevds]

It's for the database connection from the FROST instance to the configured database instance.

[IOMadness]

Alright, this seems to have done the trick.

I set the tcp keep alive sysctl values in the HTTP container with the following section added:
sysctls:
- net.ipv4.tcp_keepalive_intvl=30
- net.ipv4.tcp_keepalive_probes=10
- net.ipv4.tcp_keepalive_time=600

I then updated both the auth and db persistence strings for the HTTP and MQTT containers to include the tcpKeepAlive value:
- persistence_db_url=jdbc:postgresql:///sensorthings?tcpKeepAlive=true
- auth_db_url=jdbc:postgresql:///sensorthings?tcpKeepAlive=true

did a:
docker compose -f docker-compose.yaml down
docker compose -f docker-compose.yaml up -d

..then was back in business, and can observe the keep alive probes going out every 10 minutes.

It wasn't immediately clear how the DB string was supposed to be set (or that it even could be set), and the documentation could've provided a bit more direction. Altogether it was a bit of a puzzle, but I'm glad I was able to get it together and have it working the way its supposed to.