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 SQL_EXTENSION_ENABLED:
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 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 lint(self):
lint_ok = True
if not self.checkConfig():
print('Error checking config')
lint_ok = False
if lint_ok:
domainlist = self.config.get(self.section,'domainlist')
if domainlist.strip() == '':
print('Enforcing TLS for all domains')
elif domainlist.startswith('txt:'):
domainfile = domainlist[4:]
if not os.path.exists(domainfile):
print('Cannot find domain file %s' % domainfile)
lint_ok = False
elif domainlist.startswith('sql:'):
sqlquery = domainlist[4:]
if not sqlquery.lower().startswith('select '):
lint_ok = False
print('SQL statement must be a SELECT query')
if not SQL_EXTENSION_ENABLED:
print('SQLAlchemy not available, cannot use sql backend')
if lint_ok:
dbconnection = self.config.get(self.section, 'dbconnection')
try:
conn=get_session(dbconnection)
conn.execute(sqlquery, {'domain':'example.com'})
except Exception as e:
lint_ok = False
print(str(e))
else:
lint_ok = False
print('Could not determine domain list backend type')
return lint_ok
def lint(self):
lint_ok = True
if not self.checkConfig():
print('Error checking config')
lint_ok = False
if lint_ok:
domainlist = self.config.get(self.section,'domainlist')
if domainlist.strip() == '':
print('Enforcing TLS for all domains')
elif domainlist.startswith('txt:'):
domainfile = domainlist[4:]
if not os.path.exists(domainfile):
print('Cannot find domain file %s' % domainfile)
lint_ok = False
elif domainlist.startswith('sql:'):
sqlquery = domainlist[4:]
if not sqlquery.lower().startswith('select '):
lint_ok = False
print('SQL statement must be a SELECT query')
if not SQL_EXTENSION_ENABLED:
print('SQLAlchemy not available, cannot use sql backend')
if lint_ok:
dbconnection = self.config.get(self.section, 'dbconnection')
try:
conn=get_session(dbconnection)
conn.execute(sqlquery, {'domain':'example.com'})
except Exception as e:
lint_ok = False
print(str(e))
else:
lint_ok = False
print('Could not determine domain list backend type')
return lint_ok
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
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 lint(self):
lint_ok = True
if not HAVE_SPF:
print(
'pyspf or pydns module not installed - this plugin will do nothing'
)
lint_ok = False
if not HAVE_NETADDR:
print(
'WARNING: netaddr python module not installed - IP whitelist will not support CIDR notation'
)
if not self.checkConfig():
print('Error checking config')
lint_ok = False
selective_sender_domain_file = self.config.get(
self.section, 'domain_selective_spf_file', '').strip()
if selective_sender_domain_file != '' and not os.path.exists(
selective_sender_domain_file):
print("domain_selective_spf_file %s does not exist" %
selective_sender_domain_file)
lint_ok = False
ip_whitelist_file = self.config.get(self.section, 'ip_whitelist_file',
'').strip()
if ip_whitelist_file != '' and os.path.exists(ip_whitelist_file):
print(
"ip_whitelist_file %s does not exist - IP whitelist is disabled"
% ip_whitelist_file)
lint_ok = False
sqlquery = self.config.get(self.section, 'domain_sql_query')
dbconnection = self.config.get(self.section, 'dbconnection',
'').strip()
if not SQL_EXTENSION_ENABLED and dbconnection != '':
print('SQLAlchemy not available, cannot use SQL backend')
lint_ok = False
elif dbconnection == '':
print('No DB connection defined. Disabling SQL backend')
else:
if not sqlquery.lower().startswith('select '):
lint_ok = False
print('SQL statement must be a SELECT query')
if lint_ok:
try:
conn = get_session(dbconnection)
conn.execute(sqlquery, {'domain': 'example.com'})
except Exception as e:
lint_ok = False
print(str(e))
return lint_ok
def lint(self):
lint_ok = True
if not HAVE_SPF:
print('pyspf or pydns module not installed - this plugin will do nothing')
lint_ok = False
if not HAVE_NETADDR:
print('WARNING: netaddr python module not installed - IP whitelist will not support CIDR notation')
if not self.checkConfig():
print('Error checking config')
lint_ok = False
selective_sender_domain_file=self.config.get(self.section,'domain_selective_spf_file','').strip()
if selective_sender_domain_file != '' and not os.path.exists(selective_sender_domain_file):
print("domain_selective_spf_file %s does not exist" % selective_sender_domain_file)
lint_ok = False
ip_whitelist_file=self.config.get(self.section,'ip_whitelist_file', '').strip()
if ip_whitelist_file != '' and os.path.exists(ip_whitelist_file):
print("ip_whitelist_file %s does not exist - IP whitelist is disabled" % ip_whitelist_file)
lint_ok = False
sqlquery = self.config.get(self.section, 'domain_sql_query')
dbconnection = self.config.get(self.section, 'dbconnection', '').strip()
if not SQL_EXTENSION_ENABLED and dbconnection != '':
print('SQLAlchemy not available, cannot use SQL backend')
lint_ok = False
elif dbconnection == '':
print('No DB connection defined. Disabling SQL backend')
else:
if not sqlquery.lower().startswith('select '):
lint_ok = False
print('SQL statement must be a SELECT query')
if lint_ok:
try:
conn=get_session(dbconnection)
conn.execute(sqlquery, {'domain':'example.com'})
except Exception as e:
lint_ok = False
print(str(e))
return lint_ok
def examine(self, suspect):
session = get_session(self.backendconfig)
if self.queries is None:
filename = self.queryfile
if not os.path.exists(filename):
self.logger.error('Limiter config file %s not found', filename)
return
with open(filename) as filehandle:
queryconfig = filehandle.read()
self.queries = self.load_queries(queryconfig)
for query in self.queries:
query.column_fmap = self.get_fieldmap(query.columns)
query.filter_fmap = self.get_fieldmap(query.filters)
query.statement = self.build_query(
columns=query.column_fmap.keys(),
filters=[
str(column) + ' = :' + str(tag) for column, tag in query.filter_fmap.items()
],
from_table=query.table
)
self.logger.info('Found %d query configurations', len(self.queries))
for query in self.queries:
# We GET attributes ("real" data of suspect), but SET tags (additional data)
filter_data = defaultdict()
for _, attribute in query.filter_fmap.items():
filter_data[attribute] = self.get_suspect_attribute(suspect, attribute)
db_result = session.execute(
query.statement,
filter_data
).fetchone()
self.add_tags(suspect, query, db_result)
return DUNNO
def _real_init(self, backendconfig):
self.session = get_session(backendconfig)
metadata.create_all(bind=self.session.bind)
def __init__(self, backendconfig):
super(SQLAlchemyBackend, self).__init__(backendconfig)
self.session = get_session(backendconfig)
metadata.create_all(bind=self.session.bind)
