24. Encryption at rest

Miraj can encrypt everything it writes to disk: tables, long values, the journal, vector indexes, and the definitions of views, triggers, routines and events. A stolen disk, a copy of the data folder or a stolen backup then yields nothing readable without the folder's passphrase.

Encryption is:

• reserved for the Enterprise and Cluster editions: the Express and Developer editions do not include it, and refuse to open an encrypted folder (error 9001 or 9048);
• decided when the data folder is initialized: it is enabled on a new folder and cannot be added to a folder that already contains databases;
• adjustable per database and per table once the folder is encrypted (ENCRYPTION = 'Y' | 'N').

24.1 What is protected, and what is not#

Encryption at rest protects against:

• the theft or loss of a disk, of a stopped machine whose passphrase is not kept on site, or of a backup medium;
• the copying of the data folder or of a backup by someone who does not have the passphrase;
• a host or service provider that accesses the disks without accessing the running server.

It does not protect against:

• an attacker with administrator rights on the machine while the server is running: the data is in clear in its memory, as with any database server;
• the theft of the entire machine when the passphrase is kept on that machine for automatic startup (24.3): the passphrase file is then readable along with the machine;
• exports that you request yourself: SELECT … INTO OUTFILE, miraj-dump scripts, results sent to clients (protect the network with TLS, chapter 11).

24.2 Initializing an encrypted folder#

Encryption is requested at first startup on an empty folder, with a passphrase of at least 12 characters:

$env:MIRAJ_ENCRYPTION_PASSPHRASE = "a long phrase that only you know"
miraj-server.exe --root D:\donnees --log --data-encryption ON

or, with the server stopped, with the miraj-keyring tool (24.7), which asks for the passphrase twice without displaying it:

miraj-keyring init --root D:\donnees

The folder then receives its keyring miraj\keyring.mrk. From then on, the folder is encrypted for good: at each startup, the server requires the passphrase, whether or not data_encryption is set.

--data-encryption ON on a folder that already contains databases is refused: encryption cannot be added to existing data. To encrypt an existing installation, create a new encrypted folder and restore your databases into it with miraj-dump (chapter 18).

Keep the passphrase in a safe place. Without it, neither the data folder nor its backups can be read again: there is no way to recover it.

24.3 Providing the passphrase at startup#

The server reads the passphrase, in this order:

1. the MIRAJ_ENCRYPTION_PASSPHRASE environment variable;
2. the file designated by --encryption-passphrase-file (or the encryption_passphrase_file variable of miraj_config.xml).

The passphrase is never given on the command line or in clear text in miraj_config.xml.

Automatic startup (Windows). The scheduled task that starts the server at boot cannot ask for anything at the keyboard. Write the passphrase file protected by DPAPI for this machine, then point the server to it:

miraj-keyring write-passphrase-file --root D:\donnees --to C:\ProgramData\MIRAJ\phrase.mrp
icacls C:\ProgramData\MIRAJ\phrase.mrp /inheritance:r /grant:r "*S-1-5-18:F" "*S-1-5-32-544:F"

and in miraj_config.xml:

<encryption_passphrase_file>C:\ProgramData\MIRAJ\phrase.mrp</encryption_passphrase_file>

