Visitor III

Solved

Fortinet Firewall Support for Postgres Cluster

Forum|Forum|3 years ago
December 12, 2022
22 replies
8963 views

Environment

PostgresSQL = 15

Patroni = patroni-2.1.4-1.rhel8.x86_64 & patroni-etcd-2.1.4-1.rhel8.x86_64
OS = RHEL = 8.x

Detail

We have a setup of Postgres Cluster with 2 nodes. On top of Postgres Cluster a load balancer (name HAproxy) is also installed. Additionally the Postgres Cluster use Patroni software for HA related activities against Postgres Cluster. Patroni update the HAproxy to let him know who is Active / Leader node of Postgres Cluster thru API. So HAproxy know who is Active node of Postgres Cluster and send traffic to only Active node of Postgres.

My Query: Does Fortinet Firewall have support for Postgres Cluster and can we use Fortinet Firewall in place of HAproxy in above mentioned environment

FortiGate

Best answer by gfleming

As mentioned in a previous response to make it work for the intra-interface (i.e. Trust to Trust) you must disable "Preserve Client IP" in the Server Load Balance config and you must enable NAT on the FW Policy.

gfleming

Staff

Fortigate supports some basic load balancing/proxying. Review the docs for more details: https://docs.fortinet.com/document/fortigate/7.2.3/administration-guide/713497/virtual-server-load-balance

Not sure how Patroni works but it's possible you can use the FortiGate to determine this without using Patroni.

Fortinet also makes FortiADC a far more advanced application delivery controller. https://www.fortinet.com/content/dam/fortinet/assets/data-sheets/FortiADC.pdf

ZAHIDHASEEBAuthor

Visitor III

Do you have any PostgresSQL related fortigate article ? Usually any load balancer also do a round robin traffic distribution of traffic towards nodes which is not is the case of Postgres HA, Actually the HAproxy connect with Patroni thru API on port 8008 to validate the health of node. is it possible that you may share something specific to Postgres ?

gfleming

Staff

The capabilities of the FortiGate load balancer are outlined in the link I provided. If they aren't sufficient as I mentioned there is also FortiADC which is more advanced.

ZAHIDHASEEBAuthor

Visitor III

I am still waiting if Fortinet may respond on my query. Below is my environment details for reference again in pictorial view

1- HAPROXY Load Balancer status code 200 - 503.jpg

2- HAPROXY Load Balancer status code 200 - 503.jpg

"ENVIRONMENT DETAIL"

"KONG API Gateway" is a two tier product. KONG Application & KONG database. KONG application connect with KONG database thru load balancer (HAPROXY) in case there is a need to HA the "KONG API Gateway" product. In our environment we have two KONG applications and two database machines. The database is PostgreSQL and configured as Active/Passive

Both KONG Applications send requests to load balancer on port 5000(as example port) and then load balancer forward that request to KONG database (Active node) on port 5432.

Now you may have a question that how the load balancer recognize the Active database node so that the load balancer forward the request on to port 5432 at only Active database node. Below is the way to understand

8121 is a rest api port listening on both database machines. In our case please consider 10.10.64.11 and 10.10.64.12 are database machines
- If any node from 10.10.64.11,10.10.64.12 is running as a Active Database, the machine will respond to Load Balancer on request 'GET /' with http status code 200,
- If any node from 10.10.64.11,10.10.64.12 is running as a Replica/Slave Database, the machine will respond to Load Balancer on request 'GET /' with http status code 503.

So when the Load Balancer receive status code 200 from any 10.10.64.11 OR 10.10.64.12 machine from port 8121, the Load Balancer will recognize it as Active node and forward all inbound requests(received on Load Balancer port 5000) to 5432 port on that machine who return status code 200

"AGENDA"
We need to replace HAPROXY as load balancer by FORTIGATE. We have FORTIGATE 200F. We are unable to get a way in FORTIGATE load balancer tool so that we can replace HAPROXY. Following is a detail link of configuration for your reference

https://digitalis.io/blog/postgresql/part1-postgresql-ha-patroni-etcd-haproxy/
https://digitalis.io/blog/postgresql/deploying-postgresql-for-high-availability-with-patroni-etcd-and-haproxy-part-2/

ZAHIDHASEEBAuthor

Visitor III

