def POST(self):
user_data = web.input()
cid = int(user_data.get('cid', '-1'))
dir = user_data.get('dir', False)
web.header('Content-Type', 'application/json')
if cid == -1 or dir == False or (dir != 'inc' and dir != 'dec'):
return json.dumps({'status':-1,'msg':'params error'})
ip = utils.ip2long(web.ctx['ip'])
useragent = web.ctx.env['HTTP_USER_AGENT']
author = web.cookies().author
email = web.cookies().email
votes = db.select(T_COMMENT_VOTE, dict(id=cid,ip=ip), where="cid=$id and ipv4=$ip")
if len(votes):
return json.dumps({'status':0,'msg':'You have already voted'})
if dir == 'inc':
db.update(T_COMMENT, where='id=$cid', upnum=web.sqlliteral('upnum+1'), vars=dict(cid=cid))
elif dir == 'dec':
db.query("update comment set downnum = downnum+1 where id=$cid",vars=dict(cid=cid))
db.insert(T_COMMENT_VOTE, id=0, date=web.SQLLiteral("NOW()"), dir=dir, email = email, cid = cid, author = author, ipv4 = ip, useragent = useragent)
return json.dumps({'status':1,'msg':'OK'})
delete_record(conn=conn, rid=rid)
# Establish SQL connection.
try:
if settings.backend == 'ldap':
conn = ira_tool_lib.get_db_conn('iredadmin')
else:
conn = ira_tool_lib.get_db_conn('vmail')
except Exception, e:
sys.exit(
'<<< ERROR >>> Cannot connect to SQL database, aborted. Error: %s' %
repr(e))
# Get pathes of all maildirs.
sql_where = 'delete_date <= %s' % web.sqlquote(web.sqlliteral('NOW()'))
if delete_null_date:
sql_where = '(delete_date <= %s) OR (delete_date IS NULL)' % web.sqlquote(
web.sqlliteral('NOW()'))
qr = conn.select('deleted_mailboxes', where=sql_where)
if qr:
logger.info('Delete old mailboxes (%d in total).' % len(qr))
else:
logger.debug('No mailbox is scheduled to be removed.')
if not delete_null_date:
logger.debug(
"To remove mailboxes with empty schedule date, please run this script with argument '--delete-null-date'."
)
def delete_custom_envs(self):
common.db.update(self.table_nodes, "1", env=web.sqlliteral("NULL"))
import os, time
import web, pusher
import courier, simplethread
REPO_STORAGE = '/tmp/dudley_repo'
NOW = web.sqlliteral('now()')
pushcloud = pusher.pusher_from_url()
env = web.storage(os.environ)
buildqueue = simplethread.Queue()
def watchdb(db):
while 1:
try: buildqueue.get(timeout=60)
except simplethread.Empty: pass
for job in db.select('jobs', where="builder is null and done='f'"):
simplethread.spawn(lambda: start_build(db, job))
def get_buildserver(db, build_id):
buildservers = db.select('buildservers', where="building is null", limit=1).list()
if buildservers and db.update('buildservers', building=build_id, where="building is null and id=$buildservers[0].id", vars=locals()):
return buildservers[0]
else:
return False
def claim_job(db, job_id, build_id):
return db.update('jobs', builder=build_id, where="id=$job_id and builder is null", vars=locals())
def start_build(db, job):
build_id = db.insert('builds', job_id=job.id, updated_at=NOW)
if not claim_job(db, job.id, build_id):
# someone else is already building this job
######################
# Regular expressions.
#
# Email.
reEmail = r'''[\w\-][\w\-\.]*@[\w\-][\w\-\.]+[a-zA-Z]{2,15}'''
# Domain.
reDomain = r'''[\w\-][\w\-\.]*\.[a-z]{2,15}'''
# End Regular expressions.
####
#####################################
# Pre-defined values of SQL functions.
sqlUnixTimestamp = web.sqlliteral('UNIX_TIMESTAMP()')
#####
##############
# Validators
#
INVALID_EMAIL_CHARS = '~!#$%^&*()\\/\ '
INVALID_DOMAIN_CHARS = '~!#$%^&*()+\\/\ '
def is_email(s):
s = str(s)
if len(set(s) & set(INVALID_EMAIL_CHARS)) > 0 \
or '.' not in s \
or s.count('@') != 1:
def delete_domains(domains, keep_mailbox_days=0, conn=None):
domains = [str(d).lower() for d in domains if iredutils.is_domain(d)]
if not domains:
return (True, )
if not conn:
_wrap = SQLWrap()
conn = _wrap.conn
try:
keep_mailbox_days = abs(int(keep_mailbox_days))
except:
keep_mailbox_days = 0
if not session.get('is_global_admin'):
_max_days = max(settings.DAYS_TO_KEEP_REMOVED_MAILBOX)
if keep_mailbox_days > _max_days:
# Get the max days
keep_mailbox_days = _max_days
# Keep mailboxes 'forever', set to 100 years.
if keep_mailbox_days == 0:
sql_keep_days = web.sqlliteral('Null')
else:
if settings.backend == 'pgsql':
sql_keep_days = web.sqlliteral(
"""CURRENT_TIMESTAMP + INTERVAL '%d DAYS'""" %
keep_mailbox_days)
else:
# settings.backend == 'mysql'
sql_keep_days = web.sqlliteral(
'DATE_ADD(CURDATE(), INTERVAL %d DAY)' % keep_mailbox_days)
sql_vars = {
'domains': domains,
'admin': session.get('username'),
'sql_keep_days': sql_keep_days
}
# Log maildir paths of existing users
try:
if settings.backend == 'pgsql':
sql_raw = '''
INSERT INTO deleted_mailboxes (username, maildir, domain, admin, delete_date)
SELECT username, \
storagebasedirectory || '/' || storagenode || '/' || maildir, \
domain, \
$admin, \
$sql_keep_days
FROM mailbox
WHERE domain IN $domains'''
else:
# settings.backend == 'mysql'
sql_raw = '''
INSERT INTO deleted_mailboxes (username, maildir, domain, admin, delete_date)
SELECT username, \
CONCAT(storagebasedirectory, '/', storagenode, '/', maildir) AS maildir, \
domain, \
$admin, \
$sql_keep_days
FROM mailbox
WHERE domain IN $domains'''
conn.query(sql_raw, vars=sql_vars)
except Exception as e:
logger.error(e)
try:
# Delete domain name
for tbl in [
'domain', 'alias', 'domain_admins', 'mailbox',
'recipient_bcc_domain', 'recipient_bcc_user',
'sender_bcc_domain', 'sender_bcc_user', 'forwardings',
'moderators', settings.SQL_TBL_USED_QUOTA
]:
conn.delete(tbl, vars=sql_vars, where='domain IN $domains')
# Delete alias domain
conn.delete(
'alias_domain',
vars=sql_vars,
where='alias_domain IN $domains OR target_domain IN $domains')
# Delete domain admins
for d in domains:
conn.delete('domain_admins',
vars={'domain': '%%@' + d},
where='username LIKE $domain')
except Exception as e:
return (False, repr(e))
for d in domains:
log_activity(event='delete', domain=d, msg="Delete domain: %s." % d)
return (True, )
######################
# Regular expressions.
#
# Email.
reEmail = r"""[\w\-][\w\-\.]*@[\w\-][\w\-\.]+[a-zA-Z]{2,6}"""
# Domain.
reDomain = r"""[\w\-][\w\-\.]*\.[a-z]{2,6}"""
# End Regular expressions.
####
#####################################
# Pre-defined values of SQL functions.
sqlNOW = web.sqlliteral('NOW()')
sqlUnixTimestamp = web.sqlliteral('UNIX_TIMESTAMP()')
#####
##############
# Validators
#
INVALID_EMAIL_CHARS = '~!#$%^&*()+\\/\ '
INVALID_DOMAIN_CHARS = '~!#$%^&*()+\\/\ '
def isEmail(s):
s = str(s)
if len(set(s) & set(INVALID_EMAIL_CHARS)) > 0 \
or '.' not in s \
or s.count('@') != 1:
import os, time
import web, pusher
import courier, simplethread
REPO_STORAGE = '/tmp/dudley_repo'
NOW = web.sqlliteral('now()')
pushcloud = pusher.pusher_from_url()
env = web.storage(os.environ)
buildqueue = simplethread.Queue()
def watchdb(db):
while 1:
try:
buildqueue.get(timeout=60)
except simplethread.Empty:
pass
for job in db.select('jobs', where="builder is null and done='f'"):
simplethread.spawn(lambda: start_build(db, job))
def get_buildserver(db, build_id):
buildservers = db.select('buildservers', where="building is null",
limit=1).list()
if buildservers and db.update(
'buildservers',
building=build_id,
where="building is null and id=$buildservers[0].id",
vars=locals()):
return buildservers[0]
else:
def delete_users(accounts, keep_mailbox_days=0, conn=None):
accounts = [str(v) for v in accounts if iredutils.is_email(v)]
if not accounts:
return (True, )
# Keep mailboxes 'forever', set to 100 years.
try:
keep_mailbox_days = abs(int(keep_mailbox_days))
except:
if session.get('is_global_admin'):
keep_mailbox_days = 0
else:
_max_days = max(settings.DAYS_TO_KEEP_REMOVED_MAILBOX)
if keep_mailbox_days > _max_days:
# Get the max days
keep_mailbox_days = _max_days
if keep_mailbox_days == 0:
sql_keep_days = web.sqlliteral('Null')
else:
if settings.backend == 'mysql':
sql_keep_days = web.sqlliteral(
'DATE_ADD(CURDATE(), INTERVAL %d DAY)' % keep_mailbox_days)
elif settings.backend == 'pgsql':
sql_keep_days = web.sqlliteral(
"""CURRENT_TIMESTAMP + INTERVAL '%d DAYS'""" %
keep_mailbox_days)
sql_vars = {
'accounts': accounts,
'admin': session.get('username'),
'sql_keep_days': sql_keep_days
}
# Log maildir path of deleted users.
if settings.backend == 'mysql':
sql_raw = '''
INSERT INTO deleted_mailboxes (username, maildir, domain, admin, delete_date)
SELECT username, \
CONCAT(storagebasedirectory, '/', storagenode, '/', maildir) AS maildir, \
SUBSTRING_INDEX(username, '@', -1), \
$admin, \
$sql_keep_days
FROM mailbox
WHERE username IN $accounts'''
elif settings.backend == 'pgsql':
sql_raw = '''
INSERT INTO deleted_mailboxes (username, maildir, domain, admin, delete_date)
SELECT username, \
storagebasedirectory || '/' || storagenode || '/' || maildir, \
SPLIT_PART(username, '@', 2), \
$admin, \
$sql_keep_days
FROM mailbox
WHERE username IN $accounts'''
try:
if not conn:
_wrap = SQLWrap()
conn = _wrap.conn
conn.query(sql_raw, vars=sql_vars)
except:
pass
try:
for tbl in [
'mailbox', 'domain_admins', 'recipient_bcc_user',
'sender_bcc_user', settings.SQL_TBL_USED_QUOTA
]:
conn.delete(tbl, vars=sql_vars, where='username IN $accounts')
# remove destination bcc addresses.
for tbl in [
'recipient_bcc_user', 'sender_bcc_user',
'recipient_bcc_domain', 'sender_bcc_domain'
]:
conn.delete(tbl, vars=sql_vars, where='bcc_address IN $accounts')
# Remove user from `forwardings`, including:
# - per-user mail forwardings
# - per-domain catch-all account
# - alias membership
# - alias moderators
conn.delete('forwardings',
vars=sql_vars,
where='address IN $accounts OR forwarding IN $accounts')
# remove destination moderators.
conn.delete('moderators',
vars=sql_vars,
where='moderator IN $accounts')
except Exception as e:
return (False, repr(e))
log_activity(event='delete',
domain=accounts[0].split('@', 1)[-1],
msg="Delete user: %s." % ', '.join(accounts))
return (True, )
import web
from . import config
db = web.database(config.DATABASE_URL)
CURRENT_TIMESTAMP = web.sqlliteral("(current_timestamp at time zone 'utc')")
def get_sketch(id):
return db.where("sketch", id=id).first()
def save_sketch(id, mode, title, code):
db.update("sketch",
mode=mode,
title=title,
code=code,
last_updated=CURRENT_TIMESTAMP,
where="id=$id",
vars={"id": id})
def new_sketch(mode, title, code):
"""Creates a new sketch and returns the id.
"""
return db.insert("sketch",
mode=mode,
title=title,
code=code)
# Delete record.
delete_record(conn=conn, rid=rid)
# Establish SQL connection.
try:
if settings.backend == 'ldap':
conn = ira_tool_lib.get_db_conn('iredadmin')
else:
conn = ira_tool_lib.get_db_conn('vmail')
except Exception, e:
sys.exit('<<< ERROR >>> Cannot connect to SQL database, aborted. Error: %s' % repr(e))
# Get pathes of all maildirs.
sql_where = 'delete_date <= %s' % web.sqlquote(web.sqlliteral('NOW()'))
if delete_null_date:
sql_where = '(delete_date <= %s) OR (delete_date IS NULL)' % web.sqlquote(web.sqlliteral('NOW()'))
qr = conn.select('deleted_mailboxes', where=sql_where)
if qr:
logger.info('Delete old mailboxes (%d in total).' % len(qr))
else:
logger.debug('No mailbox is scheduled to be removed.')
if not delete_null_date:
logger.debug("To remove mailboxes with empty schedule date, please run this script with argument '--delete-null-date'.")
if not delete_without_timestamp:
logger.debug("To remove mailboxes which don't contain a timesamp in maildir path, please run this script with argument '--delete-without-timestamp'.")
def add(self, domain, data):
# Get domain name, username, cn.
self.domain = web.safestr(data.get('domainName')).strip().lower()
self.username = web.safestr(data.get('username')).strip().lower()
self.mail = self.username + '@' + self.domain
sql_vars = {
'mail': self.mail,
}
if not iredutils.isDomain(self.domain):
return (False, 'INVALID_DOMAIN_NAME')
if self.domain != domain:
return (False, 'PERMISSION_DENIED')
if not iredutils.isEmail(self.mail):
return (False, 'INVALID_MAIL')
# Check account existing.
connutils = connUtils.Utils()
if connutils.isEmailExists(self.mail):
return (False, 'ALREADY_EXISTS')
# Get domain profile.
domainLib = domainlib.Domain()
resultOfDomainProfile = domainLib.profile(domain=self.domain)
if resultOfDomainProfile[0] is True:
self.domainProfile = resultOfDomainProfile[1]
else:
return resultOfDomainProfile
# Check account limit.
adminLib = adminlib.Admin()
numberOfExistAccounts = adminLib.getNumberOfManagedAccounts(
accountType='user', domains=[self.domain])
if self.domainProfile.mailboxes == -1:
return (False, 'NOT_ALLOWED')
elif self.domainProfile.mailboxes > 0:
if self.domainProfile.mailboxes <= numberOfExistAccounts:
return (False, 'EXCEEDED_DOMAIN_ACCOUNT_LIMIT')
columns = {
'userid': self.mail,
'domain': self.domain,
}
# Check spare quota and number of spare account limit.
# Get quota from form.
self.mailQuota = str(data.get('mailQuota')).strip()
self.defaultUserQuota = self.domainProfile.get('defaultuserquota', 0)
if self.mailQuota.isdigit():
self.mailQuota = int(self.mailQuota)
else:
self.mailQuota = self.defaultUserQuota
# Re-calculate mail quota if this domain has max quota limit.
if self.domainProfile.maxquota > 0:
# Get used quota.
qr = domainLib.getAllocatedQuotaSize(domain=self.domain)
if qr[0] is True:
self.allocatedQuota = qr[1]
else:
return qr
spareQuota = self.domainProfile.maxquota - self.allocatedQuota / 1024 / 1024
if spareQuota > 0:
if spareQuota < self.mailQuota:
self.mailQuota = spareQuota
else:
# No enough quota.
return (False, 'EXCEEDED_DOMAIN_QUOTA_SIZE')
columns['maxmail_size'] = self.mailQuota * 1024 * 1024
#
# Get password from <form>.
#
newpw = web.safestr(data.get('newpw', ''))
confirmpw = web.safestr(data.get('confirmpw', ''))
# Get password length limit from domain profile or global setting.
self.minPasswordLength = self.domainProfile.get(
'minpasswordlength', cfg.general.get('min_passwd_length', '0'))
self.maxPasswordLength = self.domainProfile.get(
'maxpasswordlength', cfg.general.get('max_passwd_length', '0'))
resultOfPW = iredutils.verifyNewPasswords(
newpw,
confirmpw,
min_passwd_length=self.minPasswordLength,
max_passwd_length=self.maxPasswordLength,
)
if resultOfPW[0] is True:
if 'storePasswordInPlainText' in data:
columns['passwd'] = iredutils.getSQLPassword(resultOfPW[1],
pwscheme='PLAIN')
columns['encryption_type'] = ''
else:
columns['passwd'] = iredutils.getSQLPassword(resultOfPW[1])
columns[
'encryption_type'] = settings.SQL_DEFAULT_PASSWD_SCHEME.lower(
)
else:
return resultOfPW
# Get display name from <form>
columns['name'] = data.get('cn', '')
# Assign new user to default mail aliases.
assignedAliases = [
str(addr).lower()
for addr in str(self.domainProfile.defaultuseraliases).split(',')
if iredutils.isEmail(addr)
]
try:
# Store new user in SQL db.
self.conn.insert('dbmail_users', **columns)
# Get dbmail_users.user_idnr.
qr = self.conn.select(
'dbmail_users',
vars=sql_vars,
what='user_idnr,client_idnr',
where='userid=$mail',
limit=1,
)
p = qr[0]
user_idnr, client_idnr = p.user_idnr, p.client_idnr
self.conn.insert(
'dbmail_aliases',
alias=self.mail,
deliver_to=user_idnr,
client_idnr=client_idnr,
)
# Create and subscribe to default IMAP folders.
if settings.DBMAIL_CREATE_DEFAULT_IMAP_FOLDERS:
# Create default IMAP folders.
imap_folders = [
'(%d, "%s")' % (user_idnr, fld)
for fld in settings.DBMAIL_DEFAULT_IMAP_FOLDERS
]
self.conn.query(
'''INSERT INTO dbmail_mailboxes (owner_idnr, name) VALUES %s'''
% ','.join(imap_folders))
# Subscribe to folders by default.
self.conn.query(
'''INSERT INTO dbmail_subscription (user_id, mailbox_id)
SELECT owner_idnr, mailbox_idnr FROM dbmail_mailboxes WHERE owner_idnr = %d
''' % user_idnr)
# Assign new user to default mail aliases.
if len(assignedAliases) > 0:
for ali in assignedAliases:
try:
self.conn.update(
'dbmail_aliases',
vars={
'mail': self.mail,
'ali': ali,
'user_idnr': user_idnr,
},
where='alias = $ali AND deliver_to <> $user_idnr',
deliver_to=web.sqlliteral(
'CONCAT($mail, ",", deliver_to)'),
)
except:
pass
vars_addition_sql = {
'user_idnr': user_idnr,
'mail': self.mail,
'username': self.username,
'domain': self.domain,
}
# Execute addition SQL commands after successfully created new users.
if settings.DBMAIL_SQL_FOR_NEWLY_CREATED_USER:
try:
for sql_cmd in settings.DBMAIL_SQL_FOR_NEWLY_CREATED_USER:
self.conn.query(sql_cmd, vars=vars_addition_sql)
except Exception:
pass
# Create Amavisd policy for newly created user.
if settings.AMAVISD_EXECUTE_SQL_WITHOUT_ENABLED and settings.AMAVISD_SQL_FOR_NEWLY_CREATED_USER:
try:
from libs.amavisd.core import AmavisdWrap
amwrap = AmavisdWrap()
for sql_cmd in settings.AMAVISD_SQL_FOR_NEWLY_CREATED_USER:
amwrap.db.query(sql_cmd, vars=vars_addition_sql)
except:
pass
web.logger(
msg="Create user: %s." % (self.mail),
domain=self.domain,
event='create',
)
return (True, )
except Exception, e:
return (False, str(e))
str(addr).lower() for addr in old_member_of_aliases
if addr not in member_of_aliases
]
# Remove user from aliases if not in both existing_aliases and new_aliases.
# Assign user to new aliases.
if newly_assigned_aliases:
try:
self.conn.update(
'dbmail_aliases',
vars={
'mail': self.mail,
'newly_assigned_aliases': newly_assigned_aliases,
},
where='alias IN $newly_assigned_aliases',
deliver_to=web.sqlliteral(
'CONCAT($mail, ",", deliver_to)'),
)
except:
pass
# Remove user from old assigned aliases.
if removed_aliases:
# Get profiles of alias accounts.
alias_profiles = self.conn.select(
'dbmail_aliases',
vars={
'removed_aliases': removed_aliases,
},
what='alias,deliver_to',
where='alias IN $removed_aliases',
)
