libervia-backend: src/memory/sqlite.py comparison

comparison src/memory/sqlite.py @ 1030:15f43b54d697

core, memory, bridge: added profile password + password encryption: /!\ This changeset updates the database version to 2 and modify the database content! Description: - new parameter General / Password to store the profile password - profile password is initialized with XMPP password value, it is stored hashed - bridge methods asyncCreateProfile/asyncConnect takes a new argument "password" (default = "") - bridge method asyncConnect returns a boolean (True = connection already established, False = connection initiated) - profile password is checked before initializing the XMPP connection - new private individual parameter to store the personal encryption key of each profile - personal key is randomly generated and encrypted with the profile password - personal key is decrypted after profile authentification and stored in a Sessions instance - personal key is used to encrypt/decrypt other passwords when they need to be retrieved/modified - modifying the profile password re-encrypt the personal key - Memory.setParam now returns a Deferred (the bridge method "setParam" is unchanged) - Memory.asyncGetParamA eventually decrypts the password, Memory.getParamA would fail on a password parameter TODO: - if profile authentication is OK but XMPP authentication is KO, prompt the user for another XMPP password - fix the method "registerNewAccount" (and move it to a plugin) - remove bridge method "connect", sole "asyncConnect" should be used

author	souliane <souliane@mailoo.org>
date	Wed, 07 May 2014 16:02:23 +0200
parents	6a16ec17a458
children	65fffdcb97f1

comparison

equal deleted inserted replaced

-:f6182f6418ea
+:15f43b54d697
 # You should have received a copy of the GNU Affero General Public License
 # along with this program.  If not, see <http://www.gnu.org/licenses/>.
 from sat.core.i18n import _
+from sat.core.constants import Const as C
 from sat.core import exceptions
 from sat.core.log import getLogger
 log = getLogger(__name__)
+from sat.memory.crypto import BlockCipher, PasswordHasher
 from twisted.enterprise import adbapi
 from twisted.internet import defer
 from collections import OrderedDict
 from time import time
 import re
 import os.path
 import cPickle as pickle
 import hashlib
-CURRENT_DB_VERSION = 1
+CURRENT_DB_VERSION = 2
 # XXX: DATABASE schemas are used in the following way:
 #      - 'current' key is for the actual database schema, for a new base
 #      - x(int) is for update needed between x-1 and x. All number are needed between y and z to do an update
 #        e.g.: if CURRENT_DB_VERSION is 6, 'current' is the actuel DB, and to update from version 3, numbers 4, 5 and 6 are needed
 #      a 'current' data dict can contains the keys:
 #      - 'CREATE': it contains an Ordered dict with table to create as keys, and a len 2 tuple as value, where value[0] are the columns definitions and value[1] are the table constraints
 #      - 'INSERT': it contains an Ordered dict with table where values have to be inserted, and many tuples containing values to insert in the order of the rows (#TODO: manage named columns)
-#      an update data dict (the ones with a number) can contains the keys 'create', 'delete', 'cols create', 'cols delete', 'cols modify' or 'insert'. See Updater.generateUpdateData for more infos. This metho can be used to autogenerate update_data, to ease the work of the developers.
+#      an update data dict (the ones with a number) can contains the keys 'create', 'delete', 'cols create', 'cols delete', 'cols modify', 'insert' or 'specific'. See Updater.generateUpdateData for more infos. This method can be used to autogenerate update_data, to ease the work of the developers.
 DATABASE_SCHEMAS = {
 "current": {'CREATE': OrderedDict((
 ('profiles',        (("id INTEGER PRIMARY KEY ASC", "name TEXT"),
 ("UNIQUE (name)",))),
 ("PRIMARY KEY (namespace, key)",))),
 ('private_ind',     (("namespace TEXT", "key TEXT", "profile_id INTEGER", "value TEXT"),
 ("PRIMARY KEY (namespace, key, profile_id)", "FOREIGN KEY(profile_id) REFERENCES profiles(id) ON DELETE CASCADE"))),
 ('private_gen_bin', (("namespace TEXT", "key TEXT", "value BLOB"),
 ("PRIMARY KEY (namespace, key)",))),
 ('private_ind_bin', (("namespace TEXT", "key TEXT", "profile_id INTEGER", "value BLOB"),
 ("PRIMARY KEY (namespace, key, profile_id)", "FOREIGN KEY(profile_id) REFERENCES profiles(id) ON DELETE CASCADE")))
 )),
 'INSERT': OrderedDict((
 ('message_types', (("'chat'",), ("'error'",), ("'groupchat'",), ("'headline'",), ("'normal'",))),
 )),
 },
