How to Set Up MariaDB Over SSL/TLS on AlmaLinux
Categories:
Securing database connections is a critical aspect of modern database administration. Using SSL/TLS (Secure Sockets Layer / Transport Layer Security) to encrypt connections between MariaDB servers and their clients is essential to protect sensitive data in transit. AlmaLinux, a stable and secure RHEL-based distribution, is an excellent platform for hosting MariaDB with SSL/TLS enabled.
This guide provides a comprehensive walkthrough to set up MariaDB over SSL/TLS on AlmaLinux. By the end, you’ll have a secure MariaDB setup capable of encrypted client-server communication.
Table of Contents
- Introduction to SSL/TLS in MariaDB
- Prerequisites
- Installing MariaDB on AlmaLinux
- Generating SSL/TLS Certificates
- Configuring MariaDB for SSL/TLS
- Configuring Clients for SSL/TLS
- Testing the SSL/TLS Configuration
- Enforcing SSL/TLS Connections
- Troubleshooting Common Issues
- Conclusion
1. Introduction to SSL/TLS in MariaDB
SSL/TLS ensures secure communication between MariaDB servers and clients by encrypting data in transit. This prevents eavesdropping, data tampering, and man-in-the-middle attacks. Key benefits include:
- Data Integrity: Ensures data is not tampered with during transmission.
- Confidentiality: Encrypts sensitive data such as credentials and query results.
- Authentication: Verifies the server and optionally the client’s identity.
2. Prerequisites
Before starting, ensure you have:
AlmaLinux Installed: A running instance of AlmaLinux with root or sudo access.
MariaDB Installed: MariaDB server installed and running on AlmaLinux.
Basic Knowledge: Familiarity with Linux commands and MariaDB operations.
OpenSSL Installed: Used to generate SSL/TLS certificates:
sudo dnf install -y openssl
3. Installing MariaDB on AlmaLinux
If MariaDB is not already installed, follow these steps:
Install MariaDB:
sudo dnf install -y mariadb-server mariadbStart and Enable the Service:
sudo systemctl start mariadb sudo systemctl enable mariadbSecure MariaDB Installation:
sudo mysql_secure_installationFollow the prompts to set a root password, remove anonymous users, and disallow remote root login.
4. Generating SSL/TLS Certificates
To enable SSL/TLS, MariaDB requires server and client certificates. These can be self-signed or issued by a Certificate Authority (CA).
Step 1: Create a Directory for Certificates
Create a directory to store the certificates:
sudo mkdir /etc/mysql/ssl
sudo chmod 700 /etc/mysql/ssl
Step 2: Generate a Private Key for the Server
openssl genrsa -out /etc/mysql/ssl/server-key.pem 2048
Step 3: Create a Certificate Signing Request (CSR)
openssl req -new -key /etc/mysql/ssl/server-key.pem -out /etc/mysql/ssl/server-csr.pem
Provide the required information (e.g., Common Name should match the server’s hostname).
Step 4: Generate the Server Certificate
openssl x509 -req -in /etc/mysql/ssl/server-csr.pem -signkey /etc/mysql/ssl/server-key.pem -out /etc/mysql/ssl/server-cert.pem -days 365
Step 5: Create the CA Certificate
Generate a CA certificate to sign client certificates:
openssl req -newkey rsa:2048 -nodes -keyout /etc/mysql/ssl/ca-key.pem -x509 -days 365 -out /etc/mysql/ssl/ca-cert.pem
Step 6: Set Permissions
Ensure the certificates and keys are owned by the MariaDB user:
sudo chown -R mysql:mysql /etc/mysql/ssl
sudo chmod 600 /etc/mysql/ssl/*.pem
5. Configuring MariaDB for SSL/TLS
Step 1: Edit the MariaDB Configuration File
Modify /etc/my.cnf to enable SSL/TLS:
sudo nano /etc/my.cnf
Add the following under the [mysqld] section:
[mysqld]
ssl-ca=/etc/mysql/ssl/ca-cert.pem
ssl-cert=/etc/mysql/ssl/server-cert.pem
ssl-key=/etc/mysql/ssl/server-key.pem
Step 2: Restart MariaDB
Restart MariaDB to apply the changes:
sudo systemctl restart mariadb
6. Configuring Clients for SSL/TLS
To connect securely, MariaDB clients must trust the server’s certificate and optionally present their own.
Copy the
ca-cert.pemfile to the client machine:scp /etc/mysql/ssl/ca-cert.pem user@client-machine:/path/to/ca-cert.pemUse the
mysqlclient to connect securely:mysql --host=<server_ip> --user=<username> --password --ssl-ca=/path/to/ca-cert.pem
7. Testing the SSL/TLS Configuration
Check SSL Status on the Server: Log in to MariaDB and verify SSL is enabled:
SHOW VARIABLES LIKE 'have_ssl';Output:
+---------------+-------+ | Variable_name | Value | +---------------+-------+ | have_ssl | YES | +---------------+-------+Verify Connection Encryption: Use the following query to check if the connection is encrypted:
SHOW STATUS LIKE 'Ssl_cipher';A non-empty result confirms encryption.
8. Enforcing SSL/TLS Connections
To enforce SSL/TLS, update the user privileges:
Log in to MariaDB:
sudo mysql -u root -pRequire SSL for a User:
GRANT ALL PRIVILEGES ON *.* TO 'secure_user'@'%' REQUIRE SSL; FLUSH PRIVILEGES;Test the Configuration: Try connecting without SSL. It should fail.
9. Troubleshooting Common Issues
SSL Handshake Error
Cause: Incorrect certificate or key permissions.
Solution: Verify ownership and permissions:
sudo chown mysql:mysql /etc/mysql/ssl/* sudo chmod 600 /etc/mysql/ssl/*.pem
Connection Refused
Cause: Firewall blocking MariaDB’s port.
Solution: Open the port in the firewall:
sudo firewall-cmd --permanent --add-service=mysql sudo firewall-cmd --reload
Client Cannot Verify Certificate
- Cause: Incorrect CA certificate on the client.
- Solution: Ensure the client uses the correct
ca-cert.pem.
10. Conclusion
Setting up MariaDB over SSL/TLS on AlmaLinux enhances the security of your database by encrypting all communications between the server and its clients. With this guide, you’ve learned to generate SSL certificates, configure MariaDB for secure connections, and enforce SSL/TLS usage. Regularly monitor and update certificates to maintain a secure database environment.
By following these steps, you can confidently deploy a secure MariaDB instance, safeguarding your data against unauthorized access and network-based threats.