Do you want to secure your MySQL flavoured database server connections, and have discovered that you cannot do it like you secured your webserver?
Then I have answers for you, at least if you're using a Debian.
This post has multiple parts:
Requirements
- Debian Wheezy, Jessie, or Stretch
- GNU certtool or openssl
- MySQL client software and libraries, either as shipped by Debian, or via Oracle's repositories for MySQL 5.5, 5.6, or 5.7, or MariaDB's repositories for 10.x
- MySQL server software, similarly
Gotchas
- Debian's MySQL (Wheezy, Jessie) and MariaDB (Stretch) packages, and MySQL Community Edition precompiled binaries, including WorkBench, all use yaSSL/WolfSSL, which support an extremely limited set of crypto features, compared to OpenSSL.
- Having OpenSSL installed on the server or client does not help with the above situation.
- MariaDB ships precompiled binaries that are linked against OpenSSL.
- All keys and certificates must stay within the minimum supported feature sets (lowest common denominator) of yaSSL and OpenSSL. In essence, this means RSA keys hashed with SHA2-256. Do not attempt using DSA, ECDSA, etc.
- TLS v1.2 is impossible with older versions of yaSSL, it's only possible with the version bundled in MySQL 5.7.10 or more recent, or with MariaDB binaries that are linked with OpenSSL versions supporting TLS v1.2. Restricting to v1.2 is therefore unwise.
Part A: Certificate Generation
Either follow the
MySQL 5.5 instructions religiously, or use
certtool. Skip to Part B if you have this under control.
1. Create template for self-signed certificate
Suggested name:
ca.cfg
# An organization name
organization = "Blogger Bakkushan TLS Non-Authority"
# Geographical location things
state = "Oslo"
country = "NO"
locality = "Oslo"
# Common name (certificate "name", if you like)
cn = "Bakkushan CA 1"
# Serial number (unimportant, but you need it)
serial = 1
# Expiration - after this long, you need to generate a new CA cert
expiration_days = 3652
# This is a CA certificate, which will be used for signing
ca
cert_signing_key
signing_key
encryption_key
2. Create a self-signed CA certificate
certtool --generate-privkey --rsa --bits 4096 --outfile ca-key.pem
certtool --generate-self-signed --rsa --load-privkey ca-key.pem --template ca.cfg --outfile ca-cert.pem
3. Create template for server certificates
Filename e.g. "
server-cert.cfg".
# An organization name
organization = "Blogger Bakkushan MySQL Services"
# Geographical location things
state = "Oslo"
country = "NO"
locality = "Oslo"
# Common name (typically the full DNS name of the server)
cn = "mysql.bakkushan.example"
dns_name = "mysql.bakkushan.example"
# Got a DNS alias? Ok!
dns_name = "alias.copycat.example"
# Serial number (unimportant, but you need it)
serial = 1
# Expiration - after this long, you need to generate a new server
expiration_days = 365
# This is a server certificate, ignore the "www"
tls_www_server
# This is not a CA certificate, which will be used for signing
signing_key
encryption_key
4. Create a server certificate
certtool --generate-privkey --rsa --bits 3072 --outfile server-key.pem
certtool --generate-request --load-privkey server-key.pem --template server-cert.cfg --outfile server-req.pem
certtool --generate-certificate --rsa --load-request server-req.pem --load-ca-privkey ca-key.pem --load-ca-certificate ca-cert.pem --template server-cert.cfg --outfile server-cert.pem
5. Optional: Create template for client certificates
Filename e.g. "
client-cert.cfg".
# An organization name
organization = "A Client Organization"
# Geographical location things
state = "Stockholm"
country = "SE"
locality = "Stockholm"
# Common name (something to identify with)
cn = "Medel-Svensson"
# Serial number (unimportant, but you need it)
serial = 1
# Expiration - after this long, you need to generate a new server
expiration_days = 365
# This is a client certificate, ignore the "www"
tls_www_client
# This is not a CA certificate, which will be used for signing
signing_key
encryption_key
6. Optional: Create a client certificate
certtool --generate-privkey --rsa --bits 3072 --outfile client-key.pem
certtool --generate-request --load-privkey client-key.pem --template client-cert.cfg --outfile client-req.pem
certtool --generate-certificate --rsa --load-request client-req.pem --load-ca-privkey ca-key.pem --load-ca-certificate ca-cert.pem --template client-cert.cfg --outfile client-cert.pem
Part B: Server Configuration
Note 1: you need to get the paths
precisely correct, or nothing will work.
Note 2: path, directory/folder, and file permissions must
all permit the database server instance to read the files mentioned. Directories should need only execution privileges. Whether you do so via ACLs (
setfacl) or old-fashioned
chown and
chmod is up to you.
I like placing these files in
/etc/mysql/ssl, a directory that you'll need to create.
ssl-ca = /etc/mysql/ssl/ca-cert.pem
ssl-cert = /etc/mysql/ssl/server-cert.pem
ssl-key = /etc/mysql/ssl/server-key.pem
Part C: Client Configuration
If you created client certificates, you need to distribute the following files:
ca-cert.pem
client-cert.pem
client-key.pem
The client needs to specify the corresponding options for CA certificate, client certificate, and client key. Doing so ensures that you can connect while validating the server certificate, and permitting the server to validate the client certificate. If no validation is required, you can connect, but you must request SSL.
Please note that the latter option leaves your TLS connection open for man-in-the-middle attacks.
Part D: Database Restart
Yeah, you need to restart the database server instance to load the new config.
systemctl restart mysql.service
Part E: Database Grant Statements
I really recommend that you restrict access to TLS, and if you have provided client certificates, require that as well. The following example grants wide privileges to anyone connecting with the correct username and password from anywhere, but limits simultaneous connections for that user:
GRANT ALL PRIVILEGES ON clientdatabase TO 'client'@'%'
REQUIRE SSL
IDENTIFIED BY 'correct horse battery staple'
WITH max_user_connections 10;
This example restricts the client certificate:
GRANT ALL PRIVILEGES ON clientdatabase TO 'client'@'%'
REQUIRE SUBJECT '/C=SE/ST=Stockholm/L=Stockholm/
O=A Client Organization/
CN=Medel-Svensson'
AND ISSUER '/C=NO/ST=Oslo/L=Oslo/
O=Blogger Bakkushan TLS Non-Authority/
CN=Bakkushan CA 1'
IDENTIFIED BY 'correct horse battery staple'
WITH max_user_connections 10;
Conclusion
It is a bit cumbersome to set up properly, as you cannot rely on global CAs, on DANE/TLSA, or other conveniences you may know from HTTPS, POP, IMAP, and SMTP, but if you do not try to be too clever, it is easy enough.
Sources