+2:         {'specific': 'update2raw_v2'
+},
 1:         {'cols create': {'history': ('extra BLOB',)}
 },
 }
 d.addCallback(lambda result: [row[0] for row in result])
 return d
 @defer.inlineCallbacks
 def checkUpdates(self):
-""" Check is database schema update is needed, according to DATABASE_SCHEMAS
+""" Check if a database schema/content update is needed, according to DATABASE_SCHEMAS
 @return: deferred which fire a list of SQL update statements, or None if no update is needed
 """
 local_version = yield self.getLocalVersion()
 raw_local_sch = yield self.getLocalSchema()
 local_sch = self.rawStatements2data(raw_local_sch)
 current_sch = DATABASE_SCHEMAS['current']['CREATE']
 local_hash = self.statementHash(local_sch)
 current_hash = self.statementHash(current_sch)
-if local_hash == current_hash:
+# Force the update if the schemas are unchanged but a specific update is needed
+force_update = local_hash == current_hash and local_version < CURRENT_DB_VERSION \
+and 'specific' in DATABASE_SCHEMAS[CURRENT_DB_VERSION]
+if local_hash == current_hash and not force_update:
 if local_version != CURRENT_DB_VERSION:
 log.warning(_("Your local schema is up-to-date, but database versions mismatch, fixing it..."))
 yield self._setLocalVersion(CURRENT_DB_VERSION)
 else:
 # an update is needed
 # Database mismatch and we have the latest version
 if self._sat_version.endswith('D'):
 # we are in a development version
 update_data = self.generateUpdateData(local_sch, current_sch, False)
 log.warning(_("There is a schema mismatch, but as we are on a dev version, database will be updated"))
-update_raw = self.update2raw(update_data, True)
+update_raw = yield self.update2raw(update_data, True)
 defer.returnValue(update_raw)
 else:
 log.error(_(u"schema version is up-to-date, but local schema differ from expected current schema"))
 update_data = self.generateUpdateData(local_sch, current_sch, True)
-log.warning(_(u"Here are the commands that should fix the situation, use at your own risk (do a backup before modifying database), you can go to SàT's MUC room at sat@chat.jabberfr.org for help\n### SQL###\n%s\n### END SQL ###\n") % u'\n'.join(("%s;" % statement for statement in self.update2raw(update_data))))
+update_raw = yield self.update2raw(update_data)
+log.warning(_(u"Here are the commands that should fix the situation, use at your own risk (do a backup before modifying database), you can go to SàT's MUC room at sat@chat.jabberfr.org for help\n### SQL###\n%s\n### END SQL ###\n") % u'\n'.join("%s;" % statement for statement in update_raw))
 raise exceptions.DatabaseError("Database mismatch")
 else:
 # Database is not up-to-date, we'll do the update
-log.info(_("Database schema has changed, local database will be updated"))
+if force_update:
+log.info(_("Database content needs a specific processing, local database will be updated"))
+else:
+log.info(_("Database schema has changed, local database will be updated"))
 update_raw = []
 for version in xrange(local_version + 1, CURRENT_DB_VERSION + 1):
 try:
 update_data = DATABASE_SCHEMAS[version]
 except KeyError:
 raise exceptions.InternalError("Missing update definition (version %d)" % version)
-update_raw.extend(self.update2raw(update_data))
+update_raw_step = yield self.update2raw(update_data)
+update_raw.extend(update_raw_step)
 update_raw.append("PRAGMA user_version=%d" % CURRENT_DB_VERSION)
 defer.returnValue(update_raw)
 @staticmethod
 def createData2Raw(data):
 'cols create': create_cols_data,
 'cols delete': delete_cols_data,
 'cols modify': modify_cols_data
 }
+@defer.inlineCallbacks
 def update2raw(self, update, dev_version=False):
 """ Transform update data to raw SQLite statements
 @param upadte: update data as returned by generateUpdateData
 @param dev_version: if True, update will be done in dev mode: no deletion will be done, instead a message will be shown. This prevent accidental lost of data while working on the code/database.
 @return: list of string with SQL statements needed to update the base
 ret.append(self.DROP_SQL % self.TMP_TABLE)
 insert = update.get('insert', {})
 ret.extend(self.insertData2Raw(insert))