curl -vv http://192.168.3.78:5432
* Rebuilt URL to: http://192.168.3.78:5432/
* Trying 192.168.3.78...
* TCP_NODELAY set
* Connected to 192.168.3.78 (192.168.3.78) port 5432 (#0)
> GET / HTTP/1.1
> Host: 192.168.3.78:5432
> User-Agent: curl/7.61.1
> Accept: */*
>
* Empty reply from server
* Connection #0 to host 192.168.3.78 left intact
curl: (52) Empty reply from server

curl -vv http://192.168.3.78:8121
* Rebuilt URL to: http://192.168.3.78:8121/
* Trying 192.168.3.78...
* TCP_NODELAY set
* Connected to 192.168.3.78 (192.168.3.78) port 8121 (#0)
> GET / HTTP/1.1
> Host: 192.168.3.78:8121
> User-Agent: curl/7.61.1
> Accept: */*
>
* HTTP 1.0, assume close after body
< HTTP/1.0 200 OK
< Server: BaseHTTP/0.6 Python/3.6.8
< Date: Thu, 26 Jan 2023 19:17:51 GMT
< Content-Type: application/json
<
* Closing connection 0
{"state": "running", "postmaster_start_time": "2023-01-12 19:28:36.824401+05:00", "role": "master", "server_version": 150001, "xlog": {"location": 86103592}, "timeline": 11, "replication": [{"usename": "replicator", "application_name": "ISC-DGB-2", "client_addr": "192.168.3.79", "state": "streaming", "sync_state": "async", "sync_priority": 0}], "dcs_last_seen": 1674760670, "database_system_identifier": "7178857672087907798", "patroni": {"version": "2.1.4", "scope": "PostgreSQL--HA"}}

curl -vv http://192.168.3.79:5432
* Rebuilt URL to: http://192.168.3.79:5432/
* Trying 192.168.3.79...
* TCP_NODELAY set
* Connected to 192.168.3.79 (192.168.3.79) port 5432 (#0)
> GET / HTTP/1.1
> Host: 192.168.3.79:5432
> User-Agent: curl/7.61.1
> Accept: */*
>
* Empty reply from server
* Connection #0 to host 192.168.3.79 left intact
curl: (52) Empty reply from server

curl -vv http://192.168.3.79:8121
* Rebuilt URL to: http://192.168.3.79:8121/
* Trying 192.168.3.79...
* TCP_NODELAY set
* Connected to 192.168.3.79 (192.168.3.79) port 8121 (#0)
> GET / HTTP/1.1
> Host: 192.168.3.79:8121
> User-Agent: curl/7.61.1
> Accept: */*
>
* HTTP 1.0, assume close after body
< HTTP/1.0 503 Service Unavailable
< Server: BaseHTTP/0.6 Python/3.6.8
< Date: Thu, 26 Jan 2023 19:19:57 GMT
< Content-Type: application/json
<
* Closing connection 0
{"state": "running", "postmaster_start_time": "2023-01-26 23:02:25.784164+05:00", "role": "replica", "server_version": 150001, "xlog": {"received_location": 86103592, "replayed_location": 86103592, "replayed_timestamp": "2023-01-26 23:04:54.269609+05:00", "paused": false}, "timeline": 11, "dcs_last_seen": 1674760790, "database_system_identifier": "7178857672087907798", "patroni": {"version": "2.1.4", "scope": "PostgreSQL--HA"}}

As I described in my initial posts that first backend server Active/Master role verified via port 8121 response as http status code 200 against GET / request then the connection with backend server established on port 5432 . Please check the pictures which I posted earlier

ZAHIDHASEEBAuthor

Visitor III

As I described in earlier posts that first the status code 200 will be verified on port 8121 for Active/Master role on node against GET / request and then connection established on port 5432 i.e., PostgreSQL port

gfleming

Staff

Yes I know the status code is what we are looking for. I was curious if there was any data being sent so we could use that in the health check. Can you make it so that the server sends data in the GET request? Can you customize it? That would help so you can leverage the HTTP host check in FortiGate.

Otherwise we can use the JSON output to match on the other port but that might be a bit more complicated. I'm not sure if you can just use ""role": "master"" as matched content or if you'd need the entire JSON string....

ZAHIDHASEEBAuthor

Visitor III

I tried below mentioned ways one by one this but no success.

1- I mentioned master in the match content box and leave the URL box empty but could not get success

2- I have to create two health check boxes because I only have the IP and not index.htm which I can use so the situation would be like below and added both boxes in the Virtual Server. (no success)

3- I change the URL with only / in start. I saw this at somewhere on internet(no success)

4- I used the point 2 and 3 with port 5432 but still no success

gfleming

Staff

URL should only be what comes after the server host. So in your list of "real servers" the FortiGate will use the single health check on all of them. So the URL field should be whatever goes after the IP address. In your case it's just "/". Or you can probably leave it blank, I reckon.

So again, just one health check, and assign it to your Virtual Server object.

ZAHIDHASEEBAuthor

Visitor III

No success. Will update after support response

gfleming

Staff

Just realized if you are using "master" as your match content it will treat both servers as alive since "postmaster" exists in both responses. You need to find some unique string that only the master shows. Looks like "replication" is unique to both.

Also can you confirm you enabled NAT on the FW Policy and disabled "Preserve Client IP" on the Load Balancer object?

ZAHIDHASEEBAuthor

Visitor III

1- After changing Network Type to TCP under virtual server only one backend server will be able to get telnet request even though its configured as Standby under Real Server.

2- However I changed both Backend servers to Active - Active and only Leader/Active node is able to get telnet connection. I also checked after failover too.

3- The environment work well when I configure the Firewall policy with different zones like its working DMZ to Trust. However could not work with Trust to Trust. I am still in lab and will keep update with result

Health Check

Virtual Server

Firewall Policy

gflemingAnswer

Staff

As mentioned in a previous response to make it work for the intra-interface (i.e. Trust to Trust) you must disable "Preserve Client IP" in the Server Load Balance config and you must enable NAT on the FW Policy.

ZAHIDHASEEBAuthor

Visitor III

1- In DMZ to Trust rule, my environment is working when I only select Type is TCP

2-In DMZ to Trust rule, my environment does not work when I select Type is HTTP (either NAT or Preserve Client IP enable or disable)

3- In Trust to Trust rule my environment does not work when I select Type is HTTP or TCP (either NAT or Preserve Client IP enable or disable)

Preserve client IP is only available when Type is HTTP

Preserve client IP is not available when Type is TCP so I cant choose Preserve Client IP

ZAHIDHASEEBAuthor

Visitor III

@gfleming Bundle of thanks for your back to back followup on my issue, I am able to complete the activity. Couple of thing I corrected at my side and your suggestions made the environment workable.

Sign up

Login with SSO

Login to the community

Login with SSO