def listAccounts(self, domain, cur_page=1):
'''List all users.'''
if not iredutils.isDomain(domain):
return (False, 'INVALID_DOMAIN_NAME')
self.domain = str(domain)
# Pre-defined.
self.total = 0
try:
resultOfTotal = self.conn.select(
'mailbox',
what='COUNT(username) AS total',
where='domain=%s' % web.sqlquote(self.domain),
)
if len(resultOfTotal) == 1:
self.total = resultOfTotal[0].total or 0
resultOfRecords = self.conn.select(
'mailbox',
# Just query what we need to reduce memory use.
what='username,name,quota,bytes,messages,employeeid,active,created',
where='domain = %s' % web.sqlquote(self.domain),
order='username ASC',
limit=session['pageSizeLimit'],
offset=(cur_page-1) * session['pageSizeLimit'],
)
return (True, self.total, list(resultOfRecords))
except Exception, e:
return (False, str(e))
def proxyfunc(self, *args, **kw):
if 'mail' in kw.keys() and iredutils.isEmail(kw.get('mail')):
self.domain = web.safestr(kw['mail']).split('@')[-1]
elif 'domain' in kw.keys() and iredutils.isDomain(kw.get('domain')):
self.domain = web.safestr(kw['domain'])
else:
return False
self.admin = session.get('username')
if not iredutils.isEmail(self.admin):
return False
# Check domain global admin.
if session.get('domainGlobalAdmin') is True:
return func(self, *args, **kw)
else:
# Check whether is domain admin.
try:
result = self.conn.select(
'domain_admins',
what='username',
where='''username=%s AND domain IN %s''' % (
web.sqlquote(self.admin),
web.sqlquote([self.domain, 'ALL']),
),
)
except Exception, e:
result = {}
if len(result) != 1:
return func(self, *args, **kw)
else:
return web.seeother('/users' + '?msg=PERMISSION_DENIED&domain=' + self.domain)
def get_where(context='', query=None, user_id=None):
where = 'status is NULL or status is not NULL'
# if a context is entered
if context == 'new':
where = 'status is NULL and (referee_rating is not NULL or affiliated)'
elif context == 'pending':
where = 'referee_rating is NULL'
elif context == 'admitted':
where = 'status = "admitted"'
elif context == 'rejected':
where = 'status = "rejected"'
elif user_id and context == 'reviewed':
user_id = web.sqlquote(str(user_id))
where = 'a.id = v.applicant_id and v.user_id = %s' % user_id
# (a.id = c.applicant_id and c.user_id = %s)''' % (user_id, user_id)
# if in search mode
if query:
columns = 'first_name, last_name, a.email, affiliation, department, referee_name, status, \
occupation, website, interests'.split(', ')
query = web.sqlquote('%%' + query.encode('utf-8') + '%%')
where = (' like %s or ' % query).join(columns)
where += ' like %s' % query
where += ' or concat(first_name, " ", last_name) like ' + query
return where
def listAccounts(self, domain, cur_page=1):
'''List all users.'''
if not iredutils.isDomain(domain):
return (False, 'INVALID_DOMAIN_NAME')
self.domain = str(domain)
# Pre-defined.
self.total = 0
try:
resultOfTotal = self.conn.select(
'mailbox',
what='COUNT(username) AS total',
where='domain=%s' % web.sqlquote(self.domain),
)
if len(resultOfTotal) == 1:
self.total = resultOfTotal[0].total or 0
resultOfRecords = self.conn.select(
'mailbox',
# Just query what we need to reduce memory use.
what=
'username,name,quota,bytes,messages,employeeid,active,created',
where='domain = %s' % web.sqlquote(self.domain),
order='username ASC',
limit=session['pageSizeLimit'],
offset=(cur_page - 1) * session['pageSizeLimit'],
)
return (True, self.total, list(resultOfRecords))
except Exception, e:
return (False, str(e))
def isDomainExists(self, domain):
if not iredutils.isDomain(domain):
return True
try:
result = self.conn.select(
'domain',
what='domain',
where='domain = %s' % web.sqlquote(domain),
limit=1,
)
if len(result) > 0:
# Exists.
return True
result = self.conn.select(
'alias_domain',
what='alias_domain',
where='alias_domain = %s' % web.sqlquote(domain),
limit=1,
)
if len(result) > 0:
# Alias domain exists.
return True
else:
return False
except:
# Return True as exist to not allow to create new domain/account.
return True
def getManagedDomains(self, admin, domainNameOnly=False, listedOnly=False,):
self.admin = web.safestr(admin)
if not iredutils.isEmail(self.admin):
return (False, 'INCORRECT_USERNAME')
self.sql_where = ''
self.sql_left_join = ''
if listedOnly is True:
self.sql_where = 'AND domain_admins.username=%s' % web.sqlquote(self.admin)
else:
self.sql_left_join = 'OR domain_admins.domain="ALL"' % web.sqlquote(self.admin)
try:
result = self.conn.query(
"""
SELECT domain.domain
FROM domain
LEFT JOIN domain_admins ON (domain.domain=domain_admins.domain %s)
WHERE domain_admins.username=%s %s
ORDER BY domain_admins.domain
""" % (self.sql_left_join, web.sqlquote(self.admin), self.sql_where)
)
if domainNameOnly is True:
domains = []
for i in result:
if iredutils.isDomain(i.domain):
domains += [str(i.domain).lower()]
return (True, domains)
else:
return (True, list(result))
except Exception, e:
return (False, str(e))
def restriction(**kwargs):
rdns_name = kwargs['smtp_session_data']['reverse_client_name']
client_address = kwargs['smtp_session_data']['client_address']
# Bypass outgoing emails.
if kwargs['sasl_username']:
logger.debug('Found SASL username, bypass rDNS check for outbound.')
return SMTP_ACTIONS['default']
if rdns_name == 'unknown':
logger.debug('No reverse dns name, bypass.')
return SMTP_ACTIONS['default']
if is_trusted_client(client_address):
return SMTP_ACTIONS['default']
_policy_rdns_names = [rdns_name]
_splited = rdns_name.split('.')
for i in range(len(_splited)):
_name = '.' + '.'.join(_splited)
_policy_rdns_names.append(_name)
_splited.pop(0)
logger.debug('All policy rDNS names: %s' % repr(_policy_rdns_names))
conn = kwargs['conn_iredapd']
# Query whitelist
sql = """SELECT rdns
FROM wblist_rdns
WHERE rdns IN %s AND wb='W'
LIMIT 1""" % sqlquote(_policy_rdns_names)
logger.debug('[SQL] Query whitelisted rDNS names: \n%s' % sql)
qr = conn.execute(sql)
record = qr.fetchone()
if record:
rdns = str(record[0]).lower()
logger.info("[{}] Reverse client hostname is whitelisted: {}.".format(
client_address, rdns))
# better use 'DUNNO' instead of 'OK'
return SMTP_ACTIONS['default']
# Query blacklist
sql = """SELECT rdns
FROM wblist_rdns
WHERE rdns IN %s AND wb='B'
LIMIT 1""" % sqlquote(_policy_rdns_names)
logger.debug('[SQL] Query blacklisted rDNS names: \n%s' % sql)
qr = conn.execute(sql)
record = qr.fetchone()
if record:
rdns = str(record[0]).lower()
logger.info("[{}] Reverse client hostname is blacklisted: {}".format(
client_address, rdns))
return reject_action
return SMTP_ACTIONS['default']
def update(self, domain, profile_type, data,):
self.profile_type = str(profile_type)
self.domain = str(domain)
# Pre-defined update key:value.
updates = {'modified': iredutils.sqlNOW,}
if self.profile_type == 'general':
# Get name
self.cn = data.get('cn', '')
updates['description'] = self.cn
# Get default quota for new user.
self.defaultQuota = str(data.get('defaultQuota'))
if self.defaultQuota.isdigit():
updates['defaultuserquota'] = int(self.defaultQuota)
if session.get('domainGlobalAdmin') is True:
# Get account status
if 'accountStatus' in data.keys():
updates['active'] = 1
else:
updates['active'] = 0
updates['maxquota'] = 0
# Update SQL db with columns: maxquota, active.
try:
self.conn.update(
'domain',
where='domain=%s' % web.sqlquote(self.domain),
**updates
)
except Exception, e:
return (False, str(e))
# Get list of domain admins.
domainAdmins = [str(v).lower()
for v in data.get('domainAdmin', [])
if iredutils.isEmail(str(v))
]
try:
# Delete all records first.
self.conn.delete('domain_admins', where='domain=%s' % web.sqlquote(self.domain),)
# Add new admins.
if len(domainAdmins) > 0:
v = []
for adm in domainAdmins:
v += [{'username': adm,
'domain': self.domain,
'created': iredutils.sqlNOW,
'active': 1,
}]
self.conn.multiple_insert('domain_admins', values=v,)
except Exception, e:
return (False, str(e))
def _has_tags(tag_list):
""" Forms a condition to check for certain tags """
query_str = ""
for tag_namespace, tag in tag_list:
anded = "(tags.tag_namespace = " + web.sqlquote(tag_namespace) + \
" AND " + "tags.tag = " + web.sqlquote(tag) + ")"
query_str += (query_str and (" OR " + anded) or anded)
return query_str and ("(" + query_str + ")") or query_str
def search_locations_of_project(search_query, tag_list, project=None, loctype='point'):
project_id = project.id
conds1 = "(" + " OR ".join(
["(" + _has_query_str(fieldname, search_query) + ")"
for fieldname in ("title", )]) + ")"
select1 = """(SELECT id, 2 as weight FROM locations
WHERE type = """ + web.sqlquote(loctype) + """
AND """ + conds1 + """)"""
conds2 = "(" + " OR ".join(
["(" + _has_query_str(fieldname, search_query) + ")"
for fieldname in ("text", )]) + ")"
select2 = """(SELECT location_id as id, 2 as weight FROM notes
WHERE """ + conds2 + """)"""
select3 = """(SELECT locations_users_tags.location_id as id,
4 as weight
FROM tags, locations_users_tags
WHERE """ + _has_tags(tag_list) + """
AND locations_users_tags.tag_id = tags.id
)"""
selects = (""
+ select1
+ "\nUNION "
+ select2
+ "\nUNION "
+ select3
)
wq = """
SELECT locations.*,
locids.weight,
0 as distance,
MAX(notes.added) as last_comment,
COUNT(notes.location_id) as comments_count,
users.username as author
FROM (""" + selects + """) AS locids
LEFT JOIN locations ON (locations.id = locids.id)
LEFT JOIN projects_points as pp ON
(pp.location_id = locations.id AND pp.visible = 1)
LEFT JOIN notes ON (notes.visible = 1 AND notes.location_id = locids.id)
LEFT JOIN users ON (users.id = locations.user_id)
WHERE
locations.visible = 1
AND pp.project_id = """ + str(int(project_id)) + """
AND locations.type = """ + web.sqlquote(loctype) + """
GROUP BY """ + LOCATIONS_GROUP_BY('locations') + """, locids.weight, users.username
ORDER BY locids.weight DESC, last_comment DESC
;"""
return web.query(wq)
def _has_tags(tag_list):
""" Forms a condition to check for certain tags """
query_str = ""
for tag_namespace, tag in tag_list:
anded = "(tags.tag_namespace = " + web.sqlquote(tag_namespace) + \
" AND " + "tags.tag = " + web.sqlquote(tag) + ")"
query_str += (query_str and (" OR " + anded) or anded)
return query_str and ("(" + query_str + ")") or query_str
def search_locations_of_project(search_query,
tag_list,
project=None,
loctype='point'):
project_id = project.id
conds1 = "(" + " OR ".join([
"(" + _has_query_str(fieldname, search_query) + ")"
for fieldname in ("title", )
]) + ")"
select1 = """(SELECT id, 2 as weight FROM locations
WHERE type = """ + web.sqlquote(loctype) + """
AND """ + conds1 + """)"""
conds2 = "(" + " OR ".join([
"(" + _has_query_str(fieldname, search_query) + ")"
for fieldname in ("text", )
]) + ")"
select2 = """(SELECT location_id as id, 2 as weight FROM notes
WHERE """ + conds2 + """)"""
select3 = """(SELECT locations_users_tags.location_id as id,
4 as weight
FROM tags, locations_users_tags
WHERE """ + _has_tags(tag_list) + """
AND locations_users_tags.tag_id = tags.id
)"""
selects = ("" + select1 + "\nUNION " + select2 + "\nUNION " + select3)
wq = """
SELECT locations.*,
locids.weight,
0 as distance,
MAX(notes.added) as last_comment,
COUNT(notes.location_id) as comments_count,
users.username as author
FROM (""" + selects + """) AS locids
LEFT JOIN locations ON (locations.id = locids.id)
LEFT JOIN projects_points as pp ON
(pp.location_id = locations.id AND pp.visible = 1)
LEFT JOIN notes ON (notes.visible = 1 AND notes.location_id = locids.id)
LEFT JOIN users ON (users.id = locations.user_id)
WHERE
locations.visible = 1
AND pp.project_id = """ + str(int(project_id)) + """
AND locations.type = """ + web.sqlquote(loctype) + """
GROUP BY """ + LOCATIONS_GROUP_BY(
'locations') + """, locids.weight, users.username
ORDER BY locids.weight DESC, last_comment DESC
;"""
return web.query(wq)
def GET(self, tid=None):
info = {}
info['title'] = '给我留言'
msgs = {}
reply = []
if tid:
tid = tid[1:]
msgs = list(_db.getLeavemsg(where='id='+web.sqlquote(tid)))[0]
reply = _db.getLeavemsg(where='parent='+web.sqlquote(tid), order='time asc')
left = unicode(render.message(msgs, list(reply)))
return render.layout(info, left, self.sidebar())
def create_mailaddr(conn, addresses):
for addr in addresses:
addr_type = utils.is_valid_amavisd_address(addr)
if addr_type in utils.MAILADDR_PRIORITIES:
priority = utils.MAILADDR_PRIORITIES[addr_type]
try:
sql = "INSERT INTO mailaddr (email, priority) VALUES (%s, %s)" % (
sqlquote(addr), sqlquote(priority))
conn.execute(sql)
except:
pass
return True
def insert_pac(pac):
pac_id[0] += 1
pa = {'id':pac_id[0]} #@@ stable ids
for z, val in pac.items():
if z in lob_pac: pa[lob_pac[z]] = val
db_pac = db.select('lob_pac', where='LOWER(name)='+web.sqlquote(pa['name'].lower()))
if not db_pac:
db_pac = db.select('lob_pac', where='name ilike '+web.sqlquote('%'+cleanPacName(pa['name'])+'%') )
if not db_pac:
db.insert('lob_pac', seqname=False, **pa)
else:
pa = db_pac[0]
db.insert('lob_pac_filings',seqname=False, pac_id=pa['id'], filing_id=fil['id'])
def profile(self, domain):
self.domain = web.safestr(domain)
if not iredutils.isDomain(self.domain):
return (False, 'INVALID_DOMAIN_NAME')
try:
qr = self.conn.query(
'''
SELECT
domain.*,
sbcc.bcc_address AS sbcc_addr,
sbcc.active AS sbcc_active,
rbcc.bcc_address AS rbcc_addr,
rbcc.active AS rbcc_active,
alias.goto AS catchall,
alias.active AS catchall_active,
COUNT(DISTINCT mailbox.username) AS mailbox_count,
COUNT(DISTINCT alias.address) AS alias_count
FROM domain
LEFT JOIN sender_bcc_domain AS sbcc ON (sbcc.domain=domain.domain)
LEFT JOIN recipient_bcc_domain AS rbcc ON (rbcc.domain=domain.domain)
LEFT JOIN domain_admins ON (domain.domain = domain_admins.domain)
LEFT JOIN mailbox ON (domain.domain = mailbox.domain)
LEFT JOIN alias ON (
domain.domain = alias.domain
AND alias.address <> alias.goto
AND alias.address <> %s
)
WHERE domain.domain=%s
GROUP BY
domain.domain, domain.description, domain.aliases,
domain.mailboxes, domain.maxquota, domain.quota,
domain.transport, domain.backupmx, domain.created,
domain.active
ORDER BY domain.domain
LIMIT 1
''' % (web.sqlquote(self.domain),
web.sqlquote(self.domain),
)
)
if len(qr) == 1:
# Return first list element.
return (True, list(qr)[0])
else:
return (False, 'NO_SUCH_OBJECT')
except Exception, e:
return (False, str(e))
def auth(self, username, password, verifyPassword=False,):
if not iredutils.isEmail(username):
return (False, 'INVALID_USERNAME')
if len(password) == 0:
return (False, 'EMPTY_PASSWORD')
# Query admin.
result = self.conn.select(
'admin',
where="username=%s AND active=1" % web.sqlquote(username),
limit=1,
)
if len(result) == 1:
# It's a valid admin.
record = result[0]
# Get salt string from password which stored in SQL.
tmpsalt = str(record.password).split('$')
tmpsalt[-1] = ''
salt = '$'.join(tmpsalt)
# Compare passwords.
if md5crypt.md5crypt(password, salt) == str(record.password):
if verifyPassword is not True:
session['username'] = username
session['logged'] = True
# Set preferred language.
session['lang'] = str(record.language) or 'en_US'
# Set session['domainGlobalAdmin']
try:
result = self.conn.select(
'domain_admins',
what='domain',
where='''username=%s AND domain="ALL"''' % web.sqlquote(username),
limit=1,
)
if len(result) == 1:
session['domainGlobalAdmin'] = True
except:
pass
return (True,)
else:
return (False, 'INVALID_CREDENTIALS')
else:
return (False, 'INVALID_CREDENTIALS')
def search(query, offset=0, limit=10):
query = get_nice_query(query)
if not query:
return [], False
def sqlands(left, lst):
return left + (" and %s " % left).join(lst)
q = [str(web.sqlquote(w)) for w in query.split()]
tag_query = web.sqlors("tag = ", q)
q = [str(web.sqlquote("%%" + w + "%%")) for w in query.split()]
where = []
for c in ["title", "url", "description", "author"]:
where.append(sqlands("%s like " % c, q))
text_query = " or ".join(where)
params = {
"tag_query": tag_query,
"text_query": text_query,
"offset": offset,
"limit": limit + 1,
"size": len(query),
}
m = list(
db.query(
"\
(select distinct m.id, title, url, description, author, screenshot, \
calculated_vote as votes, m.datetime_created as dates \
from modules as m left join tags as t on m.id = t.module_id \
where %(tag_query)s \
group by t.module_id \
having count(t.module_id) >= %(size)d) \
union \
(select distinct m.id, title, url, description, author, screenshot, \
calculated_vote as votes, m.datetime_created as dates \
from modules as m \
where %(text_query)s \
order by calculated_vote desc, datetime_created desc) \
order by votes desc, dates desc limit %(limit)d offset %(offset)d"
% params
)
)
has_next = len(m) > limit
return m[:limit], has_next
def num_managed_domains(admin=None,
disabled_only=False,
first_char=None,
conn=None):
num = 0
if not admin:
admin = session.get('username')
if not conn:
_wrap = SQLWrap()
conn = _wrap.conn
sql_where = ''
if disabled_only is True:
sql_where += 'domain.active=0'
if first_char:
first_char = first_char[0].lower()
if sql_where:
sql_where += ' AND domain.domain LIKE %s' % web.sqlquote(
first_char + '%')
else:
sql_where += 'domain.domain LIKE %s' % web.sqlquote(first_char +
'%')
try:
if sql_lib_general.is_global_admin(admin=admin, conn=conn):
qr = conn.select('domain',
what='COUNT(domain) AS total',
where=sql_where or None)
else:
if sql_where:
sql_where = 'AND ' + sql_where
qr = conn.query("""
SELECT COUNT(domain.domain) AS total
FROM domain
LEFT JOIN domain_admins ON (domain.domain=domain_admins.domain)
WHERE domain_admins.username=$admin %s
""" % (sql_where),
vars={'admin': admin})
num = qr[0].total or 0
except:
log_traceback()
return num
def apply_outbound_wblist(conn, sender_ids, recipient_ids):
# Return if no valid sender or recipient id.
if not (sender_ids and recipient_ids):
logger.debug("No valid sender id or recipient id.")
return SMTP_ACTIONS["default"]
# Bypass outgoing emails.
if settings.WBLIST_BYPASS_OUTGOING_EMAIL:
logger.debug(
"Bypass outgoing email as defined in WBLIST_BYPASS_OUTGOING_EMAIL."
)
return SMTP_ACTIONS["default"]
# Get wblist
sql = """SELECT rid, sid, wb
FROM outbound_wblist
WHERE sid IN %s
AND rid IN %s""" % (sqlquote(sender_ids),
sqlquote(recipient_ids))
logger.debug("[SQL] Query outbound wblist: \n{}".format(sql))
qr = conn.execute(sql)
wblists = qr.fetchall()
if not wblists:
# no wblist
logger.debug("No wblist found.")
return SMTP_ACTIONS["default"]
logger.debug("Found outbound wblist: {}".format(wblists))
# Check sender addresses
# rids/recipients are orded by priority
for sid in sender_ids:
for rid in recipient_ids:
if (rid, sid, "W") in wblists:
logger.info(
"Whitelisted: outbound_wblist=({}, {}, 'W')".format(
rid, sid))
return SMTP_ACTIONS[
"default"] + " outbound_wblist=({}, {}, 'W')".format(
rid, sid)
if (rid, sid, "B") in wblists:
logger.info(
"Blacklisted: outbound_wblist=({}, {}, 'B')".format(
rid, sid))
return reject_action
return SMTP_ACTIONS["default"]
def update(self, domain, profile_type, data):
self.profile_type = str(profile_type)
self.domain = str(domain)
# Pre-defined update key:value.
updates = {"modified": iredutils.sqlNOW}
if self.profile_type == "general":
# Get name
self.cn = data.get("cn", "")
updates["description"] = self.cn
# Get default quota for new user.
self.defaultQuota = str(data.get("defaultQuota"))
if self.defaultQuota.isdigit():
updates["defaultuserquota"] = int(self.defaultQuota)
if session.get("domainGlobalAdmin") is True:
# Get account status
if "accountStatus" in data.keys():
updates["active"] = 1
else:
updates["active"] = 0
updates["maxquota"] = 0
# Update SQL db with columns: maxquota, active.
try:
self.conn.update("domain", where="domain=%s" % web.sqlquote(self.domain), **updates)
except Exception, e:
return (False, str(e))
# Get list of domain admins.
domainAdmins = [str(v).lower() for v in data.get("domainAdmin", []) if iredutils.isEmail(str(v))]
try:
# Delete all records first.
self.conn.delete("domain_admins", where="domain=%s" % web.sqlquote(self.domain))
# Add new admins.
if len(domainAdmins) > 0:
v = []
for adm in domainAdmins:
v += [{"username": adm, "domain": self.domain, "created": iredutils.sqlNOW, "active": 1}]
self.conn.multiple_insert("domain_admins", values=v)
except Exception, e:
return (False, str(e))
def profile(self, domain):
self.domain = web.safestr(domain)
if not iredutils.isDomain(self.domain):
return (False, "INVALID_DOMAIN_NAME")
try:
qr = self.conn.query(
"""
SELECT
domain.*,
sbcc.bcc_address AS sbcc_addr,
sbcc.active AS sbcc_active,
rbcc.bcc_address AS rbcc_addr,
rbcc.active AS rbcc_active,
alias.goto AS catchall,
alias.active AS catchall_active,
COUNT(DISTINCT mailbox.username) AS mailbox_count,
COUNT(DISTINCT alias.address) AS alias_count
FROM domain
LEFT JOIN sender_bcc_domain AS sbcc ON (sbcc.domain=domain.domain)
LEFT JOIN recipient_bcc_domain AS rbcc ON (rbcc.domain=domain.domain)
LEFT JOIN domain_admins ON (domain.domain = domain_admins.domain)
LEFT JOIN mailbox ON (domain.domain = mailbox.domain)
LEFT JOIN alias ON (
domain.domain = alias.domain
AND alias.address <> alias.goto
AND alias.address <> %s
)
WHERE domain.domain=%s
GROUP BY
domain.domain, domain.description, domain.aliases,
domain.mailboxes, domain.maxquota, domain.quota,
domain.transport, domain.backupmx, domain.created,
domain.active
ORDER BY domain.domain
LIMIT 1
"""
% (web.sqlquote(self.domain), web.sqlquote(self.domain))
)
if len(qr) == 1:
# Return first list element.
return (True, list(qr)[0])
else:
return (False, "NO_SUCH_OBJECT")
except Exception, e:
return (False, str(e))
def get_id_of_external_addresses(conn, addresses):
"""Return list of `mailaddr.id` of external addresses."""
ids = []
if not addresses:
logger.debug("No addresses, return empty list of ids.")
return ids
# Get `mailaddr.id` of external addresses, ordered by priority
sql = """SELECT id, email
FROM mailaddr
WHERE email IN %s
ORDER BY priority DESC""" % sqlquote(addresses)
logger.debug("[SQL] Query external addresses: \n{}".format(sql))
try:
qr = conn.execute(sql)
qr_addresses = qr.fetchall()
except Exception as e:
logger.error(
"Error while getting list of id of external addresses: {}, SQL: {}"
.format(repr(e), sql))
return ids
if qr_addresses:
ids = [int(r.id) for r in qr_addresses]
if not ids:
# don't waste time if we don't even have senders stored in sql db.
logger.debug("No record found in SQL database.")
return []
else:
logger.debug("Addresses (in `mailaddr`): {}".format(qr_addresses))
return ids
def updaterecent(userid, threadid):
""" updates the user's 'recent_threads' column with the new thread that they just visited
returns the updated thread list.
TOOD refactor this, it's a prety ugly implementation of recent threads.
"""
threadlist = people.getperson(userid).recent_threads
recent = []
if threadlist:
recent = threadlist.split(' ')
if threadid in recent:
recent.remove(threadid)
recent.insert(0, threadid)
print recent
text = recent.pop(0) # take care of the fence post.
for r in recent:
text += ' ' + r
web.update('people', where='id=%s' % web.sqlquote(userid), recent_threads=text)
return 'threads'
def getthread(id):
thread = web.select('threads', where='id=%s' % web.sqlquote(id), limit=1)
if thread:
return thread[0]
else:
return None
def loadbill(fn, maplightid=None):
bill = xmltramp.load(fn)
d = bill2dict(bill)
d.maplightid = maplightid
try:
bill_id = d.id
db.insert('bill', seqname=False, **d)
except IntegrityError:
bill_id = d.pop('id')
db.update('bill', where="id=" + web.sqlquote(bill_id), **d)
positions = {}
for vote in bill.actions['vote':]:
if not vote().get('roll'): continue
rolldoc = '/us/%s/rolls/%s%s-%s.xml' % (
d.session, vote('where'), vote('datetime')[:4], vote('roll'))
roll = xmltramp.load(GOVTRACK_CRAWL + rolldoc)
for voter in roll['voter':]:
positions[govtrackp(voter('id'))] = fixvote(voter('vote'))
if None in positions: del positions[None]
with db.transaction():
db.delete('position', where='bill_id=$bill_id', vars=locals())
for p, v in positions.iteritems():
db.insert('position',
seqname=False,
bill_id=bill_id,
politician_id=p,
vote=v)
def get_access_policy(mail, account_type, conn):
"""Get access policy of (mlmmj) mailing list or mail alias account.
Returns access policy (string) or None if account doesn't exist."""
_policy = None
if account_type == 'alias':
table = 'alias'
elif account_type == 'maillist':
table = 'maillists'
else:
return _policy
sql = """SELECT accesspolicy
FROM %s
WHERE address=%s
LIMIT 1""" % (table, sqlquote(mail))
logger.debug("[SQL] query access policy: \n{}".format(sql))
qr = conn.execute(sql)
record = qr.fetchone()
logger.debug("[SQL] query result: {}".format(repr(record)))
if record:
_policy = str(record[0]).lower()
return _policy
def get_alias_target_domain(alias_domain, conn):
"""Query target domain of given alias domain name."""
alias_domain = str(alias_domain).lower()
if not utils.is_domain(alias_domain):
logger.debug(
"Given alias domain ({}) is not a valid domain name.".format(
alias_domain))
return None
sql = """SELECT alias_domain.target_domain
FROM alias_domain, domain
WHERE domain.active=1
AND domain.domain=alias_domain.target_domain
AND alias_domain.alias_domain=%s
LIMIT 1""" % sqlquote(alias_domain)
logger.debug(
"[SQL] query target domain of given alias domain ({}): \n{}".format(
alias_domain, repr(sql)))
qr = conn.execute(sql)
sql_record = qr.fetchone()
logger.debug("[SQL] query result: {}".format(repr(sql_record)))
if sql_record:
target_domain = str(sql_record[0]).lower()
return target_domain
else:
return None
def num_users_under_domains(conn,
domains,
disabled_only=False,
first_char=None):
# Count separated admin accounts
num = 0
if not domains:
return num
sql_where = ''
if disabled_only:
sql_where = ' AND active=0'
if first_char:
sql_where += ' AND username LIKE %s' % web.sqlquote(
first_char.lower() + '%')
sql_vars = {'domains': domains}
try:
qr = conn.select('mailbox',
vars=sql_vars,
what='COUNT(username) AS total',
where='domain IN $domains %s' % sql_where)
if qr:
num = qr[0].total or 0
except:
pass
return num
def getDomainAdmins(self, domain, mailOnly=False):
self.domain = str(domain)
if not iredutils.isDomain(self.domain):
return (False, "INVALID_DOMAIN_NAME")
try:
qr = self.conn.query(
"""
SELECT
admin.username, admin.name, admin.language,
admin.created, admin.active
FROM admin
LEFT JOIN domain_admins ON (domain_admins.username=admin.username)
WHERE domain_admins.domain=%s
"""
% web.sqlquote(self.domain)
)
if mailOnly is True:
admins = []
for adm in qr:
admins += [adm.username]
return (True, admins)
else:
return (True, list(qr))
except Exception, e:
return (False, str(e))
def listAccounts(self, cur_page=1):
admin = session.get('username')
page = int(cur_page) or 1
sql_where = ''
if session.get('domainGlobalAdmin') is not True:
sql_where = ' WHERE domain_admins.username = %s' % web.sqlquote(
admin)
# RAW sql command used to get records.
rawSQLOfRecords = """
SELECT
a.domain, a.description, a.aliases, a.mailboxes, a.maxquota, a.quota,
a.transport, a.backupmx, a.active,
NULLIF(b.alias_count, 0) AS alias_count,
NULLIF(c.mailbox_count, 0) AS mailbox_count,
NULLIF(c.quota_count, 0) AS quota_count
FROM domain AS a
LEFT JOIN (
SELECT domain, COUNT(address) AS alias_count
FROM alias
WHERE
address<>goto
AND address<>domain
AND address NOT IN (SELECT username FROM mailbox)
GROUP BY domain
) AS b ON (a.domain=b.domain)
LEFT JOIN (
SELECT domain,
SUM(mailbox.quota) AS quota_count,
COUNT(username) AS mailbox_count
FROM mailbox
GROUP BY domain
) AS c ON (a.domain=c.domain)
LEFT JOIN domain_admins ON (domain_admins.domain=a.domain)
%s
GROUP BY
a.domain, a.description, a.aliases, a.mailboxes, a.maxquota, a.quota,
a.transport, a.backupmx, a.active,
mailbox_count, alias_count, quota_count
ORDER BY a.domain
LIMIT %d
OFFSET %d
""" % (
sql_where,
settings.PAGE_SIZE_LIMIT,
(page - 1) * settings.PAGE_SIZE_LIMIT,
)
if self.is_global_admin(admin):
try:
resultOfTotal = self.conn.select(
'domain',
what='COUNT(domain) AS total',
)
resultOfRecords = self.conn.query(rawSQLOfRecords)
except Exception, e:
return (False, str(e))
def loadbill(fn, maplightid=None):
bill = xmltramp.load(fn)
d = bill2dict(bill)
d.maplightid = maplightid
try:
bill_id = d.id
db.insert('bill', seqname=False, **d)
except IntegrityError:
bill_id = d.pop('id')
db.update('bill', where="id=" + web.sqlquote(bill_id), **d)
positions = {}
for vote in bill.actions['vote':]:
if not vote().get('roll'): continue
rolldoc = '/us/%s/rolls/%s%s-%s.xml' % (
d.session, vote('where'), vote('datetime')[:4], vote('roll'))
roll = xmltramp.load(GOVTRACK_CRAWL + rolldoc)
for voter in roll['voter':]:
positions[govtrackp(voter('id'))] = fixvote(voter('vote'))
if None in positions: del positions[None]
with db.transaction():
db.delete('position', where='bill_id=$bill_id', vars=locals())
for p, v in positions.iteritems():
db.insert('position', seqname=False,
bill_id=bill_id, politician_id=p, vote=v)
def loadroll(fn):
roll = web.storage()
roll.id = fn.split('/')[-1].split('.')[0]
vote = xmltramp.load(fn)
if vote['bill':]:
b = vote.bill
roll.bill_id = 'us/%s/%s%s' % (b('session'), b('type'), b('number'))
else:
roll.bill_id = None
roll.type = str(vote.type)
roll.question = str(vote.question)
roll.required = str(vote.required)
roll.result = str(vote.result)
try:
db.insert('roll', seqname=False, **roll)
except IntegrityError:
if not db.update('roll',
where="id=" + web.sqlquote(roll.id),
bill_id=roll.bill_id):
print "\nMissing bill:", roll.bill_id
raise NotDone
with db.transaction():
db.delete('vote', where="roll_id=$roll.id", vars=locals())
for voter in vote['voter':]:
rep = govtrackp(voter('id'))
if rep:
db.insert('vote',
seqname=False,
politician_id=rep,
roll_id=roll.id,
vote=fixvote(voter('vote')))
else:
pass #@@!--check again after load_everyone
def get_page_by_title(title):
"""
>>> db.query("SELECT * FROM foo WHERE x = $x", vars=dict(x='f'), _test=True)
<sql: "SELECT * FROM foo WHERE x = 'f'">
>>> db.query("SELECT * FROM foo WHERE x = " + sqlquote('f'), _test=True)
"""
try:
sql = """SELECT p.id, p.created, p.title, v.content, v.id AS version, v.created AS modified, u.username
FROM versions v
JOIN pages p
ON v.pageid = p.id
JOIN users u
ON v.userid = u.id
WHERE p.title = """ + web.sqlquote(title) + """
ORDER BY version DESC"""
pages = db.query(sql)
page = pages[0]
if not page.modified:
page.modified = 0
if not page.created:
page.created = 0
return page
#return db.where('pages', title=title)[0]
except IndexError:
return None
def set_account_status(conn,
accounts,
account_type,
enable_account=False):
"""Set account status.
accounts -- an iterable object (list/tuple) filled with accounts.
account_type -- possible value: domain, admin, user, alias
enable_account -- possible value: True, False
"""
if account_type in ['admin', 'user']:
# email
accounts = [str(v).lower() for v in accounts if iredutils.is_email(v)]
else:
# domain name
accounts = [str(v).lower() for v in accounts if iredutils.is_domain(v)]
if not accounts:
return (True, )
# 0: disable, 1: enable
account_status = 0
action = 'disable'
if enable_account:
account_status = 1
action = 'active'
if account_type == 'domain':
# handle with function which handles admin privilege
qr = sql_lib_domain.enable_disable_domains(domains=accounts,
action=action)
return qr
elif account_type == 'admin':
# [(<table>, <column-used-for-query>), ...]
table_column_maps = [("admin", "username")]
elif account_type == 'alias':
table_column_maps = [
("alias", "address"),
("forwardings", "address"),
]
else:
# account_type == 'user'
table_column_maps = [
("mailbox", "username"),
("forwardings", "address"),
]
for (_table, _column) in table_column_maps:
sql_where = '{} IN {}'.format(_column, web.sqlquote(accounts))
try:
conn.update(_table,
where=sql_where,
active=account_status)
except Exception as e:
return (False, repr(e))
log_activity(event=action,
msg="{} {}: {}.".format(action.title(), account_type, ', '.join(accounts)))
return (True, )
def delete(self, domains=[]):
if not isinstance(domains, types.ListType):
return (False, "INVALID_DOMAIN_NAME")
self.domains = [str(v).lower() for v in domains if iredutils.isDomain(v)]
self.sql_domains = web.sqlquote(domains)
# Delete domain and related records.
try:
self.conn.delete("alias", where="domain IN %s" % self.sql_domains)
self.conn.delete(
"alias_domain", where="alias_domain IN %s OR target_domain IN %s" % (self.sql_domains, self.sql_domains)
)
self.conn.delete("domain_admins", where="domain IN %s" % self.sql_domains)
self.conn.delete("mailbox", where="domain IN %s" % self.sql_domains)
self.conn.delete("recipient_bcc_domain", where="domain IN %s" % self.sql_domains)
self.conn.delete("recipient_bcc_user", where="domain IN %s" % self.sql_domains)
self.conn.delete("sender_bcc_domain", where="domain IN %s" % self.sql_domains)
self.conn.delete("sender_bcc_user", where="domain IN %s" % self.sql_domains)
# Finally, delete from table `domain` to make sure all related
# records were deleted.
self.conn.delete("domain", where="domain IN %s" % self.sql_domains)
for d in self.domains:
web.logger(msg="Delete domain: %s." % (d), domain=d, event="delete")
return (True,)
except Exception, e:
return (False, str(e))
def get_id_of_local_addresses(conn, addresses):
"""Return list of `users.id` of local addresses."""
# Get `users.id` of local addresses
sql = """SELECT id, email
FROM users
WHERE email IN %s
ORDER BY priority DESC""" % sqlquote(addresses)
logger.debug("[SQL] Query local addresses: \n{}".format(sql))
ids = []
try:
qr = conn.execute(sql)
qr_addresses = qr.fetchall()
if qr_addresses:
ids = [int(r.id) for r in qr_addresses]
logger.debug("Local addresses (in `amavisd.users`): {}".format(
qr_addresses))
except Exception as e:
logger.error("Error while executing SQL command: {}".format(repr(e)))
if not ids:
# don't waste time if we don't have any per-recipient wblist.
logger.debug("No record found in SQL database.")
return []
else:
return ids
def _has_query_str(fieldname, qs):
query_str = ""
for q in [WORDCHARS.sub('', c.strip()) for c in qs.split()]:
qq = '[[:<:]]%s[[:>:]]' % q
cond = REGEXQ % (fieldname, web.sqlquote(qq))
query_str += query_str and (" OR " + cond) or cond
return query_str
def loadroll(fn):
roll = web.storage()
roll.id = fn.split('/')[-1].split('.')[0]
vote = xmltramp.load(fn)
if vote['bill':]:
b = vote.bill
roll.bill_id = 'us/%s/%s%s' % (b('session'), b('type'), b('number'))
else:
roll.bill_id = None
roll.type = str(vote.type)
roll.question = str(vote.question)
roll.required = str(vote.required)
roll.result = str(vote.result)
try:
db.insert('roll', seqname=False, **roll)
except IntegrityError:
if not db.update('roll', where="id=" + web.sqlquote(roll.id), bill_id=roll.bill_id):
print "\nMissing bill:", roll.bill_id
raise NotDone
with db.transaction():
db.delete('vote', where="roll_id=$roll.id", vars=locals())
for voter in vote['voter':]:
rep = govtrackp(voter('id'))
if rep:
db.insert('vote', seqname=False,
politician_id=rep, roll_id=roll.id, vote=fixvote(voter('vote')))
else:
pass #@@!--check again after load_everyone
def delete(self, domain, mails=[]):
self.domain = str(domain)
if not iredutils.isDomain(self.domain):
return (False, 'INVALID_DOMAIN_NAME')
if not isinstance(mails, types.ListType):
return (False, 'INVALID_MAIL')
self.mails = [
str(v).lower() for v in mails
if iredutils.isEmail(v) and str(v).endswith('@' + self.domain)
]
self.sqlMails = web.sqlquote(self.mails)
# Delete domain and related records.
try:
self.conn.delete('mailbox', where='username IN %s' % self.sqlMails)
self.conn.delete('alias', where='address IN %s' % self.sqlMails)
self.conn.delete('recipient_bcc_user',
where='username IN %s' % self.sqlMails)
self.conn.delete('sender_bcc_user',
where='username IN %s' % self.sqlMails)
# TODO Remove email from alias.goto.
#self.conn.delete()
web.logger(
msg="Delete user: %s." % ', '.join(self.mails),
domain=self.domain,
event='delete',
)
return (True, )
except Exception, e:
return (False, str(e))
def _has_query_str(fieldname, qs):
query_str = ""
for q in [WORDCHARS.sub('', c.strip()) for c in qs.split()]:
qq = '[[:<:]]%s[[:>:]]' % q
cond = REGEXQ % (fieldname, web.sqlquote(qq))
query_str += query_str and (" OR " + cond) or cond
return query_str
def getAccountUsedQuota(accounts):
# @accounts: must be list/tuple of email addresses.
# Pre-defined dict of used quotas.
# {'*****@*****.**': {'bytes': INTEGER, 'messages': INTEGER,}}
accountUsedQuota = {}
# Get used quota.
if len(accounts) > 0:
try:
result_used_quota = web.admindb.select(
models.UsedQuota.__table__,
where='%s IN %s' % (
models.UsedQuota.username,
web.sqlquote(accounts),
),
what='%s,%s,%s' % (
models.UsedQuota.username,
models.UsedQuota.bytes,
models.UsedQuota.messages,
),
)
for uq in result_used_quota:
accountUsedQuota[uq.get(models.UsedQuota.username)] = {
models.UsedQuota.bytes: uq.get(models.UsedQuota.bytes, 0),
models.UsedQuota.messages: uq.get(models.UsedQuota.messages, 0),
}
except Exception, e:
pass
def getperson(id):
person = web.select("people", where="id=%s" % web.sqlquote(id), limit=1)
if person:
return person[0]
else:
return None
def isEmailExists(self, mail):
# Return True if account is invalid or exist.
self.mail = web.safestr(mail)
if not iredutils.isEmail(mail):
return True
self.sqlMail = web.sqlquote(self.mail)
try:
resultOfAlias = self.conn.select(
'alias',
what='address',
where='address=%s' % self.sqlMail,
limit=1,
)
resultOfMailbox = self.conn.select(
'mailbox',
what='username',
where='username=%s' % self.sqlMail,
limit=1,
)
if len(resultOfAlias) == 1 or len(resultOfMailbox) == 1:
return True
else:
return False
except Exception, e:
return True
def getUsedBytesMessages(self, domain=None):
"""Return (messages, bytes)"""
if domain is None:
resultOfSum = self.conn.query(
'''
SELECT
SUM(messages) AS messages,
SUM(bytes) AS bytes
FROM mailbox
'''
)
counterOfSum = resultOfSum[0]
else:
if not iredutils.isDomain(domain):
return (0, 0)
# Check domain access
if self.isDomainAdmin(domain=domain, admin=session.get('username'),):
resultOfSum = self.conn.query(
'''
SELECT
SUM(messages) AS messages,
SUM(bytes) AS bytes
FROM mailbox
WHERE domain = %s
''' % web.sqlquote(domain)
)
counterOfSum = resultOfSum[0]
else:
return (0, 0)
return (counterOfSum.messages, counterOfSum.bytes)
def isGlobalAdmin(self, admin=None,):
if admin is None:
return False
elif admin == session.get('username'):
if session.get('domainGlobalAdmin') is True:
return True
else:
return False
admin = str(admin)
# Not logged admin.
try:
result = self.conn.select(
'domain_admins',
what='username',
where='''username=%s AND domain="ALL"''' % web.sqlquote(admin),
limit=1,
)
if len(result) == 1:
return True
else:
return False
except Exception, e:
return False
def delete(self, domain, mails=[]):
self.domain = str(domain)
if not iredutils.isDomain(self.domain):
return (False, 'INVALID_DOMAIN_NAME')
if not isinstance(mails, types.ListType):
return (False, 'INVALID_MAIL')
self.mails = [str(v).lower() for v in mails if iredutils.isEmail(v) and str(v).endswith('@'+self.domain)]
self.sqlMails = web.sqlquote(self.mails)
# Delete domain and related records.
try:
self.conn.delete('mailbox', where='username IN %s' % self.sqlMails)
self.conn.delete('alias', where='address IN %s' % self.sqlMails)
self.conn.delete('recipient_bcc_user', where='username IN %s' % self.sqlMails)
self.conn.delete('sender_bcc_user', where='username IN %s' % self.sqlMails)
# TODO Remove email from alias.goto.
#self.conn.delete()
web.logger(
msg="Delete user: %s." % ', '.join(self.mails),
domain=self.domain,
event='delete',
)
return (True,)
except Exception, e:
return (False, str(e))
def profile(self, domain, mail):
self.mail = web.safestr(mail)
self.domain = self.mail.split('@', 1)[-1]
if self.domain != domain:
return web.seeother('/domains?msg=PERMISSION_DENIED')
if not self.mail.endswith('@' + self.domain):
return web.seeother('/domains?msg=PERMISSION_DENIED')
try:
result = self.conn.query(
'''
SELECT
mailbox.*,
alias.address AS alias_address,
alias.goto AS alias_goto,
alias.active AS alias_active,
sbcc.username AS sbcc_username,
sbcc.bcc_address AS sbcc_bcc_address,
rbcc.username AS rbcc_username,
rbcc.bcc_address AS rbcc_bcc_address
FROM mailbox
LEFT JOIN alias ON (mailbox.username = alias.address)
LEFT JOIN sender_bcc_user AS sbcc ON (mailbox.username = sbcc.username)
LEFT JOIN recipient_bcc_user AS rbcc ON (mailbox.username = rbcc.username)
WHERE mailbox.username = %s
LIMIT 1
''' % web.sqlquote(self.mail)
)
return (True, list(result)[0])
except Exception, e:
return (False, str(e))
def setAccountStatus(self, accounts, accountType, active=True):
# accounts must be a list/tuple.
# accountType in ['domain', 'user', 'admin', 'alias',]
# active: True -> active, False -> disabled
if not len(accounts) > 0:
return (True,)
self.accountType = str(accountType)
if active is True:
self.active = 1
self.action = 'Active'
else:
self.active = 0
self.action = 'Disable'
if self.accountType == 'domain':
self.accounts = [str(v) for v in accounts if iredutils.isDomain(v)]
try:
self.conn.update(
'domain',
where='domain IN %s' % (web.sqlquote(self.accounts)),
active=self.active,
)
except Exception, e:
return (False, str(e))
def test_timerange():
s1 = 'having conn[links] > 1000'
parser = rule_parser.RuleParser({}, 'dst', s1, default_timerange)
sql = parser.sql
t_start = datetime(2014, 1, 1)
t_stop = datetime(2014, 1, 2)
t_stop_sql = datetime(2014, 1, 1, 23, 59, 59)
assert sql.get_where() == ""
sql.set_timerange(t_start, t_stop)
assert sql.get_where() == "WHERE timestamp BETWEEN {} AND {}".format(web.sqlquote(t_start), web.sqlquote(t_stop_sql))
s2 = 'protocol UDP'
parser = rule_parser.RuleParser({}, 'dst', s2, default_timerange)
sql = parser.sql
t_start = datetime(2014, 1, 3)
t_stop = datetime(2014, 1, 4)
t_stop_sql = datetime(2014, 1, 3, 23, 59, 59)
assert sql.get_where() == "WHERE protocol = 'UDP'"
sql.set_timerange(t_start, t_stop)
assert sql.get_where() == "WHERE timestamp BETWEEN {} AND {} AND (protocol = 'UDP')".format(web.sqlquote(t_start), web.sqlquote(t_stop_sql))
s3 = 'protocol UDP having conn[links] >1000'
parser = rule_parser.RuleParser({}, 'dst', s3, default_timerange)
sql = parser.sql
t_start = datetime(2014, 1, 5)
t_stop = datetime(2014, 1, 6)
t_stop_sql = datetime(2014, 1, 5, 23, 59, 59)
assert sql.get_where() == "WHERE protocol = 'UDP'"
sql.set_timerange(t_start, t_stop)
assert sql.get_where() == "WHERE timestamp BETWEEN {} AND {} AND (protocol = 'UDP')".format(web.sqlquote(t_start), web.sqlquote(t_stop_sql))