Protect Odoo 11 database backup with a password

Database auto-backup backup process crashes with a database size > 200 MB and Odoo worker enabled.

The module “Database auto-backup” will help you to backup Odoo database on your local disk or remotely on a FTP server through ftp our sftp protocol, however it doesn’t give us the possibility to secure a bit more the backup file generated, therefore I added 2 additional options (Enable password and password) to be able to protect by a password the compressed file by using directly Linux 7z command line and recompressing a 2nd time the file, we should use dump file instead of zip to avoid redundant compression. This customization will handle only the backup on local disk part, ftp way should work as well but I didn’t make the test.

First of all, we’ll need to install p7zip-full package on our system Linux, I’m using Ubuntu, here the command bellow for its installation.

Install system package

apt-get install p7zip-full

Update model

I created a new module named auto_backup_2 and inherit “db.backup” model and override schedule_backup method from module auto_backup. “schedule_backup” method code is almost similar from the original one, modification i made are between additional import and additional code comments as bellow.

from odoo import models, fields, api, tools, _
from odoo.exceptions import Warning
import odoo
from odoo.http import content_disposition

import logging
_logger = logging.getLogger(__name__)
from ftplib import FTP
import os
import datetime

try:
    from xmlrpc import client as xmlrpclib
except ImportError:
    import xmlrpclib
import time
import base64
import socket
#####additional import#####
import subprocess 
###########################

try:
    import paramiko
except ImportError:
    raise ImportError(
        'This module needs paramiko to automatically write backups to the FTP through SFTP. Please install paramiko on your system. (sudo pip3 install paramiko)')

