def get_first_char_of_all_domains(conn=None):
"""Get first character of all domains."""
if not conn:
_wrap = SQLWrap()
conn = _wrap.conn
admin = session.get('username')
chars = []
try:
if sql_lib_general.is_global_admin(admin=admin, conn=conn):
qr = conn.select(
'domain',
what='SUBSTRING(domain FROM 1 FOR 1) AS first_char',
group='first_char')
else:
qr = conn.query(
"""SELECT SUBSTRING(domain.domain FROM 1 FOR 1) AS first_char
FROM domain
LEFT JOIN domain_admins ON (domain.domain=domain_admins.domain)
WHERE domain_admins.username=$admin
GROUP BY first_char""",
vars={'admin': admin})
if qr:
chars = [i.first_char.upper() for i in qr]
chars.sort()
return (True, chars)
except Exception as e:
logger.error(e)
return (False, repr(e))
def get_primary_and_alias_domains(conn, domain):
"""Query LDAP to get all available alias domain names of given domain.
Return list of alias domain names.
@conn -- ldap connection cursor
@domain -- domain name
"""
if not utils.is_domain(domain):
return []
try:
_f = "(&(objectClass=mailDomain)(|(domainName=%s)(domainAliasName=%s)))" % (
domain, domain)
qr = conn.search_s(
settings.ldap_basedn,
1, # 1 == ldap.SCOPE_ONELEVEL
_f,
['domainName', 'domainAliasName'])
if qr:
(_dn, _ldif) = qr[0]
_all_domains = _ldif.get('domainName', []) + _ldif.get(
'domainAliasName', [])
return list(set(_all_domains))
except Exception as e:
# Log and return if LDAP error occurs
logger.error('Error while querying alias domains of domain (%s): %s' %
(domain, repr(e)))
return []
def lambda_handler(event, _) -> object:
try:
logger.info(f'Received an event: ${event}')
body = json.loads(event['body'])
logger.info(f'Saving new definitions: {body}')
user_id = body['userId']
word = body['word']
definitions = body['definitions']
table = dynamo_db.Table(os.environ['DYNAMODB_TABLE'])
result = table.put_item(
Item={
'userId': user_id,
'word': word,
'definitions': definitions
}
)
logger.info(f'Response from dynamo_db: ${result}')
return create_response(200)
except Exception as err:
logger.error(err)
return create_response(500, {'message': 'Unknown error occurred!'})
def __num_accounts_under_domain(domain, account_type, conn=None) -> int:
num = 0
if not iredutils.is_domain(domain):
return num
if not conn:
_wrap = SQLWrap()
conn = _wrap.conn
# mapping of account types and sql table names
mapping = {'user': '******', 'alias': 'alias', 'maillist': 'maillists'}
sql_table = mapping[account_type]
try:
qr = conn.select(sql_table,
vars={'domain': domain},
what='COUNT(domain) AS total',
where='domain=$domain')
if qr:
num = qr[0].total
except Exception as e:
logger.error(e)
return num
def __init__(self):
# Initialize LDAP connection.
self.conn = None
uri = settings.iredmail_ldap_uri
# Detect STARTTLS support.
starttls = False
if uri.startswith('ldaps://'):
starttls = True
# Rebuild uri, use ldap:// + STARTTLS (with normal port 389)
# instead of ldaps:// (port 636) for secure connection.
uri = uri.replace('ldaps://', 'ldap://')
# Don't check CA cert
ldap.set_option(ldap.OPT_X_TLS_REQUIRE_CERT, ldap.OPT_X_TLS_NEVER)
self.conn = ldap.initialize(uri)
# Set LDAP protocol version: LDAP v3.
self.conn.set_option(ldap.OPT_PROTOCOL_VERSION, ldap.VERSION3)
if starttls:
self.conn.start_tls_s()
try:
# bind as vmailadmin
self.conn.bind_s(settings.iredmail_ldap_bind_dn, settings.iredmail_ldap_bind_password)
except Exception as e:
logger.error('VMAILADMIN_INVALID_CREDENTIALS. Detail: %s' % repr(e))
def delete_ml(mail, archive=True):
"""Delete a mailing list account. If archive is True or 'yes', account is
'removed' by renaming its data directory.
"""
_ml_dir = __get_ml_dir(mail=mail)
if os.path.exists(_ml_dir):
if archive in [True, 'yes']:
qr = __archive_ml(mail=mail)
return qr
else:
try:
shutil.rmtree(_ml_dir)
logger.info("[{0}] {1}, removed without archiving.".format(
web.ctx.ip, mail))
except Exception as e:
logger.error(
"[{0}] {1}, error while removing list from file system: {2}"
.format(web.ctx.ip, mail, repr(e)))
return (False, repr(e))
else:
logger.info("[{0}] {1}, removed (no data on file system).".format(
web.ctx.ip, mail))
return (True, )
def __init__(self, path):
if os.path.isfile(path) == False:
logger.error("File not exist - [%s]", path)
exit(1)
self.path = path
def push(self, msg):
try:
asynchat.async_chat.push(self, (msg + '\n').encode())
except Exception as e:
logger.error(
"Error while pushing message: msg={}, error={}".format(
msg, repr(e)))
def proxyfunc(self, *args, **kw):
try:
client_ip = web.ctx.ip
except:
# No `ip` attr before starting http service.
return None
if not _is_allowed_client(client_ip):
logger.error(
'[{0}] Blocked request from disallowed client.'.format(
client_ip))
return api_render((False, 'NOT_AUTHORIZED_API_CLIENT'))
_auth_token = get_auth_token()
if not _auth_token:
return api_render((False, 'NO_API_AUTH_TOKEN'))
else:
logger.debug('[{0}] API AUTH TOKEN: {1:.8}...'.format(
client_ip, _auth_token))
if _auth_token not in settings.api_auth_tokens:
logger.error(
'[{0}] Blocked request with invalid auth token: {1}.'.format(
client_ip, _auth_token))
return api_render((False, 'INVALID_MLMMJADMIN_API_AUTH_TOKEN'))
return func(self, *args, **kw)
def __update_text_param(mail,
param,
value,
param_file=None,
create_if_empty=False):
if not param_file:
param_file = __get_param_file(mail=mail, param=param)
if value:
try:
if isinstance(value, int):
value = str(value)
else:
value = value.strip()
value = value.encode('utf-8')
# Footer text/html must ends with an empty line, otherwise
# the characters will be a mess.
with open(param_file, 'w') as f:
f.write(value + '\n')
except Exception, e:
logger.error(
"[{0}] {1}, error while updating (normal) parameter: {2} -> {3}, {4}"
.format(web.ctx.ip, mail, param, value, e))
return (False, repr(e))
def is_email_exists(mail, conn=None):
# Return True if account is invalid or exist.
mail = str(mail).lower()
if not utils.is_email(mail):
return True
if not conn:
_wrap = SQLWrap()
conn = _wrap.conn
try:
# `forwardings` table has email addr of mail user account and alias account.
qr = conn.select('forwardings',
vars={'mail': mail},
what='address',
where='address=$mail',
limit=1)
if qr:
return True
# Check `alias` for alias account which doesn't have any member.
qr = conn.select('alias',
vars={'mail': mail},
what='address',
where='address=$mail',
limit=1)
if qr:
return True
return False
except Exception as e:
logger.error("SQL error: {0}".format(e))
return True
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 is_local_domain(conn,
domain,
include_alias_domain=True,
include_backupmx=True):
if not utils.is_domain(domain):
return False
if utils.is_server_hostname(domain):
return True
try:
_filter = '(&(objectClass=mailDomain)(accountStatus=active)'
if include_alias_domain:
_filter += '(|(domainName=%s)(domainAliasName=%s))' % (domain, domain)
else:
_filter += '(domainName=%s)' % domain
if not include_backupmx:
_filter += '(!(domainBackupMX=yes))'
_filter += ')'
qr = conn.search_s(settings.ldap_basedn,
1, # 1 == ldap.SCOPE_ONELEVEL
_filter,
['dn'])
if qr:
return True
except ldap.NO_SUCH_OBJECT:
return False
except Exception as e:
logger.error("<!> Error while querying local domain: {0}".format(repr(e)))
return False
def get_db_conn(db_name):
"""Return SQL connection instance with connection pool support."""
if settings.backend == 'pgsql':
dbn = 'postgres'
else:
dbn = 'mysql'
if settings.SQL_DB_DRIVER:
dbn += '+' + settings.SQL_DB_DRIVER
try:
uri = '%s://%s:%s@%s:%d/%s' % (
dbn,
settings.__dict__[db_name + '_db_user'],
settings.__dict__[db_name + '_db_password'],
settings.__dict__[db_name + '_db_server'],
int(settings.__dict__[db_name + '_db_port']),
settings.__dict__[db_name + '_db_name'],
)
if settings.backend == 'mysql':
uri += '?charset=utf8'
conn = create_engine(uri,
pool_size=settings.SQL_CONNECTION_POOL_SIZE,
pool_recycle=settings.SQL_CONNECTION_POOL_RECYCLE,
max_overflow=settings.SQL_CONNECTION_MAX_OVERFLOW)
return conn
except Exception as e:
logger.error("Error while creating SQL connection: {}".format(repr(e)))
return None
def __get_list_param_value(mail, param, is_email=False, param_file=None):
if not param_file:
param_file = __get_param_file(mail=mail, param=param)
_values = []
if __has_param_file(param_file):
try:
with open(param_file, 'r') as f:
_lines = f.readlines()
_lines = [_line.strip()
for _line in _lines] # remove line breaks
_values = [_line for _line in _lines
if _line] # remove empty values
if is_email:
_values = [str(i).lower() for i in _values]
except IOError:
# No such file.
pass
except Exception as e:
logger.error(
'Error while getting (list) parameter value: {0} -> {1}'.
format(param, e))
_values.sort()
return _values
def get_alias_target_domain(alias_domain, conn, include_backupmx=True):
"""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 {0} is not an valid domain name.".format(alias_domain))
return None
try:
_filter = '(&(objectClass=mailDomain)(accountStatus=active)'
_filter += '(domainAliasName=%s)' % alias_domain
if not include_backupmx:
_filter += '(!(domainBackupMX=yes))'
_filter += ')'
logger.debug("[LDAP] query target domain of given alias domain: {0}\n"
"[LDAP] query filter: {1}".format(alias_domain, _filter))
qr = conn.search_s(settings.ldap_basedn,
1, # 1 == ldap.SCOPE_ONELEVEL
_filter,
['domainName'])
logger.debug("result: {0}".format(repr(qr)))
if qr:
(_dn, _ldif) = qr[0]
_domain = _ldif['domainName'][0]
return _domain
except ldap.NO_SUCH_OBJECT:
pass
except Exception as e:
logger.error("<!> Error while querying alias domain: {0}".format(repr(e)))
return None
def lambda_handler(event, _) -> object:
try:
logger.info(f'Received an event: ${event}')
query_params = event['queryStringParameters']
validate_query_params(query_params)
user_id = query_params['userId']
word = query_params['word']
logger.info(f'Deleting word: ${word} from user ${user_id} dictionary')
table = dynamo_db.Table(os.environ['DYNAMODB_TABLE'])
result = table.delete_item(
Key={
'userId': user_id,
'word': word,
},
ReturnValues='ALL_OLD'
)
logger.info(f'Response from dynamo_db: ${result}')
attributes = 'Attributes'
if attributes not in result:
return create_response(404)
logger.info(f'Successfully deleted item: ${attributes}')
return create_response(200)
except ValueError:
return create_response(400, {'message': 'Missing required parameters: userId, word'})
except Exception as err:
logger.error(err)
return create_response(500, {'message': 'Unknown error occurred!'})
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 push(self, msg):
try:
asynchat.async_chat.push(self, (msg + '\n').encode())
except Exception as e:
logger.error(
"Error while pushing message: error={0}, message={1}".format(
repr(e), msg))
def add_maillist(mail, form, conn=None):
"""Add required SQL records to add a mailing list account."""
mail = str(mail).lower()
(listname, domain) = mail.split('@', 1)
if not utils.is_email(mail):
return (False, 'INVALID_EMAIL')
if not conn:
_wrap = SQLWrap()
conn = _wrap.conn
if not is_domain_exists(domain=domain):
return (False, 'NO_SUCH_DOMAIN')
if is_email_exists(mail=mail):
return (False, 'ALREADY_EXISTS')
params = {
'active': 1,
'address': mail,
'domain': domain,
'name': form.get('name', ''),
'transport': '%s:%s/%s' % (settings.MTA_TRANSPORT_NAME, domain, listname),
'mlid': __get_new_mlid(conn=conn),
'maxmsgsize': form_utils.get_max_message_size(form),
}
if 'only_moderator_can_post' in form:
params['accesspolicy'] = 'moderatorsonly'
elif 'only_subscriber_can_post' in form:
params['accesspolicy'] = 'membersonly'
try:
conn.insert('maillists', **params)
params = {
'active': 1,
'address': mail,
'domain': domain,
'forwarding': mail,
'dest_domain': domain,
}
conn.insert('forwardings', **params)
# Get moderators, store in SQL table `vmail.moderators`
if 'moderators' in form:
qr = __reset_moderators(mail=mail, form=form, conn=conn)
if 'owner' in form:
qr = __reset_owners(mail=mail, form=form, conn=conn)
if not qr[0]:
return qr
logger.info('Created: {0}.'.format(mail))
return (True,)
except Exception as e:
logger.error('Error while creating {0}: {1}'.format(mail, e))
return (False, repr(e))
def is_maillist_exists(mail, conn=None):
"""Return True if mailing list account is invalid or exist."""
mail = str(mail).lower()
if not utils.is_email(mail):
return True
if not conn:
_wrap = SQLWrap()
conn = _wrap.conn
try:
# Check `maillists`
qr = conn.select('maillists',
vars={'mail': mail},
what='address',
where='address=$mail',
limit=1)
if qr:
return True
return False
except Exception as e:
logger.error("SQL error: {0}".format(e))
return True
def __update_list_param(mail, param, value, param_file=None, is_email=False):
if not param_file:
param_file = __get_param_file(mail=mail, param=param)
if isinstance(value, (str, unicode)):
_values = __convert_web_param_value_to_list(value=value,
is_email=is_email)
else:
_values = value
if _values:
try:
param_file = __get_param_file(mail=mail, param=param)
if param == 'listaddress':
# Remove primary address(es)
_values = [v for v in _values if v != mail]
# Prepend primary address (must be first one)
_values = [mail] + _values
with open(param_file, 'w') as f:
f.write('\n'.join(_values) + '\n')
logger.info("[{0}] {1}, updated: {2} -> {3}".format(
web.ctx.ip, mail, param, ', '.join(_values)))
except Exception, e:
logger.error(
"[{0}] {1}, error while updating (list) parameter: {2} -> {3}, {4}"
.format(web.ctx.ip, mail, param, value, e))
return (False, repr(e))
def __update_boolean_param(mail,
param,
value,
param_file=None,
touch_instead_of_create=False):
"""Create or remove parameter file for boolean type parameter.
@touch_instead_of_create - touch parameter file instead of re-create it.
"""
if not param_file:
param_file = __get_param_file(mail=mail, param=param)
if value == 'yes':
try:
if touch_instead_of_create:
open(param_file, 'a').close()
else:
open(param_file, 'w').close()
# Avoid some conflicts
if param == 'subonlypost':
__remove_param_file(mail=mail, param='modonlypost')
if param == 'modonlypost':
__remove_param_file(mail=mail, param='subonlypost')
# Create 'control/moderated' also
_f = __get_param_file(mail=mail, param='moderated')
open(_f, 'a').close()
except Exception, e:
logger.error(
"[{0}] {1}, error while updating (boolean) parameter: {2} -> {3}, {4}"
.format(web.ctx.ip, mail, param, value, e))
return (False, repr(e))
def remove_subscribers(mail, subscribers, conn=None):
"""Remove subscribers from mailing list."""
mail = str(mail).lower()
if not utils.is_email(mail):
return (False, 'INVALID_EMAIL')
if not subscribers:
return (False, 'NO_SUBSCRIBERS')
if not isinstance(subscribers, (list, tuple, set)):
return (False, 'NO_SUBSCRIBERS')
if not conn:
_wrap = SQLWrap()
conn = _wrap.conn
try:
conn.delete('maillist_members',
vars={'mail': mail, 'subscribers': subscribers},
where='address=$mail AND member IN $subscribers')
return (True,)
except Exception as e:
logger.error("SQL error: {0}".format(e))
return (False, repr(e))
def __sendmail(conn,
user,
client_address,
throttle_tracking_id,
throttle_name,
throttle_value,
throttle_kind,
throttle_info,
throttle_value_unit=None):
"""Construct and send notification email."""
# conn: SQL connection cursor
# user: user email address
# client_address: client IP address
# throttle_tracking_id: value of sql column `throttle_tracking.id`
# throttle_name: name of throttle settings: msg_size, max_quota, max_msgs
# throttle_value: value throttle setting
# throttle_kind: one of throttle kinds: inbound, outbound
# throttle_info: detailed throttle setting
# throttle_value_unit: unit of throttle setting. e.g 'bytes' for max_quota
# and msg_size.
if not throttle_value_unit:
throttle_value_unit = ''
try:
_subject = 'Throttle quota exceeded: %s, %s=%d %s' % (
user, throttle_name, throttle_value, throttle_value_unit)
_body = '- User: '******'\n'
_body += '- Client IP address: ' + client_address + '\n'
_body += '- Throttle type: ' + throttle_kind + '\n'
_body += '- Throttle setting: ' + throttle_name + '\n'
_body += '- Limit: %d %s\n' % (throttle_value, throttle_value_unit)
_body += '- Detailed setting: ' + throttle_info + '\n'
utils.sendmail(subject=_subject, mail_body=_body)
logger.info(
'Sent notification email to admin(s) to report quota exceed: user=%s, %s=%d.'
% (user, throttle_name, throttle_value))
if throttle_tracking_id:
_now = int(time.time())
# Update last_notify_time.
_sql = """UPDATE throttle_tracking
SET last_notify_time=%d
WHERE id=%d;
""" % (_now, throttle_tracking_id)
try:
conn.execute(_sql)
logger.debug('Updated last notify time.')
except Exception as e:
logger.error(
'Error while updating last notify time of quota exceed: %s.'
% (repr(e)))
return (True, )
except Exception as e:
logger.error('Error while sending notification email: %s' % repr(e))
return (False, repr(e))
def __add_subscribers_with_confirm(mail, subscribers, subscription='normal'):
"""
Add subscribers with confirm.
@mail -- mail address of mailing list
@subscribers -- a list/tuple/set of subscribers' mail addresses
@subscription -- subscription version (normal, digest, nomail)
"""
_dir = __get_ml_dir(mail)
# Get absolute path of command `mlmmj-sub`
_cmd_mlmmj_sub = settings.CMD_MLMMJ_SUB
if not _cmd_mlmmj_sub:
if os.path.exists('/usr/bin/mlmmj-sub'):
_cmd_mlmmj_sub = '/usr/bin/mlmmj-sub'
elif os.path.exists('/usr/local/bin/mlmmj-sub'):
_cmd_mlmmj_sub = '/usr/local/bin/mlmmj-sub'
else:
return (False, 'SUB_COMMAND_NOT_FOUND')
# mlmmj-sub arguments
#
# -L: Full path to list directory
# -a: Email address to subscribe
# -C: Request mail confirmation
# -d: Subscribe to `digest` version of the list
# -n: Subscribe to nomail version of the list
_cmd = [_cmd_mlmmj_sub, '-L', _dir, '-C']
if subscription == 'digest':
_cmd.append('-d')
elif subscription == 'nomail':
_cmd.append('-n')
# Directory used to store subscription confirm notifications
_subconf_dir = os.path.join(_dir, 'subconf')
_error = {}
for addr in subscribers:
try:
# Remove confirm file generated before this request
_old_conf_files = glob.glob(
os.path.join(_subconf_dir,
'????????????????-' + addr.replace('@', '=')))
for _f in _old_conf_files:
qr = __remove_file(path=_f)
if not qr[0]:
return qr
# Send new confirm
_new_cmd = _cmd[:] + ['-a', addr]
subprocess.Popen(_new_cmd, stdout=subprocess.PIPE)
logger.debug("[{0}] {1}, queued confirm mail for {2}.".format(
web.ctx.ip, mail, addr))
except Exception, e:
logger.error("[{0}] {1}, error while subscribing {2}: {3}".format(
web.ctx.ip, mail, addr, e))
_error[addr] = repr(e)
def __init__(self):
try:
self.conn = self.connect()
except AttributeError:
# Reconnect if error raised: MySQL server has gone away.
self.conn = self.connect()
except Exception as e:
logger.error(e)
def __init__(self):
try:
self.conn = self.__connect()
except AttributeError: # should also catch `<db>.OperationalError`
# Reconnect if error raised: MySQL server has gone away.
self.conn = self.__connect()
except Exception as e:
logger.error("SQL error: {0}".format(e))
def __remove_file(path):
if os.path.exists(path):
try:
os.remove(path)
except Exception, e:
logger.error(
"[{0}] error while removing parameter file: {1}, {2}".format(
web.ctx.ip, path, e))
return (False, repr(e))
def __init__(self):
import MySQLdb
try:
self.conn = self.__connect()
except (AttributeError, MySQLdb.OperationalError):
# Reconnect if error raised: MySQL server has gone away.
self.conn = self.__connect()
except Exception, e:
logger.error("SQL error: {0}".format(e))
