Mercurial > libervia-pubsub

comparison sat_pubsub/pgsql_storage.py @ 330:82d1259b3e36

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
backend, pgsql storage: better items/notification handling, various fixes: - replaced const.VAL_AMODEL_ROSTER by const.VAL_AMODEL_PUBLISHER_ROSTER to follow change in pgsql schema - implemented whitelist access model - fixed bad access check during items retrieval (access was checked on recipient instead of requestor/sender) - getItemsData and notification filtering now use inline callbacks: this make these complexe workflows far mor easy to read, and clarity is imperative in these security critical sections. - publisher-roster access model now need to have only one owner, else it will fail. The idea is to use this model only when owner=publisher, else there is ambiguity on the roster to use to check access - replaced getNodeOwner by node.getOwners, as a node can have several owners - notifications filtering has been fixed in a similar way - psql: simplified withPEP method, pep_table argument is actually not needed - removed error.NotInRoster: error.Forbidden is used instead - notifications now notify all the owners, not only the first one
author Goffi <goffi@goffi.org>
date Sun, 26 Mar 2017 20:52:32 +0200
parents e73e42b4f6ff
children e93a9fd329d9
comparison
equal deleted inserted replaced
329:98409ef42c94 330:82d1259b3e36
53 53
54 import copy, logging 54 import copy, logging
55 55
56 from zope.interface import implements 56 from zope.interface import implements
57 57
58 from twisted.internet import defer
59 from twisted.words.protocols.jabber import jid 58 from twisted.words.protocols.jabber import jid
60 from twisted.python import log 59 from twisted.python import log
61 60
62 from wokkel import generic 61 from wokkel import generic
63 from wokkel.pubsub import Subscription 62 from wokkel.pubsub import Subscription
72 psycopg2.extensions.register_type(psycopg2.extensions.UNICODE) 71 psycopg2.extensions.register_type(psycopg2.extensions.UNICODE)
73 psycopg2.extensions.register_type(psycopg2.extensions.UNICODEARRAY) 72 psycopg2.extensions.register_type(psycopg2.extensions.UNICODEARRAY)
74 73
75 # parseXml manage str, but we get unicode 74 # parseXml manage str, but we get unicode
76 parseXml = lambda unicode_data: generic.parseXml(unicode_data.encode('utf-8')) 75 parseXml = lambda unicode_data: generic.parseXml(unicode_data.encode('utf-8'))
77 76 PEP_COL_NAME = 'pep'
78 77
79 def withPEP(query, values, pep, recipient, pep_table=None): 78
79 def withPEP(query, values, pep, recipient):
80 """Helper method to facilitate PEP management 80 """Helper method to facilitate PEP management
81 81
82 @param query: SQL query basis 82 @param query: SQL query basis
83 @param values: current values to replace in query 83 @param values: current values to replace in query
84 @param pep: True if we are in PEP mode 84 @param pep: True if we are in PEP mode
85 @param recipient: jid of the recipient 85 @param recipient: jid of the recipient
86 @param pep_table: added before pep if table need to be specified
87 @return: query + PEP AND check, 86 @return: query + PEP AND check,
88 recipient's bare jid is added to value if needed 87 recipient's bare jid is added to value if needed
89 """ 88 """
90 pep_col_name = "{}pep".format(
91 '' if pep_table is None
92 else ".{}".format(pep_table))
93 if pep: 89 if pep:
94 pep_check="AND {}=%s".format(pep_col_name) 90 pep_check="AND {}=%s".format(PEP_COL_NAME)
95 values=list(values) + [recipient.userhost()] 91 values=list(values) + [recipient.userhost()]
96 else: 92 else:
97 pep_check="AND {} IS NULL".format(pep_col_name) 93 pep_check="AND {} IS NULL".format(PEP_COL_NAME)
98 return "{} {}".format(query, pep_check), values 94 return "{} {}".format(query, pep_check), values
99 95
100 96
101 class Storage: 97 class Storage:
102 98
259 SELECT %s, entity_id, 'owner' FROM 255 SELECT %s, entity_id, 'owner' FROM
260 (SELECT entity_id FROM entities 256 (SELECT entity_id FROM entities
261 WHERE jid=%s) as e""", 257 WHERE jid=%s) as e""",
262 (node_id, owner)) 258 (node_id, owner))
263 259
264 #TODO: manage JID access 260 if config[const.OPT_ACCESS_MODEL] == const.VAL_AMODEL_PUBLISHER_ROSTER:
265 if config[const.OPT_ACCESS_MODEL] == const.VAL_AMODEL_ROSTER:
266 if const.OPT_ROSTER_GROUPS_ALLOWED in config: 261 if const.OPT_ROSTER_GROUPS_ALLOWED in config:
267 allowed_groups = config[const.OPT_ROSTER_GROUPS_ALLOWED] 262 allowed_groups = config[const.OPT_ROSTER_GROUPS_ALLOWED]
268 else: 263 else:
269 allowed_groups = [] 264 allowed_groups = []
270 for group in allowed_groups: 265 for group in allowed_groups:
271 #TODO: check that group are actually in roster 266 #TODO: check that group are actually in roster
272 cursor.execute("""INSERT INTO node_groups_authorized (node_id, groupname) 267 cursor.execute("""INSERT INTO node_groups_authorized (node_id, groupname)
273 VALUES (%s,%s)""" , (node_id, group)) 268 VALUES (%s,%s)""" , (node_id, group))
269 # XXX: affiliations can't be set on during node creation (at least not with XEP-0060 alone)
270 # so whitelist affiliations need to be done afterward
274 271
275 def deleteNodeByDbId(self, db_id): 272 def deleteNodeByDbId(self, db_id):
276 """Delete a node using directly its database id""" 273 """Delete a node using directly its database id"""
277 return self.dbpool.runInteraction(self._deleteNodeByDbId, db_id) 274 return self.dbpool.runInteraction(self._deleteNodeByDbId, db_id)
278 275
292 (nodeIdentifier,), pep, recipient)) 289 (nodeIdentifier,), pep, recipient))
293 290
294 if cursor.rowcount != 1: 291 if cursor.rowcount != 1:
295 raise error.NodeNotFound() 292 raise error.NodeNotFound()
296 293
297 def getNodeGroups(self, nodeIdentifier, pep, recipient=None): 294
298 return self.dbpool.runInteraction(self._getNodeGroups, nodeIdentifier, pep, recipient)
299
300 def _getNodeGroups(self, cursor, nodeIdentifier, pep, recipient):
301 cursor.execute(*withPEP("SELECT groupname FROM node_groups_authorized NATURAL JOIN nodes WHERE node=%s",
302 (nodeIdentifier,), pep, recipient))
303 rows = cursor.fetchall()
304
305 return [row[0] for row in rows]
306 295
307 def getAffiliations(self, entity, pep, recipient=None): 296 def getAffiliations(self, entity, pep, recipient=None):
308 d = self.dbpool.runQuery(*withPEP("""SELECT node, affiliation FROM entities 297 d = self.dbpool.runQuery(*withPEP("""SELECT node, affiliation FROM entities
309 NATURAL JOIN affiliations 298 NATURAL JOIN affiliations
310 NATURAL JOIN nodes 299 NATURAL JOIN nodes
345 334
346 def __init__(self, nodeDbId, nodeIdentifier, config): 335 def __init__(self, nodeDbId, nodeIdentifier, config):
347 self.nodeDbId = nodeDbId 336 self.nodeDbId = nodeDbId
348 self.nodeIdentifier = nodeIdentifier 337 self.nodeIdentifier = nodeIdentifier
349 self._config = config 338 self._config = config
350 self.owner = None;
351 339
352 340
353 def _checkNodeExists(self, cursor): 341 def _checkNodeExists(self, cursor):
354 cursor.execute("""SELECT 1 as exist FROM nodes WHERE node_id=%s""", 342 cursor.execute("""SELECT 1 as exist FROM nodes WHERE node_id=%s""",
355 (self.nodeDbId,)) 343 (self.nodeDbId,))
358 346
359 347
360 def getType(self): 348 def getType(self):
361 return self.nodeType 349 return self.nodeType
362 350
363 def getNodeOwner(self): 351 def getOwners(self):
364 if self.owner: 352 d = self.dbpool.runQuery("""SELECT jid FROM nodes NATURAL JOIN affiliations NATURAL JOIN entities WHERE node_id=%s and affiliation='owner'""", (self.nodeDbId,))
365 return defer.succeed(self.owner) 353 d.addCallback(lambda rows: [jid.JID(r[0]) for r in rows])
366 d = self.dbpool.runQuery("""SELECT jid FROM nodes NATURAL JOIN affiliations NATURAL JOIN entities WHERE node_id=%s""", (self.nodeDbId,))
367 d.addCallback(lambda result: jid.JID(result[0][0]))
368 return d 354 return d
369 355
370 356
371 def getConfiguration(self): 357 def getConfiguration(self):
372 return self._config 358 return self._config
588 NATURAL JOIN entities 574 NATURAL JOIN entities
589 WHERE node_id=%s""", 575 WHERE node_id=%s""",
590 (self.nodeDbId,)) 576 (self.nodeDbId,))
591 result = cursor.fetchall() 577 result = cursor.fetchall()
592 578
593 return [(jid.internJID(r[0]), r[1]) for r in result] 579 return {jid.internJID(r[0]): r[1] for r in result}
594 580
595 581
596 582
597 class LeafNode(Node): 583 class LeafNode(Node):
598 584
638 self.nodeDbId)) 624 self.nodeDbId))
639 625
640 item_id = cursor.fetchone()[0]; 626 item_id = cursor.fetchone()[0];
641 self._storeCategories(cursor, item_id, item_data.categories) 627 self._storeCategories(cursor, item_id, item_data.categories)
642 628
643 if access_model == const.VAL_AMODEL_ROSTER: 629 if access_model == const.VAL_AMODEL_PUBLISHER_ROSTER:
644 if const.OPT_ROSTER_GROUPS_ALLOWED in item_config: 630 if const.OPT_ROSTER_GROUPS_ALLOWED in item_config:
645 item_config.fields[const.OPT_ROSTER_GROUPS_ALLOWED].fieldType='list-multi' #XXX: needed to force list if there is only one value 631 item_config.fields[const.OPT_ROSTER_GROUPS_ALLOWED].fieldType='list-multi' #XXX: needed to force list if there is only one value
646 allowed_groups = item_config[const.OPT_ROSTER_GROUPS_ALLOWED] 632 allowed_groups = item_config[const.OPT_ROSTER_GROUPS_ALLOWED]
647 else: 633 else:
648 allowed_groups = [] 634 allowed_groups = []
649 for group in allowed_groups: 635 for group in allowed_groups:
650 #TODO: check that group are actually in roster 636 #TODO: check that group are actually in roster
651 cursor.execute("""INSERT INTO item_groups_authorized (item_id, groupname) 637 cursor.execute("""INSERT INTO item_groups_authorized (item_id, groupname)
652 VALUES (%s,%s)""" , (item_id, group)) 638 VALUES (%s,%s)""" , (item_id, group))
639 # TODO: whitelist access model
653 640
654 def _storeCategories(self, cursor, item_id, categories, update=False): 641 def _storeCategories(self, cursor, item_id, categories, update=False):
655 # TODO: handle canonical form 642 # TODO: handle canonical form
656 if update: 643 if update:
657 cursor.execute("""DELETE FROM item_categories 644 cursor.execute("""DELETE FROM item_categories
807 item = generic.stripNamespace(parseXml(data[0])) 794 item = generic.stripNamespace(parseXml(data[0]))
808 access_model = data[1] 795 access_model = data[1]
809 item_id = data[2] 796 item_id = data[2]
810 date = data[3] 797 date = data[3]
811 access_list = {} 798 access_list = {}
812 if access_model == const.VAL_AMODEL_ROSTER: #TODO: jid access_model 799 if access_model == const.VAL_AMODEL_PUBLISHER_ROSTER:
813 cursor.execute('SELECT groupname FROM item_groups_authorized WHERE item_id=%s', (item_id,)) 800 cursor.execute('SELECT groupname FROM item_groups_authorized WHERE item_id=%s', (item_id,))
814 access_list[const.OPT_ROSTER_GROUPS_ALLOWED] = [r[0] for r in cursor.fetchall()] 801 access_list[const.OPT_ROSTER_GROUPS_ALLOWED] = [r[0] for r in cursor.fetchall()]
815 802
816 ret.append(container.ItemData(item, access_model, access_list, date=date)) 803 ret.append(container.ItemData(item, access_model, access_list, date=date))
804 # TODO: whitelist item access model
817 return ret 805 return ret
818 806
819 items_data = [container.ItemData(generic.stripNamespace(parseXml(r[0])), r[1], r[2], date=r[3]) for r in result] 807 items_data = [container.ItemData(generic.stripNamespace(parseXml(r[0])), r[1], r[2], date=r[3]) for r in result]
820 return items_data 808 return items_data
821 809
822 def getItemsById(self, authorized_groups, unrestricted, itemIdentifiers): 810 def getItemsById(self, authorized_groups, unrestricted, itemIdentifiers):
823 """ Get items which are in the given list 811 """Get items which are in the given list
812
824 @param authorized_groups: we want to get items that these groups can access 813 @param authorized_groups: we want to get items that these groups can access
825 @param unrestricted: if true, don't check permissions 814 @param unrestricted: if true, don't check permissions
826 @param itemIdentifiers: list of ids of the items we want to get 815 @param itemIdentifiers: list of ids of the items we want to get
827 @return: list of container.ItemData 816 @return: list of container.ItemData
828 ItemData.config will contains access_list (managed as a dictionnary with same key as for item_config) 817 ItemData.config will contains access_list (managed as a dictionnary with same key as for item_config)
847 item = generic.stripNamespace(parseXml(result[0])) 836 item = generic.stripNamespace(parseXml(result[0]))
848 access_model = result[1] 837 access_model = result[1]
849 item_id = result[2] 838 item_id = result[2]
850 date= result[3] 839 date= result[3]
851 access_list = {} 840 access_list = {}
852 if access_model == const.VAL_AMODEL_ROSTER: #TODO: jid access_model 841 if access_model == const.VAL_AMODEL_PUBLISHER_ROSTER:
853 cursor.execute('SELECT groupname FROM item_groups_authorized WHERE item_id=%s', (item_id,)) 842 cursor.execute('SELECT groupname FROM item_groups_authorized WHERE item_id=%s', (item_id,))
854 access_list[const.OPT_ROSTER_GROUPS_ALLOWED] = [r[0] for r in cursor.fetchall()] 843 access_list[const.OPT_ROSTER_GROUPS_ALLOWED] = [r[0] for r in cursor.fetchall()]
844 #TODO: WHITELIST access_model
855 845
856 ret.append(container.ItemData(item, access_model, access_list, date=date)) 846 ret.append(container.ItemData(item, access_model, access_list, date=date))
857 else: #we check permission before returning items 847 else: #we check permission before returning items
858 for itemIdentifier in itemIdentifiers: 848 for itemIdentifier in itemIdentifiers:
859 args = [self.nodeDbId, itemIdentifier] 849 args = [self.nodeDbId, itemIdentifier]