def update_index(sphinx_url, index, has_rt):
"Update Sphinx index"
sql_temp = """SELECT set_var('maxts',
(SELECT maxts FROM indexer_counters
WHERE tablename='messages_delta'));
UPDATE indexer_counters
SET maxts=get_var('maxts')
WHERE tablename='%s';"""
main_index = index
delta_index = '%sdelta' % index
indexer_cmd = ['/usr/bin/indexer', '--rotate', '--merge',
main_index, delta_index]
pipe = subprocess.Popen(indexer_cmd,
stdout=subprocess.PIPE,
stderr=subprocess.PIPE)
_, stderr = pipe.communicate()
pipe.wait()
if pipe.returncode == 0:
sql = text(sql_temp % main_index)
Session.execute(sql, params={})
Session.commit()
delta_index_cmd = ['/usr/bin/indexer', '--rotate', delta_index]
pipe = subprocess.Popen(delta_index_cmd,
stdout=subprocess.PIPE,
stderr=subprocess.PIPE)
_, stderr = pipe.communicate()
pipe.wait()
if has_rt:
update_rt_index(index, sphinx_url)
else:
print >> sys.stderr, stderr
def create_sign_clean():
"Generate the sign clean ruleset"
usersql = """SELECT users.email, '' as address FROM users,
user_signatures WHERE users.active = 't'
AND users.id=user_signatures.user_id
AND user_signatures.enabled='t' AND
user_signatures.signature_type = 1
UNION
SELECT users.email, addresses.address
FROM users,addresses, user_signatures
WHERE users.id=addresses.user_id
AND users.id=user_signatures.user_id AND
addresses.enabled='t' AND
user_signatures.enabled='t' AND
user_signatures.signature_type = 1
"""
domainsql = """SELECT name FROM alldomains,
domain_signatures WHERE alldomains.id =
domain_signatures.domain_id AND
alldomains.status='t' AND
domain_signatures.enabled='t' AND
domain_signatures.signature_type = 1
"""
users = Session.execute(usersql)
domains = Session.execute(domainsql)
kwargs = dict(users=users, domains=domains)
write_ruleset('sign.clean.msgs.rules', kwargs)
Session.close()
def update_index(sphinx_url, index, has_rt):
"Update Sphinx index"
sql_temp = """SELECT set_var('maxts',
(SELECT maxts FROM indexer_counters
WHERE tablename='messages_delta'));
UPDATE indexer_counters
SET maxts=get_var('maxts')
WHERE tablename='%s';"""
main_index = index
delta_index = '%sdelta' % index
indexer_cmd = [
'/usr/bin/indexer', '--rotate', '--merge', main_index, delta_index
]
pipe = subprocess.Popen(indexer_cmd,
stdout=subprocess.PIPE,
stderr=subprocess.PIPE)
_, stderr = pipe.communicate()
pipe.wait()
if pipe.returncode == 0:
sql = text(sql_temp % main_index)
Session.execute(sql, params={})
Session.commit()
delta_index_cmd = ['/usr/bin/indexer', '--rotate', delta_index]
pipe = subprocess.Popen(delta_index_cmd,
stdout=subprocess.PIPE,
stderr=subprocess.PIPE)
_, stderr = pipe.communicate()
pipe.wait()
if has_rt:
update_rt_index(index, sphinx_url)
else:
print >> sys.stderr, stderr
def get_sig_data(sigtype):
"Get signature data"
usersql = """SELECT users.username, users.email, ''
AS address FROM users,
user_signatures WHERE users.active = 't'
AND users.id=user_signatures.user_id
AND user_signatures.enabled='t' AND
user_signatures.signature_type = :sigtype
UNION
SELECT users.username, users.email,
addresses.address FROM users,addresses,
user_signatures WHERE users.id=addresses.user_id
AND users.id=user_signatures.user_id AND
addresses.enabled='t' AND
user_signatures.enabled='t' AND
user_signatures.signature_type = :sigtype
"""
domainsql = """SELECT name FROM alldomains,
domain_signatures WHERE alldomains.id =
domain_signatures.domain_id AND
alldomains.status='t' AND
domain_signatures.enabled='t' AND
domain_signatures.signature_type = :sigtype
"""
users = Session.execute(usersql, params=dict(sigtype=sigtype))
domains = Session.execute(domainsql, params=dict(sigtype=sigtype))
kwargs = dict(users=users, domains=domains)
return kwargs
def create_sign_clean():
"Generate the sign clean ruleset"
usersql = """SELECT users.email, '' as address FROM users,
user_signatures WHERE users.active = 't'
AND users.id=user_signatures.user_id
AND user_signatures.enabled='t' AND
user_signatures.signature_type = 1
UNION
SELECT users.email, addresses.address
FROM users,addresses, user_signatures
WHERE users.id=addresses.user_id
AND users.id=user_signatures.user_id AND
addresses.enabled='t' AND
user_signatures.enabled='t' AND
user_signatures.signature_type = 1
"""
domainsql = """SELECT name FROM alldomains,
domain_signatures WHERE alldomains.id =
domain_signatures.domain_id AND
alldomains.status='t' AND
domain_signatures.enabled='t' AND
domain_signatures.signature_type = 1
"""
users = Session.execute(usersql)
domains = Session.execute(domainsql)
kwargs = dict(users=users, domains=domains)
write_ruleset('sign.clean.msgs.rules', kwargs)
Session.close()
def get_sig_data(sigtype):
"Get signature data"
usersql = """SELECT users.username, users.email, ''
AS address FROM users,
user_signatures WHERE users.active = 't'
AND users.id=user_signatures.user_id
AND user_signatures.enabled='t' AND
user_signatures.signature_type = :sigtype
UNION
SELECT users.username, users.email,
addresses.address FROM users,addresses,
user_signatures WHERE users.id=addresses.user_id
AND users.id=user_signatures.user_id AND
addresses.enabled='t' AND
user_signatures.enabled='t' AND
user_signatures.signature_type = :sigtype
"""
domainsql = """SELECT name FROM alldomains,
domain_signatures WHERE alldomains.id =
domain_signatures.domain_id AND
alldomains.status='t' AND
domain_signatures.enabled='t' AND
domain_signatures.signature_type = :sigtype
"""
users = Session.execute(usersql, params=dict(sigtype=sigtype))
domains = Session.execute(domainsql, params=dict(sigtype=sigtype))
kwargs = dict(users=users, domains=domains)
return kwargs
def setup_app(command, conf, variables):
"""Place any commands to setup baruwa here"""
# Don't reload the app if it was loaded under the testing environment
if not pylons.test.pylonsapp:
load_environment(conf.global_conf, conf.local_conf)
# Create the tables if they don't already exist
print '-' * 100
log.info("Creating tables")
Base.metadata.create_all(bind=Session.bind)
basepath = os.path.dirname(os.path.dirname(__file__))
# Create the custom functions
print '-' * 100
log.info("Creating custom functions")
sqlfile = os.path.join(basepath, 'baruwa', 'config', 'sql',
'functions.sql')
if os.path.exists(sqlfile):
with open(sqlfile, 'r') as handle:
sql = handle.read()
try:
conn = Session.connection()
conn.execute(text(sql))
Session.commit()
except ProgrammingError:
Session.rollback()
defaultserver = Session.query(Server)\
.filter(Server.hostname == 'default')\
.all()
# Create the Mailscanner SQL config views
print '-' * 100
log.info("Populating initial sql")
sqlfile = os.path.join(basepath, 'baruwa', 'config', 'sql',
'integration.sql')
if os.path.exists(sqlfile):
with open(sqlfile, 'r') as handle:
sql = handle.read()
for sqlcmd in sql.split(';'):
if sqlcmd:
try:
sqlcmd = "%s;" % sqlcmd
Session.execute(text(sqlcmd))
Session.commit()
except ProgrammingError:
Session.rollback()
if not defaultserver:
log.info("Creating the default settings node")
dfls = Server('default', True)
Session.add(dfls)
confserial = ConfigSettings('confserialnumber', 'ConfSerialNumber', 0)
confserial.value = 1
confserial.server_id = 1
Session.add(confserial)
Session.commit()
log.info("Default settings node created !")
def create_post_smtp_av():
"""Create post smtp av domains"""
sql = """SELECT name AS key, '1' AS value FROM
alldomains WHERE virus_checks_at_smtp='f'"""
domains = Session.execute(sql)
generate_cdb_file(domains, 'postsmtpav.cdb')
Session.close()
def create_callback_domains():
"""Generate SMTP callback domains"""
sql = """SELECT name AS key, name AS value FROM
mtasettings WHERE smtp_callout='t'"""
domains = Session.execute(sql)
generate_cdb_file(domains, 'cbdomains.cdb')
Session.close()
def create_ldap_domains():
"""Generate LDAP domains"""
sql = """SELECT name AS key, name AS value FROM
mtasettings WHERE ldap_callout='t'"""
domains = Session.execute(sql)
generate_cdb_file(domains, 'ldapdomains.cdb')
Session.close()
def create_av_disabled():
"""Create AV checks disabled domains"""
sql = """SELECT name AS key, '1' AS value FROM
alldomains WHERE virus_checks='f'"""
domains = Session.execute(sql)
generate_cdb_file(domains, 'avdisabled.cdb')
Session.close()
def create_relay_hosts():
"""Generate relay hosts cdb"""
sql = """SELECT address AS key, address AS value
FROM relaysettings WHERE enabled='t' AND
address NOT LIKE '%/%'"""
addrs = Session.execute(sql)
generate_cdb_file(addrs, 'relayhosts.cdb')
sql = """SELECT address || ':' AS key FROM relaysettings
WHERE enabled='t' AND address LIKE '%/%'"""
netaddrs = Session.execute(sql)
generate_text_file(netaddrs, 'relaynets')
sql = """SELECT 'trusted_networks ' || address AS key
FROM relaysettings WHERE enabled='t' AND
address != ''"""
trusted_nets = Session.execute(sql)
generate_text_file(trusted_nets, 'baruwa-custom.cf.local')
Session.close()
def create_auth_data():
"""Create auth data"""
sql = """SELECT username AS key, password AS value
FROM relaysettings WHERE username !='' AND
password !=''"""
recs = Session.execute(sql)
generate_cdb_file(recs, 'auth.cdb')
Session.close()
def create_route_data():
"""Generate route data"""
sql = """SELECT name AS key, '"<+ ' ||
array_to_string(array_agg(address), ' + ' ) || '"' AS value
FROM routedata WHERE enabled='t' GROUP BY name"""
addrs = Session.execute(sql)
generate_cdb_file(addrs, 'routedata.cdb')
Session.close()
def create_ldap_data():
"""Generate ldap data"""
sql = """SELECT ldapmaps.name AS key, url
AS value FROM ldaplookup, ldapmaps
WHERE ldaplookup.name=ldapmaps.parent"""
items = Session.execute(sql)
generate_cdb_file(items, 'ldapdata.cdb')
Session.close()
def create_ratelimit():
"""Create Ratelimit"""
sql = """SELECT CASE WHEN address != '' THEN
address ELSE username END AS key,
CAST(ratelimit AS text) AS value FROM
relaysettings WHERE enabled='t'
"""
items = Session.execute(sql)
generate_cdb_file(items, 'ratelimit.cdb')
Session.close()
def create_mta_settings(setting_type):
"""Create MTA settings"""
sql = """SELECT address AS key, address AS value FROM
mta_settings WHERE address_type=%d AND enabled='t'
""" % int(setting_type)
items = Session.execute(sql)
if int(setting_type) == 2:
generate_text_file(items, SETTINGS_MAP[int(setting_type)])
else:
generate_cdb_file(items, SETTINGS_MAP[int(setting_type)])
Session.close()
def create_domain_lists(list_type):
"""Approved list"""
if list_type == 1:
sql = """SELECT from_address AS key, from_address AS value
FROM lists WHERE to_address='any' AND list_type=1"""
filename = 'approvedlists.cdb'
else:
sql = """SELECT from_address AS key, from_address AS value
FROM lists WHERE to_address='any' AND list_type=2"""
filename = 'bannedlists.cdb'
addrs = Session.execute(sql)
generate_cdb_file(addrs, filename)
Session.close()
def create_relay_proto_domains(protocol):
"""Generate relay domains cdb"""
if protocol == 1:
sql = """SELECT name AS key, name AS value FROM mtasettings
WHERE protocol=1"""
filename = 'relaysmtpdomains.cdb'
else:
sql = """SELECT name AS key, name AS value FROM mtasettings
WHERE protocol=2"""
filename = 'relaylmtpdomains.cdb'
domains = Session.execute(sql)
generate_cdb_file(domains, filename)
Session.close()
def get_sig_img_data():
"Get signatures data"
usersql = """SELECT users.username, users.email, ''
AS address, user_sigimgs.name AS img FROM
users, user_sigimgs WHERE users.id =
user_sigimgs.user_id AND users.active='t'
UNION
SELECT users.username, users.email,
addresses.address, user_sigimgs.name AS img
FROM users, addresses, user_sigimgs
WHERE users.id = addresses.user_id AND
users.id = user_sigimgs.user_id AND
addresses.enabled='t' AND users.active='t'
"""
domainsql = """SELECT alldomains.name, dom_sigimgs.name
AS img FROM alldomains, dom_sigimgs WHERE
alldomains.id = dom_sigimgs.domain_id
AND alldomains.status='t'
"""
users = Session.execute(usersql)
domains = Session.execute(domainsql)
kwargs = dict(users=users, domains=domains)
return kwargs
def get_sig_img_data():
"Get signatures data"
usersql = """SELECT users.username, users.email, ''
AS address, user_sigimgs.name AS img FROM
users, user_sigimgs WHERE users.id =
user_sigimgs.user_id AND users.active='t'
UNION
SELECT users.username, users.email,
addresses.address, user_sigimgs.name AS img
FROM users, addresses, user_sigimgs
WHERE users.id = addresses.user_id AND
users.id = user_sigimgs.user_id AND
addresses.enabled='t' AND users.active='t'
"""
domainsql = """SELECT alldomains.name, dom_sigimgs.name
AS img FROM alldomains, dom_sigimgs WHERE
alldomains.id = dom_sigimgs.domain_id
AND alldomains.status='t'
"""
users = Session.execute(usersql)
domains = Session.execute(domainsql)
kwargs = dict(users=users, domains=domains)
return kwargs
class DBCleanCommand(BaseCommand):
"DB clean command"
BaseCommand.parser.add_option(
'-d',
'--days',
help='Archive, the delete records older than days',
type='int',
default=30)
summary = 'archive, then delete old records'
# usage = 'NAME '
group_name = 'baruwa'
def command(self):
"command"
self.init()
interval = datetime.timedelta(days=self.options.days)
last_date = now() - interval
params = dict(date=last_date)
sql1 = text("""INSERT INTO archive
SELECT * FROM messages WHERE timestamp <
:date;""")
try:
Session.execute(sql1, params=params)
except IntegrityError, error:
Session.rollback()
sql = text("""DELETE FROM archive WHERE id in
(SELECT id FROM messages WHERE timestamp < :date);""")
Session.execute(sql, params=params)
Session.execute(sql1, params=params)
print >> sys.stderr, "Integrety error occured: %s" % str(error)
sys.exit(2)
sql = text("""DELETE FROM messages WHERE timestamp < :date;""")
Session.execute(sql, params=params)
sql = text("""DELETE FROM releases WHERE timestamp < :date;""")
Session.execute(sql, params=params)
Session.commit()
def prune_archive(last_date):
"prune the messages archive"
params = dict(date=last_date)
sql = text("""DELETE FROM archive WHERE timestamp < :date;""")
result = Session.execute(sql, params=params)
Session.commit()
if result.rowcount > 0 and os.path.exists('/usr/bin/indexer'):
index_cmd = ['/usr/bin/indexer', '--rotate', 'archive']
pipe = subprocess.Popen(index_cmd,
stdout=subprocess.PIPE,
stderr=subprocess.PIPE)
_, stderr = pipe.communicate()
pipe.wait()
if stderr:
print >> sys.stderr, stderr
def prune_archive(last_date):
"prune the messages archive"
params = dict(date=last_date)
sql = text("""DELETE FROM archive WHERE timestamp < :date;""")
result = Session.execute(sql, params=params)
Session.commit()
if result.rowcount > 0 and os.path.exists('/usr/bin/indexer'):
index_cmd = ['/usr/bin/indexer', '--rotate', 'archive']
pipe = subprocess.Popen(index_cmd,
stdout=subprocess.PIPE,
stderr=subprocess.PIPE)
_, stderr = pipe.communicate()
pipe.wait()
if stderr:
print >> sys.stderr, stderr
def create_lmtp(delivery_mode):
"""Create LMTP domains"""
if delivery_mode == 1:
sql = """SELECT name AS key, name AS value FROM
mtasettings WHERE delivery_mode=1 AND
protocol=2"""
filename = 'lmtprand.cdb'
else:
sql = """SELECT name AS key, name AS value FROM
mtasettings WHERE delivery_mode=2 AND
protocol=2"""
filename = 'lmtpnonrand.cdb'
domains = Session.execute(sql)
generate_cdb_file(domains, filename)
Session.close()
def command(self):
"command"
self.init()
if self.options.email is None:
print "\nA valid email is required\n"
print self.parser.print_help()
sys.exit(2)
starttime = arrow.utcnow()
if self.options.report_period == 'daily':
endtime = starttime - datetime.timedelta(days=1)
elif self.options.report_period == 'weekly':
endtime = starttime - datetime.timedelta(weeks=1)
else:
endtime = starttime - datetime.timedelta(weeks=4)
params = dict(spamscore=self.options.spamscore,
num=self.options.num,
starttime=starttime.datetime,
endtime=endtime.datetime)
sql = text("""SELECT clientip, COUNT(clientip) a
FROM messages WHERE sascore <= :spamscore
AND (timestamp BETWEEN :endtime AND :starttime)
GROUP BY clientip HAVING COUNT(clientip) >= :num
ORDER BY a DESC;""")
results = Session.execute(sql, params=params)
if results.rowcount:
if self.options.include_count is False:
records = [result.clientip for result in results]
else:
records = ["%s\t%d" % tuple(result) for result in results]
content = "\n".join(records)
if self.options.dry_run is True:
print content
else:
mailer = Mailer(get_conf_options(self.conf))
mailer.start()
email = Msg(author=self.conf['baruwa.reports.sender'],
to=self.options.email,
subject='TWL')
email.plain = content
try:
mailer.send(email)
except (TransportFailedException, MessageFailedException), err:
print >> sys.stderr, err
mailer.stop()
def create_ms_settings():
"""Create MS local SETTINGS_MAP"""
sql = text("""SELECT * FROM quickpeek""")
proxy = Session.execute(sql)
params = [dict(rank=row.rank,
internal=row.internal,
external=row.external,
hostname=row.hostname,
value=dbval(row.value))
for row in proxy]
conn = make_connection()
insert_sql = text("""INSERT INTO quickpeek
(rank, internal, external, hostname, value)
VALUES(:rank, :internal, :external, :hostname, :value)
""")
conn.execute(text("DELETE FROM quickpeek"))
conn.execute(insert_sql, params)
def command(self):
"command"
self.init()
if self.options.email is None:
print "\nA valid email is required\n"
print self.parser.print_help()
sys.exit(2)
starttime = arrow.utcnow().datetime
if self.options.report_period == 'daily':
endtime = starttime - datetime.timedelta(days=1)
elif self.options.report_period == 'weekly':
endtime = starttime - datetime.timedelta(weeks=1)
else:
endtime = starttime - datetime.timedelta(weeks=4)
params = dict(spamscore=self.options.spamscore, num=self.options.num,
starttime=starttime, endtime=endtime)
sql = text("""SELECT clientip, COUNT(clientip) a
FROM messages WHERE sascore >= :spamscore
AND (timestamp BETWEEN :endtime AND :starttime)
GROUP BY clientip HAVING COUNT(clientip) >= :num
ORDER BY a DESC;""")
results = Session.execute(sql, params=params)
if results.rowcount:
if self.options.include_count is False:
records = [result.clientip for result in results]
else:
records = ["%s\t%d" % tuple(result) for result in results]
content = "\n".join(records)
if self.options.dry_run is True:
print content
else:
mailer = Mailer(get_conf_options(self.conf))
mailer.start()
email = Msg(author=self.conf['baruwa.reports.sender'],
to=self.options.email, subject='TSL')
email.plain = content
try:
mailer.send(email)
except (TransportFailedException, MessageFailedException), err:
print >> sys.stderr, err
mailer.stop()
def create_ms_settings():
"""Create MS local SETTINGS_MAP"""
sql = text("""SELECT * FROM quickpeek""")
proxy = Session.execute(sql)
params = [
dict(rank=row.rank,
internal=row.internal,
external=row.external,
hostname=row.hostname,
value=dbval(row.value)) for row in proxy
]
conn = make_connection()
insert_sql = text("""INSERT INTO quickpeek
(rank, internal, external, hostname, value)
VALUES(:rank, :internal, :external, :hostname, :value)
""")
conn.execute(text("DELETE FROM quickpeek"))
conn.execute(insert_sql, params)
def update_rt_index(index_name, sphinxurl):
"Update the realtime index"
ts_sql_temp = """SELECT UNIX_TIMESTAMP(maxts) FROM indexer_counters WHERE
tablename='%s_delta'"""
ids_sql_temp = """SELECT id FROM %s_rt WHERE timestamp <= %%s
option max_matches=500"""
timestamp = Session.execute(ts_sql_temp % index_name).scalar()
conn = sphinx_connection(sphinxurl)
try:
cursor = conn.cursor()
cursor.execute(ids_sql_temp % index_name, [timestamp])
ids = [int(msgid[0]) for msgid in cursor.fetchall()]
if ids:
idstr = format_sphinx_ids(len(ids))
delsql = "DELETE FROM %s_rt WHERE id in (%s)" % (index_name, idstr)
cursor.execute(delsql, ids)
except Error, exp:
print >> sys.stderr, str(exp)
def update_rt_index(index_name, sphinxurl):
"Update the realtime index"
ts_sql_temp = """SELECT UNIX_TIMESTAMP(maxts) FROM indexer_counters WHERE
tablename='%s_delta'"""
ids_sql_temp = """SELECT id FROM %s_rt WHERE timestamp <= %%s
option max_matches=500"""
timestamp = Session.execute(ts_sql_temp % index_name).scalar()
conn = sphinx_connection(sphinxurl)
try:
cursor = conn.cursor()
cursor.execute(ids_sql_temp % index_name, [timestamp])
ids = [int(msgid[0]) for msgid in cursor.fetchall()]
if ids:
idstr = format_sphinx_ids(len(ids))
delsql = "DELETE FROM %s_rt WHERE id in (%s)" % (index_name, idstr)
cursor.execute(delsql, ids)
except Error, exp:
print >> sys.stderr, str(exp)
def process_messages(last_date):
"process messages table"
params = dict(date=last_date)
sql1 = text("""INSERT INTO archive
SELECT * FROM messages WHERE timestamp <
:date;""")
try:
Session.execute(sql1, params=params)
except IntegrityError, error:
Session.rollback()
sql = text("""DELETE FROM archive WHERE id in
(SELECT id FROM messages WHERE timestamp < :date);""")
Session.execute(sql, params=params)
Session.execute(sql1, params=params)
print >> sys.stderr, "Integrety error occured: %s" % str(error)
sys.exit(2)
def process_messages(last_date):
"process messages table"
params = dict(date=last_date)
sql1 = text("""INSERT INTO archive
SELECT * FROM messages WHERE timestamp <
:date;""")
try:
Session.execute(sql1, params=params)
except IntegrityError, error:
Session.rollback()
sql = text("""DELETE FROM archive WHERE id in
(SELECT id FROM messages WHERE timestamp < :date);""")
Session.execute(sql, params=params)
Session.execute(sql1, params=params)
print >> sys.stderr, "Integrety error occured: %s" % str(error)
sys.exit(2)
def command(self):
"command"
self.init()
interval = datetime.timedelta(days=self.options.days)
last_date = now() - interval
params = dict(date=last_date)
sql1 = text("""INSERT INTO archive
SELECT * FROM messages WHERE timestamp <
:date;""")
try:
Session.execute(sql1, params=params)
except IntegrityError, error:
Session.rollback()
sql = text("""DELETE FROM archive WHERE id in
(SELECT id FROM messages WHERE timestamp < :date);""")
Session.execute(sql, params=params)
Session.execute(sql1, params=params)
print >> sys.stderr, "Integrety error occured: %s" % str(error)
sys.exit(2)
def command(self):
"command"
self.init()
interval = datetime.timedelta(days=self.options.days)
last_date = datetime.datetime.now() - interval
params = dict(date=last_date)
sql1 = text("""INSERT INTO archive
SELECT * FROM messages WHERE timestamp <
:date;""")
try:
Session.execute(sql1, params=params)
except IntegrityError, error:
Session.rollback()
sql = text("""DELETE FROM archive WHERE id in
(SELECT id FROM messages WHERE timestamp < :date);"""
)
Session.execute(sql, params=params)
Session.execute(sql1, params=params)
print >> sys.stderr, "Integrety error occured: %s" % str(error)
sys.exit(2)
params = dict(date=last_date)
sql1 = text("""INSERT INTO archive
SELECT * FROM messages WHERE timestamp <
:date;""")
try:
Session.execute(sql1, params=params)
except IntegrityError, error:
Session.rollback()
sql = text("""DELETE FROM archive WHERE id in
(SELECT id FROM messages WHERE timestamp < :date);""")
Session.execute(sql, params=params)
Session.execute(sql1, params=params)
print >> sys.stderr, "Integrety error occured: %s" % str(error)
sys.exit(2)
sql = text("""DELETE FROM messages WHERE timestamp < :date;""")
result = Session.execute(sql, params=params)
sql = text("""DELETE FROM releases WHERE timestamp < :date;""")
Session.execute(sql, params=params)
Session.commit()
if result.rowcount > 0 and os.path.exists('/usr/bin/indexer'):
index_cmd = ['/usr/bin/indexer', '--rotate', 'messages']
pipe = subprocess.Popen(index_cmd,
stdout=subprocess.PIPE,
stderr=subprocess.PIPE)
_, stderr = pipe.communicate()
pipe.wait()
if stderr:
print >> sys.stderr, stderr
def prune_archive(last_date):
def delete_totals(mapper, connection, target):
"Delete totals for the domain"
query1 = text("DELETE FROM srcmsgtotals WHERE id='dom:'")
query2 = text("DELETE FROM dstmsgtotals WHERE id='dom:'")
Session.execute(query1, params=dict(dom=target.name))
Session.execute(query2, params=dict(dom=target.name))
def setup_app(command, conf, variables):
"""Place any commands to setup baruwa here"""
# Don't reload the app if it was loaded under the testing environment
if not pylons.test.pylonsapp:
load_environment(conf.global_conf, conf.local_conf)
# Create the tables if they don't already exist
print '-' * 100
log.info("Creating tables")
Base.metadata.create_all(bind=Session.bind)
basepath = os.path.dirname(os.path.dirname(__file__))
# Create the custom functions
print '-' * 100
log.info("Creating custom functions")
sqlfile = os.path.join(basepath,
'baruwa',
'config',
'sql',
'functions.sql')
if os.path.exists(sqlfile):
with open(sqlfile, 'r') as handle:
sql = handle.read()
try:
conn = Session.connection()
conn.execute(text(sql))
Session.commit()
except ProgrammingError:
Session.rollback()
defaultserver = Session.query(Server)\
.filter(Server.hostname == 'default')\
.all()
# Create the Mailscanner SQL config views
print '-' * 100
log.info("Populating initial sql")
sqlfile = os.path.join(basepath,
'baruwa',
'config',
'sql',
'integration.sql')
if os.path.exists(sqlfile):
with open(sqlfile, 'r') as handle:
sql = handle.read()
for sqlcmd in sql.split(';'):
if sqlcmd:
try:
sqlcmd = "%s;" % sqlcmd
Session.execute(text(sqlcmd))
Session.commit()
except ProgrammingError:
Session.rollback()
if not defaultserver:
log.info("Creating the default settings node")
dfls = Server('default', True)
Session.add(dfls)
confserial = ConfigSettings('confserialnumber',
'ConfSerialNumber',
0)
confserial.value = 1
confserial.server_id = 1
Session.add(confserial)
Session.commit()
log.info("Default settings node created !")
admin = Session.query(User).filter(User.account_type==1).all()
if not admin:
def timeout_handler(signum, frame):
raise TimeoutException()
old_handler = signal.signal(signal.SIGALRM, timeout_handler)
signal.alarm(30)
try:
create_user = raw_input('Do you want to configure '
'an admin account? (Y/N): ')
except (TimeoutException, EOFError):
sys.exit(0)
finally:
signal.signal(signal.SIGALRM, old_handler)
signal.alarm(0)
if str(create_user).lower() == 'y':
print '-' * 100
log.info("Creating initial admin account")
value_map = {'username': True, 'password1': True,
'password2': True, 'firstname': False,
'lastname': False, 'email': True}
values = {}
def get_input(field, required):
"Get user input"
prompt = "Please enter the %s:" % field
while 1:
if field in ['password1', 'password2']:
value = getpass.getpass(prompt=prompt)
else:
value = raw_input(prompt)
if not required:
break
if required and value.strip() != "":
if not field in ['email', 'password1', 'password2']:
break
if field == 'email':
if not ADDRESS_RE.match(value):
print "Please provide a valid email address."
else:
break
if field == 'password1':
try:
cracklib.VeryFascistCheck(value)
except ValueError, message:
print str(message)
else:
break
if field == 'password2':
if values['password1'] == value:
break
else:
print 'password2 does not match password1'
return value
for attr in value_map:
value = get_input(attr, value_map[attr])
values[attr] = value
user = User(values['username'], values['email'])
for name in ['firstname', 'lastname']:
if values[name]:
setattr(user, name, values[name])
user.internal = True
user.active = True
user.local = True
user.account_type = 1
user.set_password(values['password1'])
Session.add(user)
Session.commit()
def delete_totals(mapper, connection, target):
"Delete totals for the domain"
query1 = text("DELETE FROM srcmsgtotals WHERE id='dom:'")
query2 = text("DELETE FROM dstmsgtotals WHERE id='dom:'")
Session.execute(query1, params=dict(dom=target.name))
Session.execute(query2, params=dict(dom=target.name))
def create_relay_domains():
"""Generate relay domains cdb"""
sql = """SELECT name AS key, name AS value FROM relaydomains"""
domains = Session.execute(sql)
generate_cdb_file(domains, 'relaydomains.cdb')
Session.close()
def prune_table(table, last_date):
"Prune a table"
params = dict(date=last_date)
sql = text("""DELETE FROM %s WHERE timestamp < :date;""" % table)
Session.execute(sql, params=params)
Session.commit()
def setup_app(command, conf, variables):
"""Place any commands to setup baruwa here"""
# Don't reload the app if it was loaded under the testing environment
if not pylons.test.pylonsapp:
load_environment(conf.global_conf, conf.local_conf)
# Create the tables if they don't already exist
print '-' * 100
log.info("Creating tables")
Base.metadata.create_all(bind=Session.bind)
basepath = os.path.dirname(os.path.dirname(__file__))
# Create the custom functions
print '-' * 100
log.info("Creating custom functions")
sqlfile = os.path.join(basepath, 'baruwa', 'config', 'sql',
'functions.sql')
if os.path.exists(sqlfile):
with open(sqlfile, 'r') as handle:
sql = handle.read()
try:
conn = Session.connection()
conn.execute(text(sql))
Session.commit()
except ProgrammingError:
Session.rollback()
defaultserver = Session.query(Server)\
.filter(Server.hostname == 'default')\
.all()
# Create the Mailscanner SQL config views
print '-' * 100
log.info("Populating initial sql")
sqlfile = os.path.join(basepath, 'baruwa', 'config', 'sql',
'integration.sql')
if os.path.exists(sqlfile):
with open(sqlfile, 'r') as handle:
sql = handle.read()
for sqlcmd in sql.split(';'):
if sqlcmd:
try:
sqlcmd = "%s;" % sqlcmd
Session.execute(text(sqlcmd))
Session.commit()
except ProgrammingError:
Session.rollback()
if not defaultserver:
log.info("Creating the default settings node")
dfls = Server('default', True)
Session.add(dfls)
confserial = ConfigSettings('confserialnumber', 'ConfSerialNumber', 0)
confserial.value = 1
confserial.server_id = 1
Session.add(confserial)
Session.commit()
log.info("Default settings node created !")
admin = Session.query(User).filter(User.account_type == 1).all()
if not admin:
def timeout_handler(signum, frame):
raise TimeoutException()
old_handler = signal.signal(signal.SIGALRM, timeout_handler)
signal.alarm(30)
try:
create_user = raw_input('Do you want to configure '
'an admin account? (Y/N): ')
except (TimeoutException, EOFError):
sys.exit(0)
finally:
signal.signal(signal.SIGALRM, old_handler)
signal.alarm(0)
if str(create_user).lower() == 'y':
print '-' * 100
log.info("Creating initial admin account")
value_map = {
'username': True,
'password1': True,
'password2': True,
'firstname': False,
'lastname': False,
'email': True
}
values = {}
def get_input(field, required):
"Get user input"
prompt = "Please enter the %s:" % field
while 1:
if field in ['password1', 'password2']:
value = getpass.getpass(prompt=prompt)
else:
value = raw_input(prompt)
if not required:
break
if required and value.strip() != "":
if not field in ['email', 'password1', 'password2']:
break
if field == 'email':
if not ADDRESS_RE.match(value):
print "Please provide a valid email address."
else:
break
if field == 'password1':
try:
cracklib.VeryFascistCheck(value)
except ValueError, message:
print str(message)
else:
break
if field == 'password2':
if values['password1'] == value:
break
else:
print 'password2 does not match password1'
return value
for attr in value_map:
value = get_input(attr, value_map[attr])
values[attr] = value
user = User(values['username'], values['email'])
for name in ['firstname', 'lastname']:
if values[name]:
setattr(user, name, values[name])
user.internal = True
user.active = True
user.local = True
user.account_type = 1
user.set_password(values['password1'])
Session.add(user)
Session.commit()
params = dict(date=last_date)
sql1 = text("""INSERT INTO archive
SELECT * FROM messages WHERE timestamp <
:date;""")
try:
Session.execute(sql1, params=params)
except IntegrityError, error:
Session.rollback()
sql = text("""DELETE FROM archive WHERE id in
(SELECT id FROM messages WHERE timestamp < :date);""")
Session.execute(sql, params=params)
Session.execute(sql1, params=params)
print >> sys.stderr, "Integrety error occured: %s" % str(error)
sys.exit(2)
sql = text("""DELETE FROM messages WHERE timestamp < :date;""")
result = Session.execute(sql, params=params)
sql = text("""DELETE FROM releases WHERE timestamp < :date;""")
Session.execute(sql, params=params)
Session.commit()
if result.rowcount > 0 and os.path.exists('/usr/bin/indexer'):
index_cmd = ['/usr/bin/indexer', '--rotate', 'messages']
pipe = subprocess.Popen(index_cmd,
stdout=subprocess.PIPE,
stderr=subprocess.PIPE)
_, stderr = pipe.communicate()
pipe.wait()
if stderr:
print >> sys.stderr, stderr
def prune_archive(last_date):
def prune_table(table, last_date):
"Prune a table"
params = dict(date=last_date)
sql = text("""DELETE FROM %s WHERE timestamp < :date;""" % table)
Session.execute(sql, params=params)
Session.commit()
def setup_app(command, conf, variables):
"""Place any commands to setup baruwa here"""
# Don't reload the app if it was loaded under the testing environment
if not pylons.test.pylonsapp:
load_environment(conf.global_conf, conf.local_conf)
# Create the tables if they don't already exist
print '-' * 100
log.info("Creating tables")
Base.metadata.create_all(bind=Session.bind)
basepath = os.path.dirname(os.path.dirname(__file__))
# Create the custom functions
print '-' * 100
log.info("Creating custom functions")
sqlfile = os.path.join(basepath,
'baruwa',
'config',
'sql',
'functions.sql')
if os.path.exists(sqlfile):
with open(sqlfile, 'r') as handle:
sql = handle.read()
try:
conn = Session.connection()
conn.execute(text(sql))
Session.commit()
except ProgrammingError:
Session.rollback()
defaultserver = Session.query(Server)\
.filter(Server.hostname == 'default')\
.all()
# Create the Mailscanner SQL config views
print '-' * 100
log.info("Populating initial sql")
sqlfile = os.path.join(basepath,
'baruwa',
'config',
'sql',
'integration.sql')
if os.path.exists(sqlfile):
with open(sqlfile, 'r') as handle:
sql = handle.read()
for sqlcmd in sql.split(';'):
if sqlcmd:
try:
sqlcmd = "%s;" % sqlcmd
Session.execute(text(sqlcmd))
Session.commit()
except ProgrammingError:
Session.rollback()
if not defaultserver:
log.info("Creating the default settings node")
dfls = Server('default', True)
Session.add(dfls)
confserial = ConfigSettings('confserialnumber',
'ConfSerialNumber',
0)
confserial.value = 1
confserial.server_id = 1
Session.add(confserial)
Session.commit()
log.info("Default settings node created !")
