Secure Your Database Backups: Encrypting and Decrypting with OpenSSL on Linux/Mac

Data security is crucial in today’s world. Even the safest backups can be compromised. This guide will walk through encrypting and decrypting our database backups using OpenSSL on your Linux or Mac. By safeguarding our backups, we’re adding an extra layer of protection for our sensitive data. Hopefully, this will protect you from ransomware; if the Cracker got our database backup, at least it’s not possible/easy to get raw/plain data.

Prerequisites

1. Linux/Mac terminal
2. OpenSSL installed (pre-installed on most systems)

Step 1: Generate Master Key

The master key will be used to encrypt and decrypt the data key, which is then used for the actual encryption of the database backup file.

This command will create a 256-bit master key and save it to a file named “master_20240731.key”

Generate the master key using the following command:

$ openssl rand -base64 32 > master_20240731.key

Step 2: Generate Data Key

This command will create a 256-bit data key and save it to a file named “data_20240731.key”

$ openssl rand -base64 32 > data_20240731.key

Step 3: Encrypt the Data Key Using the Master Key

Encrypt the data key with the master key to ensure its security, This command uses AES-256-CBC encryption to encrypt the data_20240731.key file and outputs data_20240731.key.enc.

$ openssl enc -aes-256-cbc -pbkdf2 -salt -in data_20240731.key -out data_20240731.key.enc -pass file:./master_20240731.key

Step 4: Encrypt the Database Backup

Now, we will use the data key to encrypt your database backup. This command encrypts “database_testing_dummy.sql” into “database_testing_dummy.sql.enc” using the data key. let’s start with the sample “database_testing_dummy.sql” as below

$ cat database_testing_dummy.sql
-- DROP DATABASE IF EXISTS to ensure it can be re-created fresh for testing
DROP DATABASE IF EXISTS blog_database;
CREATE DATABASE blog_database;
USE blog_database;

-- Create Users Table
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100),
    password VARCHAR(255)
);

-- Insert dummy users data
INSERT INTO users (username, email, password) VALUES
('john_doe', 'john@example.com', 'password123'),
('jane_smith', 'jane@example.com', 'password456'),
('alice_jones', 'alice@example.com', 'password789');

-- Create Posts Table
CREATE TABLE posts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT,
    title VARCHAR(255),
    body TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

-- Insert dummy posts data
INSERT INTO posts (user_id, title, body) VALUES
(1, 'My First Blog Post', 'This is the content of my first blog post.'),
(2, 'A Day in the Life', 'Here is a post about my daily routine.'),
(1, 'Tech Trends', 'Latest trends in technology in 2023.'),
(3, 'Cooking Tips', 'How to cook the perfect steak.'),
(2, 'Travel Guide', 'Top 10 places to visit in Ireland.');

-- Create Comments Table
CREATE TABLE comments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    post_id INT,
    user_id INT,
    comment_body TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (post_id) REFERENCES posts(id),
    FOREIGN KEY (user_id) REFERENCES users(id)
);

-- Insert dummy comments data
INSERT INTO comments (post_id, user_id, comment_body) VALUES
(1, 2, 'Great post! Thanks for sharing.'),
(2, 1, 'Nice read, I enjoyed it.'),
(3, 3, 'Very informative, thank you!'),
(5, 1, 'This place looks amazing!'),
(4, 2, 'Awesome tips! Going to try this out soon.'),
(2, 3, 'Good daily routine tips.'),
(1, 3, 'Thanks for the insights.');

-- Select statements to verify data insertion (Optional)
-- SELECT * FROM users;
-- SELECT * FROM posts;
-- SELECT * FROM comments;%

encrypt this “database_testing_dummy.sql” with this command

$ openssl enc -aes-256-cbc -pbkdf2 -salt -in database_testing_dummy.sql -out database_testing_dummy.sql.enc -pass file:./data_20240731.key

let’s check the output of file “database_testing_dummy.sql.enc”

