Encrypting the backup

mdsupport wrote on Tuesday, May 03, 2011:

Since OpenEMR backup consists of a MySQL dump of all tables, we want to encrypt the data contained in the backup.
Does anyone have settings for this?
We think it will be good idea to make encryption a standard feature for the backup script.

aethelwulffe wrote on Tuesday, May 03, 2011:

Use PGP or Truecrypt.  Mount a Truecrypt volume, and run a normal backup using that volume as the drive.  Dismount the volume, and poof…it is gone.  You could run the emr (or whole operating system) inside a truecrypt file container.  If someone shuts down the machine, the data is totally unreadable until someone with the password and/or keyfile (and who knows that the data is even on the drive) mounts the drive.

aethelwulffe wrote on Tuesday, May 03, 2011:

Use the following script with OpenSSL:  http://www.openssl.org/

     #!/bin/bash
        ##################################################################
        # sqlbackup.sh:
        #   rolling sqlbackups using mysqldump
        #
        #
        #   This script will dump mysql databases to a specified directory
        #   with optional encrytion.
        #
        #
        ###################################################################
    
        # program options
        backup_dir=/backup
        backup_age=28
        use_encryption=0
        use_bzip=0
        nice_level=19
        mysql_user=root
        mysql_passfile=/etc/keys/.mysqlpass
        openssl_passfile=/etc/keys/.opensslpass
        date=`date +%Y%m%d`
        exten=".sql"
        logfile=/var/log/mysqlbackup.log
        mysqldump_options=" --all-databases --single-transaction --quote-names "
    
        # program paths
        mysqldump=/usr/bin/mysqldump
        openssl=/usr/bin/openssl
        bzip2=/usr/bin/bzip2
    
    
        # usage summary
        function usage {
        echo "
        sqlbackup.sh:
            creates backups of sql databases using the mysqldump command,
            including optional encryption, bzip2 compression, and 
            variable backup aging
    
        Usage: sqlbackup.sh [OPTIONS]
    
            Options:
    
            -h              : display this help
    
            -b <dir>        : select directory to store backups. This directory 
                            will not be created.
    
            -e              : enable encryption using openssl's des3 encryption. 
    
            -k <file>       : specify file containing password to be used for encryption
                            This defaults to /etc/keys/.opensslpass
                    
            -u <user>       : username to use for connecting to MySQL
    
            -p </user></file><file>       : specifies a file containing a password to be used for
                            connecting to MySQL
        
            -z              : enable bzip2 compression of backups
    
            -a <days>       : backup age in days. Backups older than this will be deleted
    
            -n <level>      : Specifies a nice level. This level will apply to all operations of the 
                            backup. Defaults to 19 (very nice)
                    
            -l </level></days></file><file>       : logfile path. Alternate path to a file for logging. Defaults
                            to '/var/log/mysqlbackup.log'
                    
        "
        }      
    
        # echo a string to screen and log
        function echo_and_log {
            logstring="[ `date` ] : $*"
            echo $logstring
            echo $logstring >> $logfile
        }
    
    
        # print error to screen and exit
        function exit_with_error {
            echo_and_log "Error: $*"
            echo_and_log "Exiting with code 1"
            exit 1
        }
    
        # funtion to delete old backups
        function clean_backups {
        find $backup_dir -type f -mtime +${backup_age} -exec rm -f {} \;
        [ "$?" == "0" ] || echo_and_log "Error encountered when cleaning old backups from $backup_dir"
        }
    
    
        # process command line options
        while getopts ":hb:ek:u:p:za:n:l:" opt 
        do
            case $opt in
                b) backup_dir=$OPTARG ;;
                e) use_encryption=1 ;;
                k) openssl_passfile=$OPTARG ;;
                u) mysql_user=$OPTARG ;;
                p) mysql_passfile=$OPTARG ;;
                z) use_bzip=1 ;;
                a) backup_age=$OPTARG ;;
                n) nice_level=$OPTARG ;;
                l) logfile=$OPTARG ;;
                h) usage 
                    exit 0 ;;
                \?) echo "unknown option"
                    usage 
                    exit 1 ;;
                *)  usage
                    exit 1 ;;
            esac
        done
    
        # set umask
        umask 0077
    
        # start processing
        echo_and_log "Starting MySQL backup: encryption = $use_encryption, bzip = $use_bzip, backup directory = $backup_dir"
        
        # error checking and validation
        [ -d $backup_dir ] || exit_with_error "Backup directory '$backup_dir' does not exist"
        [ -n "$logfile" ] || exit_with_error "No log file specified."
        [ -f $mysql_passfile ] || exit_with_error "MySQL password file '$mysql_passfile' does not exist"
        [ -x $mysqldump ] || exit_with_error "MySQLDump program at '$mysqldump' does not exist or is not executable"
        # validate compression options
        if [ "$use_bzip" == "1" ]
        then
            [ -x $bzip2 ] || exit_with_error "Bzip2 program at '$bzip2' does not exist or is not executable"
            bzip2_command=" | nice -n $nice_level $bzip2 "
            exten="${exten}.bz2"
        fi
    
        # validate encryption options
        if [ "$use_encryption" == "1" ]
        then 
            [ -x $openssl ] || exit_with_error "OpenSSL program at '$openssl' does not exist or is not executable"
            [ -f $openssl_passfile ] || exit_with_error "OpenSSL password file '$openssl_passfile' does not exist"
            openssl_command=" | nice -n $nice_level $openssl des3 -salt -pass file:${openssl_passfile} "
            exten="${exten}.cryp"
        fi
    
        # call funtion to clean backup directory
        clean_backups
    
        # execute dump command
        command="$mysqldump $mysqldump_options --user=${mysql_user} --password=$(<$mysql_passfile) $bzip2_command $openssl_command > $backup_dir/mysqlbackup_${date}${exten}"
        eval $command
        [ "$?" == "0" ] || exit_with_error "MySQLDump command returned non-zero error code. Backup not completed successfully"
    
        # finish
        echo_and_log "Backup complete"
        exit 0
    