class db_backup(models.Model):
    _inherit = 'db.backup'
    
    #####additional code#####
    file_password_enable =  fields.Boolean('Enable file password', help="compress the backup file with a password")
    file_password = fields.Char('File password', help="backup file password")
    #####additional code#####    

    @api.model
    def schedule_backup(self):
        conf_ids = self.search([])

        for rec in conf_ids:
            db_list = self.get_db_list(rec.host, rec.port)

            if rec.name in db_list:
                try:
                    if not os.path.isdir(rec.folder):
                        os.makedirs(rec.folder)
                except:
                    raise
                # Create name for dumpfile.
                bkp_file = '%s_%s.%s' % (time.strftime('%Y_%m_%d_%H_%M_%S'), rec.name, rec.backup_type)
                file_path = os.path.join(rec.folder, bkp_file)
                uri = 'http://' + rec.host + ':' + rec.port
                conn = xmlrpclib.ServerProxy(uri + '/xmlrpc/db')
                bkp = ''
                try:
                    # try to backup database and write it away
                    fp = open(file_path, 'wb')
                    odoo.service.db.dump_db(rec.name, fp, rec.backup_type)
                    fp.close()
                    #####additional code#####
                    if rec.file_password_enable:
                        _logger.debug(file_path)
                        new_file = file_path + '.7z'
                        re = subprocess.call(['7z', 'a', '-p' + rec.file_password, '-y', new_file] + [file_path])
                        _logger.debug(re)
                        if os.path.exists(new_file) and re == 0:
                            os.remove(file_path)
                    ###########################
                except Exception as error:
                    _logger.debug(
                        "Couldn't backup database %s. Bad database administrator password for server running at http://%s:%s" % (
                        rec.name, rec.host, rec.port))
                    _logger.debug("Exact error from the exception: " + str(error))
                    continue

            else:
                _logger.debug("database %s doesn't exist on http://%s:%s" % (rec.name, rec.host, rec.port))

            # Check if user wants to write to SFTP or not.
            if rec.sftp_write is True:
                try:
                    # Store all values in variables
                    dir = rec.folder
                    pathToWriteTo = rec.sftp_path
                    ipHost = rec.sftp_host
                    portHost = rec.sftp_port
                    usernameLogin = rec.sftp_user
                    passwordLogin = rec.sftp_password
                    _logger.debug('sftp remote path: %s' % pathToWriteTo)

                    try:
                        s = paramiko.SSHClient()
                        s.set_missing_host_key_policy(paramiko.AutoAddPolicy())
                        s.connect(ipHost, portHost, usernameLogin, passwordLogin, timeout=20)
                        sftp = s.open_sftp()
                    except Exception as error:
                        _logger.critical('Error connecting to remote server! Error: ' + str(error))

                    try:
                        sftp.chdir(pathToWriteTo)
                    except IOError:
                        # Create directory and subdirs if they do not exist.
                        currentDir = ''
                        for dirElement in pathToWriteTo.split('/'):
                            currentDir += dirElement + '/'
                            try:
                                sftp.chdir(currentDir)
                            except:
                                _logger.info('(Part of the) path didn\'t exist. Creating it now at ' + currentDir)
                                # Make directory and then navigate into it
                                sftp.mkdir(currentDir, 777)
                                sftp.chdir(currentDir)
                                pass
                    sftp.chdir(pathToWriteTo)
                    # Loop over all files in the directory.
                    for f in os.listdir(dir):
                        if rec.name in f:
                            fullpath = os.path.join(dir, f)
                            if os.path.isfile(fullpath):
                                try:
                                    sftp.stat(os.path.join(pathToWriteTo, f))
                                    _logger.debug(
                                        'File %s already exists on the remote FTP Server ------ skipped' % fullpath)
                                # This means the file does not exist (remote) yet!
                                except IOError:
                                    try:
                                        # sftp.put(fullpath, pathToWriteTo)
                                        sftp.put(fullpath, os.path.join(pathToWriteTo, f))
                                        _logger.info('Copying File % s------ success' % fullpath)
                                    except Exception as err:
                                        _logger.critical(
                                            'We couldn\'t write the file to the remote server. Error: ' + str(err))

                    # Navigate in to the correct folder.
                    sftp.chdir(pathToWriteTo)

                    # Loop over all files in the directory from the back-ups.
                    # We will check the creation date of every back-up.
                    for file in sftp.listdir(pathToWriteTo):
                        if rec.name in file:
                            # Get the full path
                            fullpath = os.path.join(pathToWriteTo, file)
                            # Get the timestamp from the file on the external server
                            timestamp = sftp.stat(fullpath).st_atime
                            createtime = datetime.datetime.fromtimestamp(timestamp)
                            now = datetime.datetime.now()
                            delta = now - createtime
                            # If the file is older than the days_to_keep_sftp (the days to keep that the user filled in on the Odoo form it will be removed.
                            if delta.days >= rec.days_to_keep_sftp:
                                # Only delete files, no directories!
                                if sftp.isfile(fullpath) and (".dump" in file or '.zip' in file):
                                    _logger.info("Delete too old file from SFTP servers: " + file)
                                    sftp.unlink(file)
                    # Close the SFTP session.
                    sftp.close()
                except Exception as e:
                    _logger.debug('Exception! We couldn\'t back up to the FTP server..')
                    # At this point the SFTP backup failed. We will now check if the user wants
                    # an e-mail notification about this.
                    if rec.send_mail_sftp_fail:
                        try:
                            ir_mail_server = self.env['ir.mail_server']
                            message = "Dear,\n\nThe backup for the server " + rec.host + " (IP: " + rec.sftp_host + ") failed.Please check the following details:\n\nIP address SFTP server: " + rec.sftp_host + "\nUsername: " + rec.sftp_user + "\nPassword: " + rec.sftp_password + "\n\nError details: " + tools.ustr(
                                e) + "\n\nWith kind regards"
                            msg = ir_mail_server.build_email("auto_backup@" + rec.name + ".com", [rec.email_to_notify],
                                                             "Backup from " + rec.host + "(" + rec.sftp_host + ") failed",
                                                             message)
                            ir_mail_server.send_email(self._cr, self._uid, msg)
                        except Exception:
                            pass

            """
            Remove all old files (on local server) in case this is configured..
            """
            if rec.autoremove:
                dir = rec.folder
                # Loop over all files in the directory.
                for f in os.listdir(dir):
                    fullpath = os.path.join(dir, f)
                    # Only delete the ones wich are from the current database 
                    # (Makes it possible to save different databases in the same folder)
                    if rec.name in fullpath:
                        timestamp = os.stat(fullpath).st_ctime
                        createtime = datetime.datetime.fromtimestamp(timestamp)
                        now = datetime.datetime.now()
                        delta = now - createtime
                        if delta.days >= rec.days_to_keep:
                            # Only delete files (which are .dump and .zip), no directories.
                            if os.path.isfile(fullpath) and (".dump" in f or '.zip' in f):
                                _logger.info("Delete local out-of-date file: " + fullpath)
                                os.remove(fullpath)


Update view

The xml code bellow will display our 2 additional fields: “file_password_enable” and “file_password”

<odoo>
    <data>
        <record id="view_backup_config_form" model="ir.ui.view">
            <field name="name">db.backup.form</field>
            <field name="model">db.backup</field>
            <field name="type">form</field>
            <field name="inherit_id" ref="auto_backup.view_backup_config_form" />
            <field name="arch" type="xml">
            	<data>
            		<xpath expr="//field[@name='days_to_keep']" position="after">
            			<field name="file_password_enable" />
                                <field name="file_password" />
            		</xpath>
            	</data>
            </field>
        </record>
    </data>
</odoo>

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.