Encrypt database backups

[maikschneider]

The database dumps created by deployer ({{deploy_path}}/.dep/database/dumps) are not encrypted and therefore a potentially security risk.

It would be nice to extend the db:compress/ db:decompress commands for a password option of the gzip command.

This feature could be used as a PR for deployer-extended-database

[martipoe]

Standard zip encryption with PKZIP is unsafe.

Firstly, a distinction must be made between symmetric or asymmetric encryption.

• Symmetric encryption is faster, but the encryption key is needed during encryption and decryption.
• Asymmetric encryption is slower, but the private key is not needed for encryption (only the public key).

Considering that db:compress and db:decompress are both executed on the deployment target, asymmetric encryption is not totally useful here. Besides, database dumps can be large and encryption should be fast.

Looking at symmetric encryption, AES-256 might be the best choice for two reasons:

• hardware acceleration by most processors (AES-NI) (https://crypto.stackexchange.com/questions/102086/fastest-cipher-for-use-with-openssl-and-x86-intel)
• no known weaknesses
• AES-128 is not as future-proof

When using encryption, there is more to consider:

• Key length
• Key derivation function, hashing algorithm, salts, iterations: https://cheatsheetseries.owasp.org/cheatsheets/Password_Storage_Cheat_Sheet.html
• Implementation. 7zip would generally be a good choice, but unix file permissions are not preserved (man page) and even more, it would be an additional dependency. But openssl nearly always comes out of the box.

The current openssl version on Debian 11 supports these ciphers:

:~$ openssl version
OpenSSL 1.1.1n  15 Mar 2022

:~$ openssl enc -ciphers
Supported ciphers:
-aes-128-cbc               -aes-128-cfb               -aes-128-cfb1             
-aes-128-cfb8              -aes-128-ctr               -aes-128-ecb              
-aes-128-ofb               -aes-192-cbc               -aes-192-cfb              
-aes-192-cfb1              -aes-192-cfb8              -aes-192-ctr              
-aes-192-ecb               -aes-192-ofb               -aes-256-cbc              
-aes-256-cfb               -aes-256-cfb1              -aes-256-cfb8             
-aes-256-ctr               -aes-256-ecb               -aes-256-ofb              
-aes128                    -aes128-wrap               -aes192                   
-aes192-wrap               -aes256                    -aes256-wrap              
-aria-128-cbc              -aria-128-cfb              -aria-128-cfb1            
-aria-128-cfb8             -aria-128-ctr              -aria-128-ecb             
-aria-128-ofb              -aria-192-cbc              -aria-192-cfb             
-aria-192-cfb1             -aria-192-cfb8             -aria-192-ctr             
-aria-192-ecb              -aria-192-ofb              -aria-256-cbc             
-aria-256-cfb              -aria-256-cfb1             -aria-256-cfb8            
-aria-256-ctr              -aria-256-ecb              -aria-256-ofb             
-aria128                   -aria192                   -aria256                  
-bf                        -bf-cbc                    -bf-cfb                   
-bf-ecb                    -bf-ofb                    -blowfish                 
-camellia-128-cbc          -camellia-128-cfb          -camellia-128-cfb1        
-camellia-128-cfb8         -camellia-128-ctr          -camellia-128-ecb         
-camellia-128-ofb          -camellia-192-cbc          -camellia-192-cfb         
-camellia-192-cfb1         -camellia-192-cfb8         -camellia-192-ctr         
-camellia-192-ecb          -camellia-192-ofb          -camellia-256-cbc         
-camellia-256-cfb          -camellia-256-cfb1         -camellia-256-cfb8        
-camellia-256-ctr          -camellia-256-ecb          -camellia-256-ofb         
-camellia128               -camellia192               -camellia256              
-cast                      -cast-cbc                  -cast5-cbc                
-cast5-cfb                 -cast5-ecb                 -cast5-ofb                
-chacha20                  -des                       -des-cbc                  
-des-cfb                   -des-cfb1                  -des-cfb8                 
-des-ecb                   -des-ede                   -des-ede-cbc              
-des-ede-cfb               -des-ede-ecb               -des-ede-ofb              
-des-ede3                  -des-ede3-cbc              -des-ede3-cfb             
-des-ede3-cfb1             -des-ede3-cfb8             -des-ede3-ecb             
-des-ede3-ofb              -des-ofb                   -des3                     
-des3-wrap                 -desx                      -desx-cbc                 
-id-aes128-wrap            -id-aes128-wrap-pad        -id-aes192-wrap           
-id-aes192-wrap-pad        -id-aes256-wrap            -id-aes256-wrap-pad       
-id-smime-alg-CMS3DESwrap  -rc2                       -rc2-128                  
-rc2-40                    -rc2-40-cbc                -rc2-64                   
-rc2-64-cbc                -rc2-cbc                   -rc2-cfb                  
-rc2-ecb                   -rc2-ofb                   -rc4                      
-rc4-40                    -seed                      -seed-cbc                 
-seed-cfb                  -seed-ecb                  -seed-ofb                 
-sm4                       -sm4-cbc                   -sm4-cfb                  
-sm4-ctr                   -sm4-ecb                   -sm4-ofb                  

aes-256-ctr seems to be a safe choice: https://crypto.stackexchange.com/questions/6029/aes-cbc-mode-or-aes-ctr-mode-recommended

Base on the above research, we could use openssl like this:

# Password as environment variable
export key_var=password

# Encrypt
openssl enc -aes-256-ctr -md sha256 -pbkdf2 -iter 600000 -salt -in dump.sql.gz -out dump.sql.gz.crypt -pass env:key_var

# Decrypt
openssl enc -aes-256-ctr -md sha256 -pbkdf2 -iter 600000 -salt -d -in dump.sql.gz.crypt -out dump.sql.gz -pass env:key_var

Speed test on Cloud VM with 2 Cores @ 2GHz

# Random 1GB file:
:~$ head -c 1G </dev/urandom >dump.sql.gz
:~$ ls -lh dump.sql.gz 
-rw-r--r-- 1 user user 1,0G 29. Apr 19:03 dump.sql.gz

# Encryption
:~$ time openssl enc -aes-256-ctr -md sha256 -pbkdf2 -iter 600000 -salt -in dump.sql.gz -out dump.sql.gz.crypt -pass env:key_var

real	 0m3,558s
user 0m1,239s
sys	 0m1,194s

# Decryption
:~$ time openssl enc -aes-256-ctr -md sha256 -pbkdf2 -iter 600000 -salt -d -in dump.sql.gz.crypt -out dump.sql.gz -pass env:key_var

real	 0m2,946s
user 0m1,350s
sys	 0m1,396s

Todos:

• Where is the passphrase stored? As CI Variable? It should not be stored in a file on the deployment target.
• How long should the passphrase be? 80 bits +
• Is it fast enough for large dumps?
• How to pipe from gzip?
• Integrity checks with gzip?