DB-유

Restricting MySQL Access with firewalld and ufw

· mysql, firewall, linux

A glance at the connection counters on one of my database servers was enough to convince me: port 3306 was getting probed all day. MySQL's own access control is per user and per host, but it only kicks in after a TCP connection has been accepted, so any scanner can still complete a handshake, read the server version from the greeting packet and start guessing passwords. A host firewall that drops everything except the application servers takes that surface away for a couple of commands.

I cover firewalld on CentOS 7/8 and ufw on Ubuntu 16.04/18.04, plus the Docker case, where published ports bypass both. Before you start, write down the IPs or subnets of every legitimate client (app servers, replicas, monitoring, the jump host), and keep console or out-of-band access handy. I always confirm port 22 is allowed before touching anything else; locking myself out of SSH is a mistake I only needed to make once.

What is listening

Check first:

ss -tlnp | grep -E '3306|33060'

If MySQL listens on 0.0.0.0:3306 or *:3306, the firewall is your only protection. Also note 33060, the X Protocol port that MySQL 8.0 opens by default; block or allow it the same way as 3306.

firewalld on CentOS 7/8

Check the active zone and current rules:

sudo firewall-cmd --get-active-zones
sudo firewall-cmd --zone=public --list-all

If the mysql service (which is just port 3306/tcp) was previously added for everyone, remove it:

sudo firewall-cmd --permanent --zone=public --remove-service=mysql

Then allow specific sources. The cleanest way is a dedicated zone containing only the trusted addresses:

sudo firewall-cmd --permanent --new-zone=dbclients
sudo firewall-cmd --permanent --zone=dbclients --add-source=10.0.1.30/32
sudo firewall-cmd --permanent --zone=dbclients --add-source=10.0.1.31/32
sudo firewall-cmd --permanent --zone=dbclients --add-source=10.0.5.0/24
sudo firewall-cmd --permanent --zone=dbclients --add-port=3306/tcp
sudo firewall-cmd --reload

Packets from those sources are matched by the dbclients zone (source-based zones take precedence over interface zones), where 3306 is open; everything else lands in public, where it is not. Alternatively, a rich rule in the existing zone does the same in one line per client:

sudo firewall-cmd --permanent --zone=public \
  --add-rich-rule='rule family="ipv4" source address="10.0.1.30/32" port port="3306" protocol="tcp" accept'
sudo firewall-cmd --reload

On CentOS 8, firewalld uses the nftables backend rather than iptables, but the firewall-cmd syntax is identical.

ufw on Ubuntu 16.04/18.04

sudo ufw status verbose

If it is inactive, allow SSH first, then enable:

sudo ufw allow OpenSSH
sudo ufw default deny incoming
sudo ufw default allow outgoing
sudo ufw enable

Then add MySQL rules per source:

sudo ufw allow from 10.0.1.30 to any port 3306 proto tcp comment 'app1'
sudo ufw allow from 10.0.1.31 to any port 3306 proto tcp comment 'app2'
sudo ufw allow from 10.0.5.0/24 to any port 3306 proto tcp comment 'reporting'

The comment keyword needs ufw 0.35 or later (Ubuntu 16.04 ships 0.35). If a blanket ufw allow 3306 exists from earlier, delete it:

sudo ufw status numbered
sudo ufw delete 3

Rules are evaluated in order, so make sure a specific allow from is not shadowed by an earlier deny.

Docker and its port publishing

If MySQL runs in a container with -p 3306:3306, Docker inserts its own iptables rules in the DOCKER chain that are evaluated before ufw or the firewalld public zone. The host firewall appears to work while the port is wide open. Two fixes:

  1. Bind the published port to the private interface only: -p 10.0.1.20:3306:3306 (or "10.0.1.20:3306:3306" in docker-compose.yml).
  2. Or set "iptables": false in /etc/docker/daemon.json and manage NAT yourself, which is more work than it sounds.

Option 1 is what I do; combined with a source rule for 10.0.1.20 in the host firewall it behaves as expected.

Make the MySQL accounts agree

The firewall says which hosts may reach the port; MySQL should say the same thing about accounts. Any account with Host = '%' undoes half the benefit:

SELECT user, host FROM mysql.user WHERE host IN ('%', '');

Replace them with accounts scoped to the same addresses or subnets you allowed in the firewall, such as 'app'@'10.0.1.30' or 'reporting'@'10.0.5.%'.

Test from both sides

From an allowed host:

mysql -h 10.0.1.20 -u app -p -e 'SELECT 1'

From a host that is not allowed, the connection must time out or be refused before authentication:

nc -zv -w 3 10.0.1.20 3306

Connection timed out (firewalld DROP default) or Connection refused (ufw REJECT) is what you want; succeeded means a rule is wrong. On the server, confirm the rules survived a reboot with firewall-cmd --list-all --zone=dbclients or ufw status, and check SHOW GLOBAL STATUS LIKE 'Aborted_connects' is no longer climbing from unknown sources.

Gotchas

  • firewall-cmd --reload and the rule is gone: it was added without --permanent. Runtime rules disappear on reload or reboot; add --permanent and reload, or use --runtime-to-permanent.
  • ERROR: Bad source address from ufw: it does not accept /32 for a single host in some versions; write the bare address.
  • Application server can connect, replica cannot: replication uses the same 3306 port but from the replica's address, which is easy to forget. Add it to the zone or a rule.
  • ERROR 2003 (HY000): Can't connect to MySQL server on '10.0.1.20' (110) from an allowed host: check that the client's outbound address is the one you allowed; hosts with multiple interfaces or NAT often present a different source IP. sudo tcpdump -ni eth0 port 3306 on the database server shows the real one.
  • Rules look right but connections still reach MySQL from anywhere: Docker (see above), or firewalld is inactive and a stray iptables rule set is running. systemctl status firewalld and iptables -L -n -v will tell.

Not sophisticated, but a leaked password matters far less when the port isn't reachable.