-return ret
+specific = update.get('specific', None)
+if specific:
+cmds = yield getattr(self, specific)()
+ret.extend(cmds)
+defer.returnValue(ret)
+def update2raw_v2(self):
+"""Update the database from v1 to v2 (add passwords encryptions):
+- the XMPP password value is re-used for the profile password (new parameter)
+- the profile password is stored hashed
+- the XMPP password is stored encrypted, with the profile password as key
+- as there are no other stored passwords yet, it is enough, otherwise we
+would need to encrypt the other passwords as it's done for XMPP password
+"""
+xmpp_pass_path = ('Connection', 'Password')
+def encrypt_values(values):
+ret = []
+list_ = []
+for profile_id, xmpp_password in values:
+def prepare_queries(result):
+try:
+id_ = result[0][0]
+except IndexError:
+log.error("Profile of id %d is referenced in 'param_ind' but it doesn't exist!" % profile_id)
+return
+sat_password = xmpp_password
+d1 = PasswordHasher.hash(sat_password)
+personal_key = BlockCipher.getRandomKey(base64=True)
+d2 = BlockCipher.encrypt(sat_password, personal_key)
+d3 = BlockCipher.encrypt(personal_key, xmpp_password)
+def gotValues(res):
+sat_cipher, personal_cipher, xmpp_cipher = res[0][1], res[1][1], res[2][1]
+ret.append("INSERT INTO param_ind(category,name,profile_id,value) VALUES ('%s','%s',%s,'%s')" %
+(C.PROFILE_PASS_PATH[0], C.PROFILE_PASS_PATH[1], id_, sat_cipher))
+ret.append("INSERT INTO private_ind(namespace,key,profile_id,value) VALUES ('%s','%s',%s,'%s')" %
+(C.MEMORY_CRYPTO_NAMESPACE, C.MEMORY_CRYPTO_KEY, id_, personal_cipher))
+ret.append("REPLACE INTO param_ind(category,name,profile_id,value) VALUES ('%s','%s',%s,'%s')" %
+(xmpp_pass_path[0], xmpp_pass_path[1], id_, xmpp_cipher))
+return defer.DeferredList([d1, d2, d3]).addCallback(gotValues)
+d = self.dbpool.runQuery("SELECT id FROM profiles WHERE id=?", (profile_id,))
+d.addCallback(prepare_queries)
+list_.append(d)
+d_list = defer.DeferredList(list_)
+d_list.addCallback(lambda dummy: ret)
+return d_list
+d = self.dbpool.runQuery("SELECT profile_id,value FROM param_ind WHERE category=? AND name=?", xmpp_pass_path)
+d.addCallback(encrypt_values)
+return d

Mercurial > libervia-backend

comparison src/memory/sqlite.py @ 1030:15f43b54d697