❯ cat database_testing_dummy.sql.enc
�m�*C�@S�/牀��!� M�`�&#�'1���_��깳:}hy�I__�J��W����U`Xz�IΓym3ܓ��s��~$�0`�hd$4�]�ԋ*�>&^ŗ�3��Fܪ�)\!�
                                                                                                  �)Ge����|�x��!�I�y&��~��_�l+H�(d�8`�#���
                                                                                                                                          t<6�i�
                                                                                                                                                #њ�QG��ę�{�
��I�a�� ��>�:�q���OIS��
��˓�Է؊H���cQ��ߧE���X�8��>�i-;e#"�Jp.�u�D�N7��[��]�FC��?q�>�amO!�ً��d� ݭNP�R��ı�U�X��W+1k�-�ل�ؤK���D��$-}(Vtr�:=�}��t�-E�q��5M���G�̴$N�7 �c��&\��lI��9��E�ĨD��4�(��Y~BSC$i�T����0��~U|��Q���r�(��&�_Y��W�Ngd��ѽ�}���ݨ�+�z�5I�R�1 b����_b1O�@

,�u�;>.���
�z�ʹP,zas[�ʄ0��������(/��HK�}���B�w�O��t����C)��~�kd�c�{���S�t���
                                     �Sy
�@}��`�:��|��lj�I���:��
                        i�6�Ȑ�Kz�"��
r�V?<v�k�V�M��.��$��PLB��g�z�m����͇��qtB$�8���-�1��41������ž�k5��q`��!Ũf3e��άb���٪���;+��^��ng|�H���!���~S�U�a��E�1lq����T}�݂4ƥ�����o���@J��>�d��7x��!���ٞ9B'
�G��q�Z
       T��a��#�b����
...

Step 5: Decrypt the Database Backup

To decrypt the database backup, first let’s create a folder of restoration drill to decrypt the data key using the master key and copy all required files, e.g., data encrypted key and master key, into that folder.

$ mkdir restore_backup_drill && cd $_
$ cp ../database_testing_dummy.sql.enc .
$ cp ../data_20240731.key.enc .
$ cp ../master_20240731.key .

now, let’s try to decrypt the database_testing_dummy.sql.enc by two steps

first, decrypt the data encrypted key “data_20240731.key.enc” using the master key “master_20240731.key” to get the “data_20240731.key”, by executing the command as below:

$ openssl enc -d -aes-256-cbc -pbkdf2 -in data_20240731.key.enc -out data_20240731.key -pass file:./master_20240731.key
$ ls -ltrh data_20240731.key

Once we have the data key, we will use it to decrypt the database and execute the command below:

$ openssl enc -d -aes-256-cbc -pbkdf2 -in database_testing_dummy.sql.enc -out database_testing_dummy.sql -pass file:./data_20240731.key
$ ls -ltrh database_testing_dummy.sql

This command decrypts “database_testing_dummy.sql.enc” into “database_testing_dummy.sql.enc”.

Summary

Encrypting your database backups is one vital layer of security, ensuring that your data remains protected even outside of your primary environment or security parameters. Using OpenSSL is a convenient and powerful way to handle encryption and decryption tasks.

What we’re using as a key?

• Master Key: This key is used to encrypt the data key.
• Data Key: This key is used to encrypt the file backup database.

What Needs to Be Rotated?

• Data Key: This is the key that is most frequently rotated. By rotating the data key, we effectively change the encryption key for all backup files without the need to decrypt and re-encrypt everything.
• Master Key: Master key rotation is also important, but not as frequent as data key rotation. The master key is used to protect the data keys, so if the master key is compromised, all your data keys are at risk.

How to Rotate Without Re-encrypting All Files?

• Avoid Decryption: There’s no need to decrypt all backup files when rotating the data key.
• Generate a New Data Key: Each time you want to rotate, create a new data key using the master key.
• Encrypt New Files: For new backups, use the newly generated data key. Older backup files remain secure with their existing data key.

Why Not Use the Master Key Directly to Encrypt Backup Files?

• Enhanced security: By introducing an intermediate layer (the data key), the overall security of the system is bolstered.
• Increased flexibility: The ability to rotate data keys frequently without affecting the master key simplifies the key management process.

Reference

1. OpenSSL Documentation
2. Linux Man Pages