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()
