DB-유

Allowing Remote Connections to MySQL Safely

· mysql, security, networking

Every time a developer says "just let it listen on everything and grant root from %, it's only for a week", I feel a little sick. The default is right: Ubuntu's MySQL listens only on 127.0.0.1, and on CentOS root only exists for localhost. But the moment the app moves to its own server, or a reporting tool on another host needs read access, you have to open the door, and doing it carelessly is how databases end up in breach reports.

This is what I do on MySQL 5.7 (CentOS 7 or Ubuntu 16.04) to allow access to exactly the hosts and users that need it, with encryption on the wire. Have the client IPs or subnets written down first, plus root on both the database and the firewall.

Bind to the right interface

In /etc/my.cnf (CentOS) or /etc/mysql/mysql.conf.d/mysqld.cnf (Ubuntu), under [mysqld]:

[mysqld]
bind-address = 10.0.1.20
port         = 3306

Bind to the server's private interface address rather than 0.0.0.0 when the machine also has a public interface. MySQL 5.7 accepts a single address here (or * for all), so if you need both IPv4 and IPv6 use :: and rely on the firewall. Restart the service and check:

sudo systemctl restart mysqld
ss -tlnp | grep 3306

Accounts scoped to a host or subnet

I never open root remotely. Create per-application users whose Host part restricts where they may connect from:

CREATE USER 'shopapp'@'10.0.1.30' IDENTIFIED BY 'App-Str0ng-P@ss';
GRANT SELECT, INSERT, UPDATE, DELETE ON shop.* TO 'shopapp'@'10.0.1.30';

-- a whole subnet, using a netmask
CREATE USER 'reporting'@'10.0.2.0/255.255.255.0' IDENTIFIED BY 'Rep0rt-P@ss';
GRANT SELECT ON shop.* TO 'reporting'@'10.0.2.0/255.255.255.0';
FLUSH PRIVILEGES;

MySQL matches the netmask form ip/netmask only; CIDR notation such as 10.0.2.0/24 is not understood in 5.7 and would be treated as a literal host name. Wildcards like '10.0.1.%' also work and are easier to read for whole /24 networks.

Firewall: those clients only

CentOS 7 with firewalld, using a rich rule so the port is not open to the world:

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 --permanent --zone=public \
  --add-rich-rule='rule family="ipv4" source address="10.0.2.0/24" port port="3306" protocol="tcp" accept'
sudo firewall-cmd --reload

Ubuntu 16.04 with ufw:

sudo ufw allow from 10.0.1.30 to any port 3306 proto tcp
sudo ufw allow from 10.0.2.0/24 to any port 3306 proto tcp
sudo ufw status numbered

If the server is in a cloud environment, mirror the same rule in the security group. Belt and braces is fine for a database port.

Require TLS for remote users

MySQL 5.7 generates self-signed certificates in the data directory on first start (ca.pem, server-cert.pem, server-key.pem) and enables them automatically. Check:

SHOW VARIABLES LIKE '%ssl%';

have_ssl should be YES. Then force encryption for the remote accounts:

ALTER USER 'shopapp'@'10.0.1.30' REQUIRE SSL;
ALTER USER 'reporting'@'10.0.2.0/255.255.255.0' REQUIRE SSL;

For a stricter setup, REQUIRE X509 demands a client certificate as well, and require_secure_transport = ON in my.cnf refuses every unencrypted TCP connection server-wide. If you replace the self-signed certificates with ones from your internal CA, set ssl-ca, ssl-cert and ssl-key under [mysqld] and distribute ca.pem to the clients.

Shrinking the blast radius

A few extra settings I put on every remotely reachable server:

[mysqld]
max_connect_errors   = 100
skip-name-resolve    = 1
local_infile         = 0

skip-name-resolve makes MySQL match accounts by IP only, which is faster and avoids DNS-based tricks; it means every account must use an IP or wildcard in the Host part, not a host name. local_infile=0 disables LOAD DATA LOCAL, which a compromised server can abuse to read client files.

Testing it from both sides

From the client host:

mysql -h 10.0.1.20 -u shopapp -p --ssl-mode=REQUIRED -e "SHOW SESSION STATUS LIKE 'Ssl_cipher'"

A non-empty cipher such as DHE-RSA-AES256-SHA confirms TLS. From a host that should not have access, mysql -h 10.0.1.20 -u shopapp -p must hang or be refused at the TCP level, not reach the authentication step. On the server, SELECT user, host, ssl_type FROM mysql.user; should show ANY or X509 for remote accounts.

Errors you'll probably see

  • ERROR 2003 (HY000): Can't connect to MySQL server on '10.0.1.20' (111): the server is not listening on that address, or the firewall rejected the packet. Check ss -tlnp and firewall-cmd --list-rich-rules / ufw status.
  • ERROR 1130 (HY000): Host '10.0.1.31' is not allowed to connect to this MySQL server: no account matches that client IP. Create one for the right host; do not fix it with '%'.
  • ERROR 3159 (HY000): Connections using insecure transport are prohibited while --require_secure_transport=ON: the client did not negotiate TLS. Older Connector/J and PHP versions need useSSL=true or MYSQLI_CLIENT_SSL explicitly.
  • ERROR 1129 (HY000): Host '10.0.1.30' is blocked because of many connection errors: an application with a wrong password hit max_connect_errors. Fix the password and run mysqladmin flush-hosts.

Listener, firewall, account host. If one of them is wrong, the other two still hold. That's the whole idea.