Let them stay together. In the last YEARS, I have seen quite often that users, when installing a product such as PXC, instead of spending five minutes to understand what to do just run iptables -F and save. In short, they remove any rules for their firewall.
With this post, I want to show you how easy it can be to do the right thing instead of putting your server at risk. I'll show you how a slightly more complex setup like PXC (compared to MySQL), can be easily achieved without risky shortcuts. iptables is the utility used to manage the chains of rules used by the Linux kernel firewall, which is your basic security tool. Linux comes with a wonderful firewall built into the kernel.
As an administrator, you can configure this firewall with interfaces like ipchains — which we are not going to cover — and iptables, which we shall talk about. iptables is stateful, which means that the firewall can make decisions based on received packets. This means that I can, for instance, DROP a packet if it's coming from bad-guy.com. I can also create a set of rules that either will allow or reject the package, or that will redirect it to another rule. This potentially can create a very complex scenario.
However, for today and for this use case let's keep it simple… Looking at my own server:
iptables -v -L
Chain INPUT (policy ACCEPT 0 packets, 0 bytes)
pkts bytes target prot opt in out source destination
250K 29M ACCEPT all -- any any anywhere anywhere state RELATED,ESTABLISHED
6 404 ACCEPT icmp -- any any anywhere anywhere
0 0 ACCEPT all -- lo any anywhere anywhere
9 428 ACCEPT tcp -- any any anywhere anywhere state NEW tcp dpt:ssh
0 0 ACCEPT tcp -- any any anywhere anywhere state NEW tcp dpt:mysql
0 0 ACCEPT tcp -- any any anywhere anywhere
210 13986 REJECT all -- any any anywhere anywhere reject-with icmp-host-prohibited
Chain FORWARD (policy ACCEPT 0 packets, 0 bytes)
pkts bytes target prot opt in out source destination
0 0 REJECT all -- any any anywhere anywhere reject-with icmp-host-prohibited
Chain OUTPUT (policy ACCEPT 241K packets, 29M bytes)
pkts bytes target prot opt in out source destination
That's not too bad, my server is currently accepting only SSH and packets on port 3306. Please note that I used the -v option to see more information like IN/OUT and that allows me to identify that actually row #3 is related to my loopback device, and as such it's good to have it open. The point is that if I try to run the PXC cluster with these settings it will fail, because the nodes will not be able to see each other. A quite simple example when try to start the second node of the cluster:
2018-05-21T17:56:14.383686Z 0 [Note] WSREP: (3cb4b3a6, 'tcp://10.0.0.21:4567') connection to peer 584762e6 with addr tcp://10.0.0.23:4567 timed out, no messages seen in PT3S
Starting a new node will fail, given that the connectivity will not be established correctly. In the Percona documentation there is a notes section in which we mention that these ports must be open to have the cluster working correctly.:
- 3306 For MySQL client connections and State Snapshot Transfer that use the mysqldump method.
- 4567 For Galera Cluster replication traffic, multicast replication uses both UDP transport and TCP on this port.
- 4568 For Incremental State Transfer.
- 4444 For all other State Snapshot Transfer.
Of course, if you don’t know how to do it that could be a problem, but it is quite simple. Just use the following commands to add the needed rules:
iptables -I INPUT 2 --protocol tcp --match tcp --dport 3306 --source 10.0.0.1/24 --jump ACCEPT iptables -I INPUT 3 --protocol tcp --match tcp --dport 4567 --source 10.0.0.1/24 --jump ACCEPT iptables -I INPUT 4 --protocol tcp --match tcp --dport 4568 --source 10.0.0.1/24 --jump ACCEPT iptables -I INPUT 5 --protocol tcp --match tcp --dport 4444 --source 10.0.0.1/24 --jump ACCEPT iptables -I INPUT 6 --protocol udp --match udp --dport 4567 --source 10.0.0.1/24 --jump ACCEPT
Once you have done this check the layout again and you should have something like this:
[root@galera1h1n5 gal571]# iptables -L Chain INPUT (policy ACCEPT) target prot opt source destination ACCEPT all -- anywhere anywhere state RELATED,ESTABLISHED ACCEPT tcp -- 10.0.0.0/24 anywhere tcp dpt:mysql ACCEPT tcp -- 10.0.0.0/24 anywhere tcp dpt:tram ACCEPT tcp -- 10.0.0.0/24 anywhere tcp dpt:bmc-reporting ACCEPT tcp -- 10.0.0.0/24 anywhere tcp dpt:krb524 ACCEPT udp -- 10.0.0.0/24 anywhere udp dpt:tram ACCEPT icmp -- anywhere anywhere ACCEPT tcp -- anywhere anywhere tcp dpt:ssh ACCEPT tcp -- anywhere anywhere tcp dpt:mysql REJECT all -- anywhere anywhere reject-with icmp-port-unreachable Chain FORWARD (policy ACCEPT) target prot opt source destination REJECT all -- anywhere anywhere reject-with icmp-port-unreachable Chain OUTPUT (policy ACCEPT) target prot opt source destination
Try to start the secondary node, and — tadaaa — the node will connect, will provision itself, and finally will start correctly. All good? Well not really, you still need to perform a final step. We need to make our server accessible also for PMM monitoring agents. You have PMM right? If you don’t take a look here and you will want it. :D Anyhow PMM will not work correctly with the rules I have, and the result will be an empty set of graphs when accessing the server statistics. Luckily, PMM has a very easy way to help you identify the issue:
[root@galera1h1n5 gal571]# pmm-admin check-network PMM Network Status Server Address | 192.168.1.52 Client Address | 192.168.1.205 * System Time NTP Server (0.pool.ntp.org) | 2018-05-24 08:05:37 -0400 EDT PMM Server | 2018-05-24 12:05:34 +0000 GMT PMM Client | 2018-05-24 08:05:37 -0400 EDT PMM Server Time Drift | OK PMM Client Time Drift | OK PMM Client to PMM Server Time Drift | OK * Connection: Client --> Server -------------------- ------- SERVER SERVICE STATUS -------------------- ------- Consul API OK Prometheus API OK Query Analytics API OK Connection duration | 1.051724ms Request duration | 311.924µs Full round trip | 1.363648ms * Connection: Client <-- Server -------------- ------------ -------------------- ------- ---------- --------- SERVICE TYPE NAME REMOTE ENDPOINT STATUS HTTPS/TLS PASSWORD -------------- ------------ -------------------- ------- ---------- --------- linux:metrics galera1h1n5 192.168.1.205:42000 DOWN NO NO mysql:metrics gal571 192.168.1.205:42002 DOWN NO NO When an endpoint is down it may indicate that the corresponding service is stopped (run 'pmm-admin list' to verify). If it's running, check out the logs /var/log/pmm-*.log When all endpoints are down but 'pmm-admin list' shows they are up and no errors in the logs, check the firewall settings whether this system allows incoming connections from server to address:port in question. Also you can check the endpoint status by the URL: http://192.168.1.52/prometheus/targets
What you want more? You have all the information to debug and build your new rules. I just need to open the ports 42000 42002 on my firewall:
iptables -I INPUT 7 --protocol tcp --match tcp --dport 42000 --source 192.168.1.1/24 --jump ACCEPT iptables -I INPUT 8 --protocol tcp --match tcp --dport 42002 --source 192.168.1.1/24 --jump ACCEPT
Please note that we are handling the connectivity for PMM using a different range of IPs/subnet. This because it is best practice to have PXC nodes communicate to a dedicated network/subnet (physical and logical). Run the test again:
* Connection: Client <-- Server -------------- ------------ -------------------- ------- ---------- --------- SERVICE TYPE NAME REMOTE ENDPOINT STATUS HTTPS/TLS PASSWORD -------------- ------------ -------------------- ------- ---------- --------- linux:metrics galera1h1n5 192.168.1.205:42000 OK YES YES mysql:metrics gal571 192.168.1.205:42002 OK YES YES
Done … I just repeat this on all my nodes and I will have set my firewall to handle the PXC related security. Now that all my settings are working well I can save my firewall’s rules:
iptables-save > /etc/sysconfig/iptables
For Ubuntu you may need some additional steps as for (https://help.ubuntu.com/community/IptablesHowTo#Using_iptables-save.2Frestore_to_test_rules) There are some nice tools to help you even more, if you are very lazy, like UFW and the graphical one, GUFW. Developed to ease iptables firewall configuration, ufw provides a user friendly way to create an IPv4 or IPv6 host-based firewall. By default UFW is disabled in Ubuntu. Given that ultimately they use iptables, and their use is widely covered in other resources such as the official Ubuntu documentation, I won't cover these here.
Conclusion
Please don't make the mistake of flushing/ignoring your firewall, when to make this right is just a matter of 5 commands. It's easy enough to be done by everyone and it's good enough to stop the basic security attacks. Happy MySQL (and PXC) to everyone.