</file></dir>

mdsupport wrote on Tuesday, May 03, 2011:

Thanks a lot.  With all the privacy issues, this is the least every installation should do.

aethelwulffe wrote on Tuesday, May 03, 2011:

Yeah, let’s not get all SONY about this.
  Personally, I am not even operating on https.  I have never used a security certificate on a site before.

ajperezcrespo wrote on Thursday, September 08, 2011:

Maybe adding something like this to the backup.php ?

# Encrypt
tar -cf - /var/www/openemr | openssl aes-128-cbc -salt -out openemr.tar.aes     

# Decrypt
openssl aes-128-cbc -d -salt -in openemr.tar.aes | tar -x -f - 

Just a thought

jason0 wrote on Tuesday, September 20, 2011:

Hello,

The trouble with encrypting the backups as it comes out of the server is that no OS really uses the same encryption.  Thus, if the server uses openssl to encrypt the backup, but the end user has a windows system, it won’t matter unless that user knows how to use openssl.  I don’t know how to use openssl, and there is a lot of conflicting information regarding how to encrypt/decrypt data.  Thus, there is an excellent chance that the end user will have a file of useless data: the worst kind of backup failure.

If https is being used, then the actual data transfer encrypts the data on the fly, and is decrypted at the customer’s system, so you don’t need to worry about any packet sniffing attempts.  If you are using ssl only within a private network or vpn, you shouldn’t even need to purchase an ssl certificate: the certificate is only used to identify that the server is legitimate and not used to encrypt.  If you are not using https for your web connections, I recommend it strongly, and there are ways to keep it cheap.  I am considering writing a document on securing apache.

It seems to me the more important issue here is what to do with your data backups once you have them.  You need to guard your data not only from theft, but also from disasters such as fire, earthquake, or even a benign user deleting files that don’t appear to have a purpose.  You absolutely must store that data somewhere where it will be safe, preferably in more than one location away from where you use it.  If you encrypt it, you must keep the key somewhere safe, in both electronic and hard-copy: none of the backups will matter if you lose the key.  For example: if you follow truecrypt’s instructions and create a passphrase that truly is strong, no one will be able to decrypt it for you.

Other than being a (I like to think mindful) rant, I hope this helps!

-jason

jason0 wrote on Tuesday, September 20, 2011:

Oh, I really like aethelwulffe’s comments about truecrypt.  I have considered using truecrypt on an amazon S3 mounted volume.  I don’t know how hipaa compliant it would be, but by the time the data lands on amazon’s disks, it’s form is useless to anyone without the passphrase key.

-jason