def _get_listings(self): usecache = self.config.getboolean(self.section,'usecache') listings = None if usecache: listings = self.memcache.get('listings') if not listings: listings = {} try: session = get_session(self.config.get(self.section,'dbconnection')) listing_types = [unicode(l['name']) for l in LISTING_TYPES] result = session.query(UserPref).filter(UserPref.preference.in_(listing_types)).all() for r in result: listing_type = r.preference if not listing_type in listings: listings[listing_type] = {} username = r.username if username.startswith('*@'): # roundcube sauserprefs plugin domain wide scope username = username.lstrip('*@') if not username in listings[listing_type]: listings[listing_type][username] = [] listings[listing_type][username].append(r.value) except Exception as e: self.logger.error('Failed to get listings: %s' % str(e)) if listings and usecache: self.memcache.put('listings', listings) return listings
def lint(self): status = True if not SQLALCHEMY_AVAILABLE: print "sqlalchemy is not installed" status = False try: session = get_session(self.config.get(self.section,'dbconnection')) try: session.query(UserPref).first() except Exception as e: print "Table or field configuration error: %s"%str(e) status = False except Exception as e: print "DB Connection failed. Reason: %s"%(str(e)) status = False if status: listings = self._get_listings() count = 0 for listingtype in listings: for user in listings[listingtype]: count += len(listings[listingtype][user]) print "found %s listings" % count for check in LISTING_TYPES: if self._get_action(check['name']) is None: print 'Invalid action %s for action_%s' % (self.config.get(self.section,'action_%s' % check['name']), check['name']) status = False return status
def lint(self): if not SQLALCHEMY_AVAILABLE: print "sqlalchemy is not installed" return False #check fieldmap, select all fields (if we can't select, we can't insert) if not self.checkConfig(): return False tablename=self.config.get(self.section,'table') fieldmap=self.get_fieldmap() requiredcolumnnames=fieldmap.keys() dbcolumns=",".join(requiredcolumnnames) try: conn=get_session(self.config.get(self.section,'dbconnection')) except Exception as e: print "DB Connection failed. Reason: %s"%(str(e)) return False sql_query="SELECT %s FROM %s LIMIT 0,1"%(dbcolumns,tablename) try: conn.execute(sql_query) except Exception as e: print "Table or field configuration error: %s"%str(e) return False return True
def unblacklist(self,relayordomain): """remove a server from the blacklist/history""" conn=get_session(self.config.get('AddressCheck','dbconnection')) statement="""DELETE FROM ca_blacklist WHERE domain=:removeme or relay=:removeme""" values={'removeme':relayordomain} res=conn.execute(statement,values) return res.rowcount
def get_relays(self,domain,domainconfig=None): """Determine the relay(s) for a domain""" serverconfig=self.get_domain_config(domain, 'server', domainconfig,{'domain':domain}) (tp,val)=serverconfig.split(':',1) if tp=='sql': conn=get_session(self.config.get('AddressCheck','dbconnection')) ret=conn.execute(val) arr= [result[0] for result in ret] conn.remove() return arr elif tp=='mx': return mxlookup(val) elif tp=='static': return [val,] elif tp=='txt': try: content=open(val).read() lines=content.split('\n') for line in lines: fdomain,ftarget=line.split() if domain.lower()==fdomain.lower(): return [ftarget,] except Exception as e: self.logger.error("Txt lookup failed: %s"%str(e)) else: self.logger.error('unknown relay lookup type: %s'%tp) return None
def get_domain_config_all(self,domain): retval=dict() conn=get_session(self.config.get('AddressCheck','dbconnection')) res=conn.execute("SELECT confkey,confvalue FROM ca_configoverride WHERE domain=:domain",{'domain':domain}) for row in res: retval[row[0]]=row[1] return retval
def get_total_counts(self): conn=get_session(self.config.get('AddressCheck','dbconnection')) statement="SELECT count(*) FROM ca_addresscache WHERE expiry_ts>now() and positive=1" result=conn.execute(statement) poscount=result.fetchone()[0] statement="SELECT count(*) FROM ca_addresscache WHERE expiry_ts>now() and positive=0" result=conn.execute(statement) negcount=result.fetchone()[0] return (poscount,negcount)
def get_all_addresses(self,domain): conn=get_session(self.config.get('AddressCheck','dbconnection')) if not conn: return None statement="SELECT email,positive FROM ca_addresscache WHERE domain=:domain and expiry_ts>now() ORDER BY email" values={'domain':domain} result=conn.execute(statement,values) return result
def wipe_address(self,address): conn=get_session(self.config.get('AddressCheck','dbconnection')) if not conn: return statement="""DELETE FROM ca_addresscache WHERE email=:email""" values={'email':address} res=conn.execute(statement,values) return res.rowcount
def get_blacklist(self): """return all blacklisted servers""" conn=get_session(self.config.get('AddressCheck','dbconnection')) if not conn: return None statement="SELECT domain,relay,reason,expiry_ts FROM ca_blacklist WHERE expiry_ts>now() ORDER BY domain" values={} result=conn.execute(statement,values) return result
def is_blacklisted(self,domain,relay): """Returns True if the server/relay combination is currently blacklisted and should not be used for recipient verification""" conn=get_session(self.config.get('AddressCheck','dbconnection')) if not conn: return False statement="SELECT reason FROM ca_blacklist WHERE domain=:domain and relay=:relay and expiry_ts>now()" values={'domain':domain,'relay':relay} ret=conn.execute(statement,values) return ret.scalar()
def examine(self,suspect): try: tablename=self.config.get(self.section,'table') sender=suspect.get_value('sender') if sender is not None: from_address=strip_address(sender) from_domain=extract_domain(from_address) else: from_address=None from_domain=None recipient=suspect.get_value('recipient') if recipient is not None: to_address=strip_address(recipient) to_domain=extract_domain(to_address) else: to_address=None to_domain=None fields=suspect.values.copy() fields['from_address']=from_address fields['from_domain']=from_domain fields['to_address']=to_address fields['to_domain']=to_domain fields['timestamp']=suspect.timestamp #build query fieldmap=self.get_fieldmap() requiredcolumnnames=fieldmap.keys() dbcolumns=",".join(requiredcolumnnames) placeholders=",".join(map(lambda x:u':'+x, requiredcolumnnames)) sql_insert="INSERT INTO %s (%s) VALUES (%s)"%(tablename,dbcolumns,placeholders) # #fill the required vars into new dict with the db columns data={} for col in requiredcolumnnames: postfixfieldname=fieldmap[col] if postfixfieldname in fields: #a fiew fields are numeric.. convert them if postfixfieldname in ['recipient_count','size','encryption_keysize']: data[col]=int(fields[postfixfieldname]) else: data[col]=fields[postfixfieldname] else: data[col]=None #print sql_insert #print data conn=get_session(self.config.get(self.section,'dbconnection')) conn.execute(sql_insert,data) except Exception as e: self.logger.error("DB Writer plugin failed, Log not written. : %s"%str(e)) return DUNNO,None
def get_address(self,address): """Returns a tuple (positive(boolean),message) if a cache entry exists, None otherwise""" conn=get_session(self.config.get('AddressCheck','dbconnection')) if not conn: return statement="SELECT positive,message FROM ca_addresscache WHERE email=:email and expiry_ts>now()" values={'email':address} res=conn.execute(statement,values) return res.first()
def get_domain_config_value(self,domain,key): sc=None try: conn=get_session(self.config.get('AddressCheck','dbconnection')) res=conn.execute("SELECT confvalue FROM ca_configoverride WHERE domain=:domain and confkey=:confkey",{'domain':domain,'confkey':key}) sc=res.scalar() conn.remove() except Exception as e: self.logger.error('Could not connect to database') return sc
def get_domain_config_all(self,domain): retval=dict() try: conn=get_session(self.config.get('AddressCheck','dbconnection')) res=conn.execute("SELECT confkey,confvalue FROM ca_configoverride WHERE domain=:domain",{'domain':domain}) for row in res: retval[row[0]]=row[1] conn.remove() except Exception as e: self.logger.error('Could not connect to database') return retval
def blacklist(self,domain,relay,seconds,failstage='rcpt_to',reason='unknown'): """Put a domain/relay combination on the recipient verification blacklist for a certain amount of time""" conn=get_session(self.config.get('AddressCheck','dbconnection')) statement="""INSERT INTO ca_blacklist (domain,relay,expiry_ts,check_stage,reason) VALUES (:domain,:relay,now()+interval :interval second,:checkstag,:reason) ON DUPLICATE KEY UPDATE expiry_ts=now()+interval :interval second,check_stage=:checkstage,reason=:reason """ values={ 'domain':domain, 'relay':relay, 'interval':seconds, 'checkstage':failstage, 'reason':reason, } res=conn.execute(statement,values)
def cleanup(self): conn=get_session(self.config.get('AddressCheck','dbconnection')) postime=self.config.getint('AddressCheck','keep_positive_history_time') negtime=self.config.getint('AddressCheck','keep_negative_history_time') statement="""DELETE FROM ca_addresscache WHERE positive=:pos and expiry_ts<(now() -interval :keeptime day)""" res=conn.execute(statement,dict(pos=0,keeptime=negtime)) negcount=res.rowcount res=conn.execute(statement,dict(pos=1,keeptime=postime)) poscount=res.rowcount res=conn.execute("""DELETE FROM ca_blacklist where expiry_ts<now()""") blcount=res.rowcount conn.remove() return (poscount,negcount,blcount)
def put_address(self,address,seconds,positiveEntry=True,message=None): """put address into the cache""" conn=get_session(self.config.get('AddressCheck','dbconnection')) if not conn: return statement="""INSERT INTO ca_addresscache (email,domain,expiry_ts,positive,message) VALUES (:email,:domain,now()+interval :interval second,:positive,:message) ON DUPLICATE KEY UPDATE check_ts=now(),expiry_ts=now()+interval :interval second,positive=:positive,message=:message """ #todo strip domain domain=extract_domain(address) values={'email':address, 'domain':domain, 'interval':seconds, 'positive':positiveEntry, 'message':message, } conn.execute(statement,values)
def lint(self): if not SQLALCHEMY_AVAILABLE: print "sqlalchemy is not installed" return False #check fieldmap, select all fields (if we can't select, we can't insert) if not self.checkConfig(): return False tablename=self.config.get(self.section,'table') fieldmap=self.get_fieldmap() requiredcolumnnames=fieldmap.keys() dbcolumns=",".join(requiredcolumnnames) try: conn=get_session(self.config.get(self.section,'dbconnection')) except Exception,e: print "DB Connection failed. Reason: %s"%(str(e)) return False
def wipe_domain(self,domain,positive=None): """wipe all cache info for a domain. if positive is None(default), all cache entries are deleted. if positive is False all negative cache entries are deleted if positive is True, all positive cache entries are deleted """ conn=get_session(self.config.get('AddressCheck','dbconnection')) if not conn: return posstatement="" if positive==True: posstatement="and positive=1" if positive==False: posstatement="and positive=0" statement="""DELETE FROM ca_addresscache WHERE domain=:domain %s"""%posstatement values={'domain':domain} res=conn.execute(statement,values) return res.rowcount
def get_domain_config_value(self,domain,key): retval=None conn=get_session(self.config.get('AddressCheck','dbconnection')) res=conn.execute("SELECT confvalue FROM ca_configoverride WHERE domain=:domain and confkey=:confkey",{'domain':domain,'confkey':key}) return res.scalar()