The file is readable only on this machine (the task's SYSTEM account can read it back); the icacls command restricts it to SYSTEM and administrators. Reminder (24.1): this mode protects stolen disks and backups, not the entire machine.

Linux and macOS. The passphrase file contains the passphrase as text; it must be readable only by its owner (0600 permissions), otherwise the server refuses to start.

24.4 Choosing what is encrypted#

On an encrypted folder, by default, everything is encrypted (default_table_encryption = ON). The ENCRYPTION clause lets you exclude or include a database or a table:

-- Database whose new tables and definitions are in clear
CREATE DATABASE catalogue_public DEFAULT ENCRYPTION = 'N';

-- Table that is encrypted in this database all the same
CREATE TABLE catalogue_public.tarifs_negocies (id INT PRIMARY KEY, prix DECIMAL(10,2)) ENCRYPTION = 'Y';

-- Change afterwards: the table is rewritten in the new mode
ALTER TABLE catalogue_public.tarifs_negocies ENCRYPTION = 'N';

-- Database: default setting for tables created afterwards, and encryption of its definitions
ALTER DATABASE catalogue_public DEFAULT ENCRYPTION = 'Y';

ALTER DATABASE … ENCRYPTION does not change existing tables: it sets the setting for tables created afterwards and rewrites the definition files. ALTER TABLE … ENCRYPTION rewrites the table and its long values.

Database and table names remain visible (they are folder and file names).

Display. SHOW CREATE TABLE shows ENCRYPTION='Y' for an encrypted table; SHOW CREATE DATABASE shows DEFAULT ENCRYPTION='Y'; the information_schema.TABLES.CREATE_OPTIONS and information_schema.SCHEMATA.DEFAULT_ENCRYPTION columns also report it. The @@data_encryption and @@default_table_encryption variables (read-only) give the server's state.

On a non-encrypted server, ENCRYPTION = 'N' is accepted with no effect and ENCRYPTION = 'Y' is refused (error 9049).

24.5 Changing the passphrase, rotating the key#

Changing the passphrase (server stopped): only the keyring is rewritten, the data does not move.

miraj-keyring change-passphrase --root D:\donnees --write-passphrase-file C:\ProgramData\MIRAJ\phrase.mrp

Backups made before keep the old passphrase (their keyring is the one from the time of the backup, 24.6).

Rotating the master key (server running, ENCRYPTION_KEY_ADMIN privilege):

ALTER INSTANCE ROTATE MASTER KEY;

A new master key replaces the old one; the keys of each database are rewrapped, and the data is not rewritten. The operation survives a hard crash: an interrupted rotation is completed at the next startup.

24.6 Backups#

BACKUP DATABASE and miraj-backup copy the files as they are: the copy of an encrypted database is encrypted. It carries the keyring (keyring.mrk, protected by the passphrase) and its manifest indicates the master key (master_key).

• miraj-backup verify checks an encrypted copy without the passphrase (sizes, checksums, journal, table envelopes); --deep does not decode the content of encrypted tables without a key.
• Restoring requires a server that has the same master key: the original server, or a folder initialized from its keyring. An encrypted copy restored onto a non-encrypted server, or one with a different key, remains unavailable (the database is reported as unreadable).

Offline operation (miraj-backup --root, miraj-dump --root) reads the passphrase like the server: the MIRAJ_ENCRYPTION_PASSPHRASE variable, or the encryption_passphrase_file of the folder's miraj_config.xml.

24.7 miraj-keyring#

miraj-keyring is used with the server stopped (it takes the folder's lock).

The current passphrase comes from MIRAJ_ENCRYPTION_PASSPHRASE, from --passphrase-file, otherwise from a masked prompt; a new passphrase comes from --new-passphrase-file or from a repeated prompt.

24.8 Server logs#

On an encrypted folder, server.log and the slow query log (slow.log) mask values: query literals become ? and the values quoted by error messages become '?'. Table and column names remain readable.

24.9 Embedded library#

The DLL opens an encrypted folder with miraj_server_open_encrypted(root, options, passphrase, create_encrypted, &server); a non-zero create_encrypted initializes a new folder as encrypted. miraj_server_open is enough if the passphrase is in MIRAJ_ENCRYPTION_PASSPHRASE.

24.10 How it works#

For administrators who want to know what is done:

• the passphrase is derived with Argon2id (64 MiB, 3 passes); the resulting key wraps the folder's master key (keyring.mrk), which in turn wraps a data key per database (base.mro, in the database's folder);
• everything is encrypted with AES-256-GCM, which authenticates what it encrypts: a modified byte, or a block or page that has been moved, is rejected on read;
• each file keeps its checksums, computed on the encrypted data: CHECK TABLE and backup verification detect damage without the key;
• the overhead is low on a recent processor (AES instructions), and passphrase derivation adds a fraction of a second to startup.

24.11 Limits#

• Cluster: encryption cannot yet be combined with a cluster node; the server refuses to start if both are requested.
• Disk table (ENGINE = Aria): ALTER TABLE … ENCRYPTION is refused as long as the table already holds long values (.dbmrj) in the other mode; first switch it to memory (ENGINE = MIRAJ), then back to disk.
• Restoring onto another server: it requires the same master key (24.6).
• Encryption cannot be added to an existing folder (24.2).