GitHub/iRedAdmin-Pro-SQL
1
0
Fork 0
mirror of https://github.com/marcus-alicia/iRedAdmin-Pro-SQL.git synced 2026-05-26 07:08:10 +00:00
Code Issues Packages Projects Releases Wiki Activity
Files
main
iRedAdmin-Pro-SQL/libs/sqllib/user.py
Red Queen ffdbde06bc Update to V5.5
2023-10-31 11:00:41 +01:00

2630 lines
92 KiB
Python
Raw Permalink Blame History

# Author: Zhang Huangbin <zhb@iredmail.org>
import os
import web
import settings
from controllers.utils import api_render
from libs import iredutils, iredpwd, form_utils
from libs.l10n import TIMEZONES
from libs.logger import logger, log_activity
from libs.sqllib import SQLWrap, decorators, sqlutils
from libs.sqllib import general as sql_lib_general
from libs.sqllib import admin as sql_lib_admin
from libs.sqllib import domain as sql_lib_domain
from libs.sqllib import api_utils
from libs import mlmmj
from libs.amavisd import spampolicy as spampolicylib
from libs.amavisd import get_wblist_from_form, wblist as lib_wblist
session = web.config.get('_session', {})
if settings.amavisd_enable_policy_lookup:
from libs.amavisd.utils import delete_policy_accounts
if settings.iredapd_enabled:
from libs.iredapd import throttle as iredapd_throttle
from libs.iredapd import greylist as iredapd_greylist
from libs.iredapd import utils as iredapd_utils
ENABLED_SERVICES = [
'enablesmtp', 'enablesmtpsecured',
'enablepop3', 'enablepop3secured',
'enableimap', 'enableimapsecured',
'enablesogo',
'enablesogowebmail', 'enablesogocalendar', 'enablesogoactivesync',
'enablemanagesieve', 'enablemanagesievesecured',
'enablesieve', 'enablesievesecured',
'enabledeliver',
]
def user_is_global_admin(conn, mail, user_profile=None):
try:
if user_profile:
if user_profile.get('isglobaladmin', 0) == 1:
return True
else:
if not conn:
_wrap = SQLWrap()
conn = _wrap.conn
qr = conn.select('mailbox',
vars={'username': mail},
what='isglobaladmin',
where='username=$username AND isglobaladmin=1',
limit=1)
if qr:
return True
except:
pass
return False
def redirect_if_user_is_global_admin(conn, mail, user_profile=None, url=None):
domain = mail.split('@', 1)[-1].lower()
if user_is_global_admin(conn=conn, mail=mail, user_profile=user_profile):
if web.ctx.homepath.startswith("/api/"):
return api_render((False, 'PERMISSION_DENIED_UPDATE_GLOBAL_ADMIN_PROFILE'))
else:
if not url:
url = '/users/%s?msg=PERMISSION_DENIED_UPDATE_GLOBAL_ADMIN_PROFILE' % domain
raise web.seeother(url)
else:
return None
@decorators.require_domain_access
def change_email(mail, new_mail, conn=None):
if not iredutils.is_email(mail):
return False, 'INVALID_OLD_EMAIL'
if not iredutils.is_email(new_mail):
return False, 'INVALID_NEW_EMAIL'
old_domain = mail.split('@', 1)[-1]
new_domain = new_mail.split('@', 1)[-1]
if old_domain != new_domain:
return False, 'PERMISSION_DENIED'
if not conn:
_wrap = SQLWrap()
conn = _wrap.conn
if not sql_lib_general.is_email_exists(mail=mail, conn=conn):
return False, 'OLD_EMAIL_NOT_EXIST'
if sql_lib_general.is_email_exists(mail=new_mail, conn=conn):
return False, 'NEW_EMAIL_ALREADY_EXISTS'
# Change email address
try:
sql_vars = {'mail': mail, 'new_mail': new_mail}
conn.update('mailbox',
vars=sql_vars,
username=new_mail,
where='username=$mail')
# Replace old address by the new one in
# - `forwardings.address`
# - `forwardings.forwarding`
conn.update('forwardings',
vars=sql_vars,
address=new_mail,
where='address=$mail')
conn.update('forwardings',
vars=sql_vars,
forwarding=new_mail,
where='forwarding=$mail')
# Update moderators
_qr = conn.select("moderators",
vars=sql_vars,
what="address",
where="moderator=$mail")
if _qr:
conn.update('moderators',
vars=sql_vars,
moderator=new_mail,
where='moderator=$mail')
# Update mlmmj.
_mls = []
for row in _qr:
_addr = row["address"].lower()
_mls.append(_addr)
if _mls:
# Exclude mail alias accounts.
_qr = conn.select("alias",
vars={"mails": _mls},
what="address",
where="address IN $mails")
if _qr:
for row in _qr:
_alias_addr = row["address"].lower()
_mls.remove(_alias_addr)
for _addr in _mls:
# Get existing moderators from mlmmj mailing list profile.
_qr = mlmmj.get_account_profile(mail=_addr, with_subscribers=False)
if _qr[0]:
_profile = _qr[1]
_owners = _profile.get("moderators", [])
# Replace old address by the new one.
_owners = [i for i in _owners if i != mail]
_owners.append(new_mail)
# Reset owners.
_qr2 = mlmmj.update_account_profile(mail=_addr, data={"moderators": ",".join(_owners)})
if not _qr2[0]:
logger.error("Failed to reset moderators of mailing list ({}) "
"while changing user email address: "
"{}".format(_addr, repr(_qr2[1])))
return False, _qr2[1]
# Update mailing list owners.
_qr = conn.select("maillist_owners",
vars=sql_vars,
what="address",
where="owner=$mail")
if _qr:
conn.update('maillist_owners',
vars=sql_vars,
owner=new_mail,
where='owner=$mail')
# Update mlmmj.
for row in _qr:
_addr = row["address"].lower()
# Get existing owners from mlmmj mailing list profile.
_qr2 = mlmmj.get_account_profile(mail=_addr, with_subscribers=False)
if _qr2[0]:
_profile = _qr2[1]
_owners = _profile.get("owner", [])
# Replace old address by the new one.
_owners = [i for i in _owners if i != mail]
_owners.append(new_mail)
# Reset owners.
_qr3 = mlmmj.update_account_profile(mail=_addr, data={"owner": ",".join(_owners)})
if not _qr3[0]:
logger.error("Failed to reset owners of mailing list ({}) "
"while changing user email address: "
"{}".format(_addr, repr(_qr3[1])))
return False, _qr3[1]
log_activity(event='update',
domain=old_domain,
msg="Change user email address: {} -> {}.".format(mail, new_mail))
return True,
except Exception as e:
return False, repr(e)
def delete_users(accounts,
keep_mailbox_days=0,
conn=None):
accounts = [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')
# Remove users from subscribed mlmmj mailing lists
for _mail in accounts:
_qr = mlmmj.remove_subscriber_from_all_subscribed_lists(subscriber=_mail)
if not _qr[0]:
return _qr
except Exception as e:
return False, repr(e)
# Delete records in Amavisd database: users, policy
if settings.amavisd_enable_policy_lookup:
delete_policy_accounts(accounts=accounts)
# Delete data in iRedAPD database
if settings.iredapd_enabled:
iredapd_utils.delete_settings_for_removed_users(mails=accounts)
log_activity(event='delete',
domain=accounts[0].split('@', 1)[-1],
msg="Delete user: %s." % ', '.join(accounts))
return True,
@decorators.require_domain_access
def simple_profile(mail, columns=None, conn=None):
"""Return value of sql column `mailbox.settings`.
@columns -- a list or tuple which contains SQL column names
"""
if not conn:
_wrap = SQLWrap()
conn = _wrap.conn
sql_what = '*'
if columns:
sql_what = ','.join(columns)
try:
qr = conn.select('mailbox',
vars={'username': mail},
what=sql_what,
where='username=$username',
limit=1)
if qr:
return True, list(qr)[0]
else:
return False, 'NO_SUCH_ACCOUNT'
except Exception as e:
return False, repr(e)
def promote_users_to_be_global_admin(mails, promote=True, conn=None):
mails = [str(i).lower() for i in mails if iredutils.is_email(i)]
if not mails:
return True,
if not conn:
_wrap = SQLWrap()
conn = _wrap.conn
try:
sql_vars = {'mails': mails, 'domain': 'ALL'}
conn.delete('domain_admins',
vars=sql_vars,
where="username IN $mails AND domain=$domain")
if promote:
v = []
for i in mails:
v += [{'username': i, 'domain': 'ALL'}]
conn.multiple_insert('domain_admins', v)
# Update `vmail.mailbox`
conn.update('mailbox',
vars=sql_vars,
isglobaladmin=1,
where="username IN $mails")
else:
# Update `vmail.mailbox`
conn.update('mailbox',
vars=sql_vars,
isglobaladmin=0,
where="username IN $mails")
return True,
except Exception as e:
logger.error(e)
return False, repr(e)
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
@decorators.require_domain_access
def get_paged_users(conn,
domain,
cur_page=1,
admin_only=False,
order_name=None,
order_by_desc=None,
first_char=None,
disabled_only=False):
domain = str(domain).lower()
cur_page = int(cur_page) or 1
sql_vars = {'domain': domain}
sql_where = 'mailbox.domain=%s' % web.sqlquote(domain)
if admin_only:
sql_where += ' AND (mailbox.isadmin=1 OR mailbox.isglobaladmin=1)'
if first_char:
sql_where += ' AND mailbox.username LIKE %s' % web.sqlquote(first_char.lower() + '%')
if disabled_only:
sql_where += ' AND mailbox.active=0'
try:
if order_name == 'quota':
if settings.backend == 'mysql':
sql_cmd_percentage = '100 * IFNULL(%s.bytes, 0)/(mailbox.quota * 1024 * 1024) AS percentage' % settings.SQL_TBL_USED_QUOTA
else:
# ATTENTION:
# - 'COALESCE(X, 0) as percentage': set percentage of unlimited mailbox to 0
# - 'NULLIF()': set `mailbox.quota` of unlimited mailbox to null,
# this way we can avoid PostgreSQL error: `division by zero`
sql_cmd_percentage = 'COALESCE((100 * COALESCE(%s.bytes, 0)/(NULLIF(mailbox.quota, 0) * 1024 * 1024)), 0) as percentage' % settings.SQL_TBL_USED_QUOTA
if order_by_desc:
_order_by = 'DESC'
else:
_order_by = 'ASC'
qr = conn.query("""
SELECT
LOWER(mailbox.username) AS username, mailbox.name, mailbox.quota,
mailbox.employeeid, mailbox.active, mailbox.isadmin,
mailbox.isglobaladmin, mailbox.passwordlastchange,
%s
FROM mailbox
LEFT JOIN %s ON (%s.username = mailbox.username)
WHERE %s
ORDER BY percentage %s, mailbox.username ASC
LIMIT %d
OFFSET %d
""" % (sql_cmd_percentage,
settings.SQL_TBL_USED_QUOTA, settings.SQL_TBL_USED_QUOTA,
sql_where,
_order_by,
settings.PAGE_SIZE_LIMIT,
(cur_page - 1) * settings.PAGE_SIZE_LIMIT))
elif order_name == 'name':
sql_order = 'name ASC, username ASC'
if order_by_desc:
sql_order = 'name DESC, username ASC'
qr = conn.select(
'mailbox',
vars=sql_vars,
# Just query what we need to reduce memory use.
what='LOWER(username) AS username,name,quota,employeeid,active,isadmin,isglobaladmin,passwordlastchange',
where=sql_where,
order=sql_order,
limit=settings.PAGE_SIZE_LIMIT,
offset=(cur_page - 1) * settings.PAGE_SIZE_LIMIT,
)
else:
qr = conn.select(
'mailbox',
vars=sql_vars,
# Just query what we need to reduce memory use.
what='LOWER(username) AS username,name,quota,employeeid,active,isadmin,isglobaladmin,passwordlastchange',
where=sql_where,
order='username ASC',
limit=settings.PAGE_SIZE_LIMIT,
offset=(cur_page - 1) * settings.PAGE_SIZE_LIMIT)
return True, list(qr)
except Exception as e:
return False, repr(e)
def mark_user_as_admin(conn,
domain,
users,
as_normal_admin=None,
as_global_admin=None):
"""Mark normal mail user accounts as domain admin.
@domain -- specified users will be admin of this domain.
@users -- iterable object which contains list of email addresses.
@as_normal_admin -- True to enable, False to disable. None for no change.
@as_global_admin -- True to enable, False to disable. None for no change.
"""
sql_vars = {'users': users}
sql_updates = {}
if as_normal_admin is True:
sql_updates['isadmin'] = 1
elif as_normal_admin is False:
sql_updates['isadmin'] = 0
if session.get('is_global_admin'):
if as_global_admin is True:
sql_updates['isglobaladmin'] = 1
elif as_global_admin is False:
sql_updates['isglobaladmin'] = 0
if not sql_updates:
return True,
try:
# update `mailbox.isadmin`, `mailbox.isglobaladmin`.
conn.update('mailbox',
vars=sql_vars,
where='username IN $users',
**sql_updates)
if as_normal_admin is True:
# Add records in `domain_admins` to identify admin privilege.
for u in users:
try:
conn.insert('domain_admins',
username=u,
domain=domain)
except:
pass
elif as_normal_admin is False:
# Remove admin privilege.
try:
conn.delete('domain_admins',
vars={'users': users},
where="username IN $users AND domain <> 'ALL'")
except:
pass
if as_global_admin is True:
promote_users_to_be_global_admin(mails=users, promote=True, conn=conn)
elif as_global_admin is False:
promote_users_to_be_global_admin(mails=users, promote=False, conn=conn)
return True,
except Exception as e:
return False, repr(e)
def profile(mail,
with_aliases=False,
with_alias_groups=True,
with_mailing_lists=True,
with_forwardings=True,
with_used_quota=True,
with_last_login=True,
conn=None):
"""Get full user profile.
@with_alias -- get per-user alias addresses.
@with_forwardings -- get mail forwarding addresses
"""
mail = str(mail).lower()
try:
if not conn:
_wrap = SQLWrap()
conn = _wrap.conn
qr = conn.query(
'''
SELECT
mailbox.*,
sbcc.bcc_address AS sender_bcc_address,
sbcc.active AS sbcc_active,
rbcc.bcc_address AS recipient_bcc_address,
rbcc.active AS rbcc_active
FROM mailbox
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 = $username
LIMIT 1
''',
vars={'username': mail})
if qr:
p = qr[0]
p['aliases'] = []
p['mailing_aliases'] = []
p['mailing_lists'] = []
p['forwardings'] = []
p['stored_bytes'] = 0
p['stored_messages'] = 0
if with_aliases:
(_status, _result) = get_user_alias_addresses(mail=mail, conn=conn)
if _status:
p['aliases'] = _result
if with_alias_groups:
(_status, _result) = get_assigned_aliases(mail=mail, conn=conn)
if _status:
p['mailing_aliases'] = _result
if with_mailing_lists:
(_status, _result) = mlmmj.get_subscribed_lists(mail=mail,
query_all_lists=False,
email_only=True)
if _status:
p['mailing_lists'] = _result
if with_forwardings:
(_status, _result) = get_user_forwardings(mail=mail, conn=conn)
if _status:
_fwds = _result
_fwds.sort()
# Remove self if only self exists (no forwarding actually)
if _fwds == [mail]:
_fwds = []
p['forwardings'] = _fwds
if with_used_quota:
_used_quota = sql_lib_general.get_account_used_quota(accounts=[mail], conn=conn)
if mail in _used_quota:
p['stored_bytes'] = _used_quota[mail]['bytes']
p['stored_messages'] = _used_quota[mail]['messages']
p['last_login'] = ''
if with_last_login:
_last_login = sql_lib_general.get_account_last_login(accounts=[mail], conn=conn)
if mail in _last_login:
_login_epoch_seconds = _last_login[mail]
_login_date = iredutils.epoch_seconds_to_gmt(seconds=_login_epoch_seconds)
p['last_login'] = _login_date
return True, p
else:
return False, 'NO_SUCH_ACCOUNT'
except Exception as e:
return False, repr(e)
def add_user_from_form(domain, form, conn=None):
# Get domain name, username, cn.
mail_domain = form_utils.get_domain_name(form)
mail_username = form.get('username')
if mail_username:
mail_username = web.safestr(mail_username).strip().lower()
else:
return False, 'INVALID_ACCOUNT'
mail = mail_username + '@' + mail_domain
if mail_domain != domain:
return False, 'PERMISSION_DENIED'
if not iredutils.is_auth_email(mail):
return False, 'INVALID_MAIL'
if not conn:
_wrap = SQLWrap()
conn = _wrap.conn
# Check account existing.
if sql_lib_general.is_email_exists(mail=mail, conn=conn):
return False, 'ALREADY_EXISTS'
# Get domain profile.
qr = sql_lib_domain.profile(conn=conn, domain=domain)
if qr[0]:
domain_profile = qr[1]
domain_settings = sqlutils.account_settings_string_to_dict(domain_profile['settings'])
else:
return qr
# Check account limit.
num_exist_accounts = sql_lib_admin.num_managed_users(conn=conn, domains=[domain])
if domain_profile.mailboxes == -1:
return False, 'NOT_ALLOWED'
elif domain_profile.mailboxes > 0:
if domain_profile.mailboxes <= num_exist_accounts:
return False, 'EXCEEDED_DOMAIN_ACCOUNT_LIMIT'
# Check spare quota and number of spare account limit.
# Get quota from <form>
quota = str(form.get('mailQuota')).strip()
qr = sql_lib_domain.assign_given_mailbox_quota(domain=domain, quota=quota)
if not qr[0]:
return qr
quota = qr[1]
#
# Get password from <form>.
#
pw_hash = form.get('password_hash', '')
newpw = web.safestr(form.get('newpw', ''))
confirmpw = web.safestr(form.get('confirmpw', ''))
if pw_hash:
if not iredpwd.is_supported_password_scheme(pw_hash):
return False, 'INVALID_PASSWORD_SCHEME'
passwd = pw_hash
else:
# Get password length limit from domain profile or global setting.
min_passwd_length = domain_settings.get('min_passwd_length', 0)
max_passwd_length = domain_settings.get('max_passwd_length', 0)
qr_pw = iredpwd.verify_new_password(newpw,
confirmpw,
min_passwd_length=min_passwd_length,
max_passwd_length=max_passwd_length)
if qr_pw[0]:
pwscheme = None
if 'store_password_in_plain_text' in form and settings.STORE_PASSWORD_IN_PLAIN_TEXT:
pwscheme = 'PLAIN'
passwd = iredpwd.generate_password_hash(qr_pw[1], pwscheme=pwscheme)
else:
return qr_pw
# Get display name from <form>
cn = form_utils.get_single_value(form, input_name='cn', default_value='')
# Get preferred language.
preferred_language = form_utils.get_language(form)
if preferred_language not in iredutils.get_language_maps():
preferred_language = ''
# Assign new user to default mail aliases.
assigned_aliases = [str(v).lower()
for v in domain_settings.get('default_groups', [])
if iredutils.is_email(v)]
# Assign new user to default mailing lists.
default_mailing_lists = [str(v).lower()
for v in domain_settings.get('default_mailing_lists', [])
if iredutils.is_email(v)]
_qr = sql_lib_general.filter_existing_mailing_lists(mails=default_mailing_lists, conn=conn)
default_mailing_lists = _qr['exist']
# Get storage base directory.
_storage_base_directory = settings.storage_base_directory
splited_sbd = _storage_base_directory.rstrip('/').split('/')
storage_node = splited_sbd.pop()
storage_base_directory = '/'.join(splited_sbd)
maildir = iredutils.generate_maildir_path(mail)
# Read full maildir path from web form - from RESTful API.
mailbox_maildir = form.get('maildir', '').lower().rstrip('/')
if mailbox_maildir and os.path.isabs(mailbox_maildir):
# Split storageBaseDirectory and storageNode
_splited = mailbox_maildir.rstrip('/').split('/')
storage_base_directory = '/' + _splited[0]
storage_node = _splited[1]
maildir = '/'.join(_splited[2:])
record = {
'domain': domain,
'username': mail,
'password': passwd,
'name': cn,
'quota': quota,
'storagebasedirectory': storage_base_directory,
'storagenode': storage_node,
'maildir': maildir,
'language': preferred_language,
'disclaimer': '',
'created': iredutils.get_gmttime(),
'active': 1,
}
if settings.SET_PASSWORD_CHANGE_DATE_FOR_NEW_USER:
record['passwordlastchange'] = iredutils.get_gmttime()
# Get settings from SQL db.
db_settings = iredutils.get_settings_from_db()
# Get mailbox format and folder.
_mailbox_format = form.get('mailboxFormat', db_settings['mailbox_format']).lower()
_mailbox_folder = form.get('mailboxFolder', db_settings['mailbox_folder'])
if iredutils.is_valid_mailbox_format(_mailbox_format):
record['mailboxformat'] = _mailbox_format
if iredutils.is_valid_mailbox_folder(_mailbox_folder):
record['mailboxfolder'] = _mailbox_folder
# Always store plain password in another attribute.
if settings.STORE_PLAIN_PASSWORD_IN_ADDITIONAL_ATTR:
record[settings.STORE_PLAIN_PASSWORD_IN_ADDITIONAL_ATTR] = newpw
# Set disabled mail services.
disabled_mail_services = domain_settings.get('disabled_mail_services', [])
for srv in disabled_mail_services:
record['enable' + srv] = 0
# globally disabled mail services
for srv in settings.ADDITIONAL_DISABLED_USER_SERVICES:
record['enable' + srv] = 0
# globally enabled mail services
for srv in settings.ADDITIONAL_ENABLED_USER_SERVICES:
record['enable' + srv] = 1
try:
# Store new user in SQL db.
conn.insert('mailbox', **record)
# Assign new user to default mail aliases.
if assigned_aliases:
for ali in assigned_aliases:
try:
conn.insert('forwardings',
address=ali,
forwarding=mail,
domain=ali.split('@', 1)[-1],
dest_domain=domain,
is_list=1)
except:
pass
# Assign new user to default mailing lists.
if default_mailing_lists:
for ml in default_mailing_lists:
_qr = mlmmj.add_subscribers(mail=ml,
subscribers=[mail],
subscription='normal',
require_confirm=False)
if not _qr[0]:
return _qr
# Create an entry in `vmail.forwardings` with `address=forwarding`
conn.insert('forwardings',
address=mail,
forwarding=mail,
domain=domain,
dest_domain=domain,
is_forwarding=1,
active=1)
# Create bcc
for (addr, sql_table) in [(domain_settings.get('default_recipient_bcc'), 'recipient_bcc_user'),
(domain_settings.get('default_sender_bcc'), 'sender_bcc_user')]:
if iredutils.is_email(addr):
conn.insert(sql_table,
username=mail,
bcc_address=addr,
domain=domain,
created=iredutils.get_gmttime(),
active=1)
log_activity(msg="Create user: %s." % mail,
domain=domain,
event='create')
return True,
except Exception as e:
return False, repr(e)
def reset_forwardings(mail, forwardings=None, conn=None):
"""Reset per-user mail forwarding addresses.
If `forwardings` is empty of None, all existing forwardings will be removed.
"""
domain = mail.split('@', 1)[-1]
if isinstance(forwardings, (list, tuple, set)):
_forwardings = {str(i).lower() for i in forwardings if iredutils.is_email(i)}
else:
_forwardings = []
if not conn:
_wrap = SQLWrap()
conn = _wrap.conn
# Remove all forwardings first
try:
conn.delete('forwardings',
vars={'mail': mail},
where='address=$mail')
except Exception as e:
return False, repr(e)
# Return if no need to reset
if _forwardings:
# Remove non-existing internal addresses.
addrs_in_domain = list({v for v in _forwardings if v.endswith('@' + domain)})
addrs_not_in_domain = list({v for v in _forwardings if not v.endswith('@' + domain)})
# Verify addresses in same domain
if addrs_in_domain:
qr = sql_lib_general.filter_existing_emails(mails=addrs_in_domain, conn=conn)
addrs_in_domain = qr['exist']
_forwardings = addrs_in_domain + addrs_not_in_domain
else:
_forwardings = [mail]
if sql_lib_general.is_active_user(mail=mail, conn=conn):
active = 1
else:
active = 0
try:
v = []
for _addr in _forwardings:
v += [{'address': mail,
'forwarding': _addr,
'domain': domain,
'dest_domain': _addr.split('@', 1)[-1],
'is_forwarding': 1,
'active': active}]
conn.multiple_insert('forwardings', values=v)
return True,
except Exception as e:
return False, repr(e)
def __reset_assigned_aliases(mail, groups=None, conn=None):
"""
Reset assigned mail alias groups. if @groups is empty of None, all
assigned groups will be removed.
"""
domain = mail.split('@', 1)[-1]
_addresses = {str(i).lower()
for i in groups
if iredutils.is_email(i)}
if not conn:
_wrap = SQLWrap()
conn = _wrap.conn
# Remove all existing aliases.
try:
# NOTE: Use `domain=`, NOT `dest_domain=`.
conn.delete('forwardings',
vars={'mail': mail},
where='forwarding=$mail AND is_list=1')
except Exception as e:
return False, repr(e)
# Return if no need to reset
if not _addresses:
return True,
# Get existing mail alias accounts
qr = sql_lib_general.filter_existing_aliases(mails=_addresses, conn=conn)
_existings = qr['exist']
# Remove existing addresses, use non-existing addresses
if not _existings:
return True,
v = []
for i in _existings:
v += [{'address': i,
'forwarding': mail,
'domain': domain,
'dest_domain': mail.split('@', 1)[-1],
'is_list': 1,
'active': 1}]
try:
conn.multiple_insert('forwardings', values=v)
return True,
except Exception as e:
return False, repr(e)
def update(conn, mail, profile_type, form):
profile_type = web.safestr(profile_type)
mail = str(mail).lower()
domain = mail.split('@', 1)[-1]
# Normal admin is not allowed to update global admin's profile
# Get user profile.
if not session.get('is_global_admin'):
redirect_if_user_is_global_admin(conn=conn, mail=mail)
# change email address
if profile_type == 'rename':
# new email address
new_mail = web.safestr(form.get('new_mail_username')).strip().lower() + '@' + domain
qr = change_email(mail=mail, new_mail=new_mail, conn=conn)
if qr[0]:
raise web.seeother('/profile/user/general/%s?msg=EMAIL_CHANGED' % new_mail)
else:
raise web.seeother('/profile/user/general/{}?msg={}'.format(new_mail, web.urlquote(qr[1])))
qr = sql_lib_domain.simple_profile(conn=conn,
domain=domain,
columns=['maxquota', 'settings'])
if not qr[0]:
return qr
domain_profile = qr[1]
del qr
domain_settings = sqlutils.account_settings_string_to_dict(domain_profile.get('settings', ''))
disabled_user_profiles = domain_settings.get('disabled_user_profiles', [])
if not session.get('is_global_admin'):
if profile_type in disabled_user_profiles:
return False, 'PERMISSION_DENIED'
# Pre-defined update key:value pairs
updates = {'modified': iredutils.get_gmttime()}
discarded_aliases = []
if profile_type == 'general':
managed_domains = []
if session.get('is_global_admin') or session.get('allowed_to_grant_admin'):
# Get settings of domain admin and global admin
if 'domainadmin' in form:
updates['isadmin'] = 1
log_activity(msg="User %s is marked as normal domain admin." % mail,
domain=domain,
admin=session.get('username'),
username=mail,
event='grant')
else:
updates['isadmin'] = 0
if 'allowed_to_grant_admin' in form:
sql_lib_general.update_user_settings(conn=conn,
mail=mail,
new_settings={'grant_admin': 'yes'})
log_activity(msg="Grant user {} as domain admin by {}".format(mail, session.get('username')),
domain=domain,
admin=session.get('username'),
username=mail,
event='grant')
else:
sql_lib_general.update_user_settings(conn=conn,
mail=mail,
removed_settings=['grant_admin'])
if mail == session.get('username'):
session['allowed_to_grant_admin'] = False
if 'old_allowed_to_grant_admin' in form:
log_activity(msg="Revoke admin {} by {}".format(mail, session.get('username')),
domain=domain,
admin=session.get('username'),
username=mail,
event='revoke')
if session.get('is_global_admin'):
# Mark user as global admin
if 'domainGlobalAdmin' in form:
updates['isglobaladmin'] = 1
managed_domains += ['ALL']
log_activity(msg="User %s is marked as global admin." % mail,
domain=domain,
admin=session.get('username'),
username=mail,
event='grant')
else:
updates['isglobaladmin'] = 0
# Update account settings
_new_settings = {}
_removed_settings = []
for k in ['disable_viewing_mail_log',
'disable_managing_quarantined_mails']:
if k in form:
_new_settings[k] = 'yes'
else:
_removed_settings += [k]
#
# If marked as normal domain admin, allow to create new domains
#
if 'allowed_to_create_domain' in form:
_new_settings['create_new_domains'] = 'yes'
for i in ['create_max_domains',
'create_max_quota',
'create_max_users',
'create_max_aliases']:
if i in form:
try:
v = int(form.get(i, '0'))
except:
v = 0
if v > 0:
_new_settings[i] = v
else:
_removed_settings.append(i)
if 'create_max_quota' in _new_settings:
if 'create_quota_unit' in form:
v = form.get('create_quota_unit', 'TB')
if v in ['TB', 'GB']:
_new_settings['create_quota_unit'] = v
else:
_removed_settings += ['create_quota_unit']
for k in ['disable_domain_ownership_verification']:
if k in form:
_new_settings[k] = 'yes'
else:
_removed_settings += [k]
else:
_removed_settings += ['create_new_domains',
'create_max_domains',
'create_max_quota',
'create_max_users',
'create_max_aliases',
'disable_domain_ownership_verification']
if _new_settings:
sql_lib_general.update_user_settings(conn=conn, mail=mail, new_settings=_new_settings)
if _removed_settings:
sql_lib_general.update_user_settings(conn=conn, mail=mail, removed_settings=_removed_settings)
if session.get('is_global_admin') or session.get('allowed_to_grant_admin'):
# Get managed domains
managed_domains += form_utils.get_domain_names(form)
try:
# Delete records in domain_admins first
conn.delete('domain_admins',
vars={'username': mail},
where='username=$username')
if managed_domains:
v = []
for d in set(managed_domains):
v += [{'username': mail,
'domain': d,
'created': iredutils.get_gmttime(),
'active': 1}]
conn.multiple_insert('domain_admins', values=v)
del v
except Exception as e:
return False, repr(e)
# Get name
updates['name'] = form.get('cn', '')
# Get preferred language: short lang code. e.g. en_US, de_DE.
preferred_language = form_utils.get_language(form)
if preferred_language in iredutils.get_language_maps():
updates['language'] = preferred_language
else:
updates['language'] = ''
tz_name = form_utils.get_timezone(form)
if tz_name:
sql_lib_general.update_user_settings(conn=conn,
mail=mail,
new_settings={'timezone': tz_name})
if session['username'] == mail:
session['timezone'] = TIMEZONES[tz_name]
else:
sql_lib_general.update_user_settings(conn=conn,
mail=mail,
removed_settings=['timezone'])
# Update language immediately.
if session.get('username') == mail and \
session.get('lang', 'en_US') != preferred_language:
session['lang'] = preferred_language
# check account status
updates['active'] = 0
if 'accountStatus' in form:
updates['active'] = 1
# Update account status in table `forwardings` immediately
try:
conn.update('forwardings',
vars={'address': mail},
where='address=$address OR forwarding=$address',
active=updates['active'])
except:
pass
# Get mail quota size.
mailQuota = str(form.get('mailQuota'))
if mailQuota.isdigit():
mailQuota = int(mailQuota)
else:
mailQuota = 0
# Verify mail quota, it cannot exceed domain quota.
domain_quota = int(domain_profile.get('maxquota', 0))
max_user_quota = domain_settings.get('max_user_quota', 0)
if domain_quota == 0:
# Unlimited domain quota
if max_user_quota:
if mailQuota <= max_user_quota:
updates['quota'] = mailQuota
else:
updates['quota'] = max_user_quota
else:
updates['quota'] = mailQuota
else:
# Get domain spare quota
# Get allocated quota size
domain_allocated_quota = sql_lib_domain.get_allocated_domain_quota(domains=[domain], conn=conn)
# Get quota of current user
qr = simple_profile(conn=conn,
mail=mail,
columns=['quota'])
if qr[0]:
current_user_quota = int(qr[1].quota)
else:
return qr
domain_spare_quota = domain_quota - domain_allocated_quota + current_user_quota
if domain_spare_quota < 0:
# Set to 1 MB
updates['quota'] = 1
else:
if mailQuota <= domain_spare_quota:
if max_user_quota:
if mailQuota <= max_user_quota:
updates['quota'] = mailQuota
else:
updates['quota'] = max_user_quota
else:
updates['quota'] = mailQuota
else:
if max_user_quota:
if domain_spare_quota <= max_user_quota:
updates['quota'] = domain_spare_quota
else:
updates['quota'] = max_user_quota
else:
updates['quota'] = domain_spare_quota
# Get employee id.
updates['employeeid'] = form.get('employeeNumber', '')
######################################
# Member of subscriable mailing lists
#
# Get currently subscribed lists.
_subscribed_lists = []
_qr = mlmmj.get_subscribed_lists(mail=mail, query_all_lists=False, email_only=True)
if _qr[0]:
_subscribed_lists = _qr[1]
_form_subscribed_lists = [str(i).lower()
for i in form.get('subscribed_list', [])
if i.endswith('@' + domain) and iredutils.is_email(i)]
_unsubscribe_lists = [i for i in _subscribed_lists if i not in _form_subscribed_lists]
_new_subscribed_lists = [i for i in _form_subscribed_lists if i not in _subscribed_lists]
for _list in _unsubscribe_lists:
# Unsubscribe user from multiple lists
_qr = mlmmj.remove_subscribers(mail=_list, subscribers=[mail])
if not _qr[0]:
return _qr
if _new_subscribed_lists:
# Subscribe to multiple lists
_qr = mlmmj.subscribe_to_lists(subscriber=mail, lists=_new_subscribed_lists)
if not _qr[0]:
return _qr
# Get list of assigned alias accounts.
_old_assigned_aliases = []
_qr = get_assigned_aliases(mail=mail, conn=conn)
if _qr[0]:
_old_assigned_aliases = _qr[1]
# Get list of assigned alias from web form.
_form_new_aliases = [str(i).lower()
for i in form.get('memberOfGroup', [])
if iredutils.is_email(i)]
# get aliases in same domain.
_assigned_internal_aliases = [i for i in _form_new_aliases if i.endswith('@' + domain)]
_assigned_external_aliases = [i for i in _form_new_aliases if not i.endswith('@' + domain)]
# Keep old external aliases.
# WARNING: do NOT add any new external aliases, otherwise normal domain
# admin can submit new external aliases.
_kept_external_aliases = [i for i in _old_assigned_aliases
if not i.endswith('@' + domain) and i in _assigned_external_aliases]
_assigned_aliases = _assigned_internal_aliases + _kept_external_aliases
_qr = __reset_assigned_aliases(mail=mail, groups=_assigned_aliases, conn=conn)
if not _qr[0]:
return _qr
elif profile_type == 'forwarding':
fwd_addresses = form.get('mailForwardingAddresses', '').splitlines()
fwd_addresses = list({str(v).lower() for v in fwd_addresses if iredutils.is_email(v)})
if 'savecopy' in form:
fwd_addresses += [mail]
qr = reset_forwardings(mail=mail, forwardings=fwd_addresses, conn=conn)
if not qr[0]:
return qr
elif profile_type == 'bcc':
# Get bcc status
rbcc_active = 0
sbcc_active = 0
if 'recipientbcc' in form:
rbcc_active = 1
if 'senderbcc' in form:
sbcc_active = 1
# Get sender/recipient bcc.
_sbcc = form_utils.get_single_value(form=form,
input_name='senderBccAddress',
is_email=True,
to_lowercase=True)
_rbcc = form_utils.get_single_value(form=form,
input_name='recipientBccAddress',
is_email=True,
to_lowercase=True)
# BCC must handle alias domains.
bcc_alias_domains = [domain]
# Get all alias domains.
_qr = sql_lib_domain.get_all_alias_domains(domain=domain,
name_only=True,
conn=conn)
if _qr[0]:
bcc_alias_domains += _qr[1]
bcc_alias_users = list({mail.split('@', 1)[0] + '@' + d for d in bcc_alias_domains})
del bcc_alias_domains
try:
# Delete bcc records first.
for u in bcc_alias_users:
conn.delete('sender_bcc_user',
vars={'username': u},
where='username=$username')
conn.delete('recipient_bcc_user',
vars={'username': u},
where='username=$username')
# Check local domain and verify existence.
if iredutils.is_email(_sbcc):
_sbcc_domain = _sbcc.split("@", 1)[-1]
if sql_lib_general.is_domain_exists(domain=_sbcc_domain, conn=conn):
if not sql_lib_general.is_email_exists(mail=_sbcc, conn=conn):
_sbcc = None
if iredutils.is_email(_rbcc):
_rbcc_domain = _rbcc.split("@", 1)[-1]
# Check local domain.
if sql_lib_general.is_domain_exists(domain=_rbcc_domain, conn=conn):
if not sql_lib_general.is_email_exists(mail=_rbcc, conn=conn):
_rbcc = None
# Insert new records.
if _sbcc:
for u in bcc_alias_users:
conn.insert('sender_bcc_user',
username=u,
bcc_address=_sbcc,
domain=u.split('@', 1)[-1],
created=iredutils.get_gmttime(),
modified=iredutils.get_gmttime(),
active=sbcc_active)
if _rbcc:
for u in bcc_alias_users:
conn.insert('recipient_bcc_user',
username=u,
bcc_address=_rbcc,
domain=u.split('@', 1)[-1],
created=iredutils.get_gmttime(),
modified=iredutils.get_gmttime(),
active=rbcc_active)
except Exception as e:
return False, repr(e)
elif profile_type == 'relay':
# Get transport.
transport = str(form.get('mtaTransport', ''))
updates['transport'] = transport
# Get sender dependent relayhost
relayhost = str(form.get('relayhost', ''))
# Update relayhost
_qr = sql_lib_general.update_sender_relayhost(account=mail,
relayhost=relayhost,
conn=conn)
if not _qr[0]:
return _qr
elif profile_type == 'aliases':
if session.get('is_global_admin') or 'aliases' not in disabled_user_profiles:
user_alias_addresses = form_utils.get_multi_values(form,
input_name='user_alias_addresses',
default_value=[],
input_is_textarea=True,
is_email=True,
to_lowercase=True)
# Remove primary address first.
if mail in user_alias_addresses:
user_alias_addresses.remove(mail)
# Remove all existing per-user aliases first.
conn.delete('forwardings',
vars={'mail': mail},
where='forwarding=$mail AND is_alias=1')
if not settings.USER_ALIAS_CROSS_ALL_DOMAINS:
# Remove emails not under same domain
user_alias_addresses = [v for v in user_alias_addresses if v.endswith('@' + domain)]
else:
# Remove non-local mail domains
# Get all local mail domains
ua_domains = set()
for addr in user_alias_addresses:
ua_domains.add(addr.split('@', 1)[-1])
# Get exist/nonexist mail domains
qr = sql_lib_general.filter_existing_domains(conn=conn, domains=ua_domains)
exist_domains = qr['exist']
# Remove email addresses in non-local mail domains
user_alias_addresses = [v for v in user_alias_addresses
if v.split('@', 1)[-1] in exist_domains]
# Verify existence
qr = sql_lib_general.filter_existing_emails(conn=conn, mails=user_alias_addresses)
discarded_aliases = qr['exist']
nonexist_user_alias_addresses = qr['nonexist']
# Add all available per-user alias addresses.
v = []
for addr in nonexist_user_alias_addresses:
v += [{'address': addr,
'forwarding': mail,
'domain': domain,
'dest_domain': mail.split('@', 1)[-1],
'is_alias': 1,
'active': 1}]
if v:
conn.multiple_insert('forwardings', values=v)
elif profile_type == 'throttle':
if settings.iredapd_enabled:
t_account = mail
inbound_setting = form_utils.get_throttle_setting(form, account=t_account, inout_type='inbound')
outbound_setting = form_utils.get_throttle_setting(form, account=t_account, inout_type='outbound')
iredapd_throttle.add_throttle(account=t_account,
setting=inbound_setting,
inout_type='inbound')
iredapd_throttle.add_throttle(account=t_account,
setting=outbound_setting,
inout_type='outbound')
elif profile_type == 'wblist':
if session.get('is_global_admin') or 'wblist' not in disabled_user_profiles:
if settings.amavisd_enable_policy_lookup:
wl_senders = get_wblist_from_form(form, 'wl_sender')
bl_senders = get_wblist_from_form(form, 'bl_sender')
wl_rcpts = get_wblist_from_form(form, 'wl_rcpt')
bl_rcpts = get_wblist_from_form(form, 'bl_rcpt')
qr = lib_wblist.add_wblist(account=mail,
wl_senders=wl_senders,
bl_senders=bl_senders,
wl_rcpts=wl_rcpts,
bl_rcpts=bl_rcpts,
flush_before_import=True)
return qr
elif profile_type == 'spampolicy':
qr = spampolicylib.update_spam_policy(account=mail, form=form)
return qr
elif profile_type == 'password':
newpw = web.safestr(form.get('newpw', ''))
confirmpw = web.safestr(form.get('confirmpw', ''))
# Get password length limit from domain profile or global setting.
min_passwd_length = domain_settings.get('min_passwd_length', 0)
max_passwd_length = domain_settings.get('max_passwd_length', 0)
# Verify new passwords.
qr = iredpwd.verify_new_password(newpw=newpw,
confirmpw=confirmpw,
min_passwd_length=min_passwd_length,
max_passwd_length=max_passwd_length)
if qr[0]:
pwscheme = None
if 'store_password_in_plain_text' in form and settings.STORE_PASSWORD_IN_PLAIN_TEXT:
pwscheme = 'PLAIN'
passwd = iredpwd.generate_password_hash(qr[1], pwscheme=pwscheme)
else:
return qr
# Hash/encrypt new password.
updates['password'] = passwd
updates['passwordlastchange'] = iredutils.get_gmttime()
# Store plain password in another attribute.
if settings.STORE_PLAIN_PASSWORD_IN_ADDITIONAL_ATTR:
updates[settings.STORE_PLAIN_PASSWORD_IN_ADDITIONAL_ATTR] = newpw
elif profile_type == 'greylisting':
if settings.iredapd_enabled:
qr = iredapd_greylist.update_greylist_settings_from_form(account=mail, form=form)
return qr
elif profile_type == 'advanced':
# Get enabled/disabled services.
enabledService = [str(v).lower()
for v in form.get('enabledService', [])
if v in ENABLED_SERVICES]
disabledService = []
# Append 'sieve', 'sievesecured' for dovecot-1.2.
if 'enablemanagesieve' in enabledService:
enabledService += ['enablesieve']
else:
disabledService += ['enablesieve']
if 'enablemanagesievesecured' in enabledService:
enabledService += ['enablesievesecured']
else:
disabledService += ['enablesievesecured']
# Receiving email on server for this user
if 'enabledeliver' in enabledService:
enabledService += ['enablelda']
enabledService += ['enablelmtp']
# Mark `forwardings.active=1` to tell Postfix to accept emails for this user.
try:
conn.update("forwardings",
vars={"mail": mail},
where="address=$mail",
active=1)
except Exception as e:
return False, repr(e)
else:
disabledService += ['enablelda']
disabledService += ['enablelmtp']
# Mark `forwardings.active=0` to tell Postfix to NOT accept emails for this user.
try:
conn.update("forwardings",
vars={"mail": mail},
where="address=$mail",
active=0)
except Exception as e:
return False, repr(e)
disabledService += [v for v in ENABLED_SERVICES if v not in enabledService]
# Enable/disable services.
for srv in enabledService:
if srv in ["enablesogowebmail", "enablesogocalendar", "enablesogoactivesync"]:
updates[srv] = "y"
else:
updates[srv] = 1
for srv in disabledService:
if srv in ["enablesogowebmail", "enablesogocalendar", "enablesogoactivesync"]:
updates[srv] = "n"
else:
updates[srv] = 0
# allow_nets
_allow_nets = form.get('allow_nets', '').splitlines()
allow_nets = [str(v) for v in _allow_nets if iredutils.is_ip_or_network(v)]
if allow_nets:
updates['allow_nets'] = ','.join(allow_nets)
else:
updates['allow_nets'] = None
# Maildir path
if session.get('is_global_admin'):
# Get maildir related settings.
storagebasedirectory = str(form.get('storageBaseDirectory', ''))
storagenode = str(form.get('storageNode', ''))
maildir = str(form.get('mailMessageStore', ''))
updates['storagebasedirectory'] = storagebasedirectory
updates['storagenode'] = storagenode
updates['maildir'] = maildir
else:
return True,
# Update SQL db
try:
conn.update('mailbox',
vars={'username': mail},
where='username=$username',
**updates)
# Handle the new sql column `mailbox.enableimaptls` introduced in
# iRedMail-0.9.8, required by Dovecot-2.3.
# Some sysadmin may forgot to add this new column, so we handle it
# separately. and we remove this in 2 future iRedAdmin-Pro releases.
# TODO remove this in 2 future iRedAdmin-Pro releases.
if 'enableimapsecured' in updates:
try:
conn.update('mailbox',
vars={'username': mail},
where='username=$username',
enableimaptls=updates['enableimapsecured'])
except:
pass
if 'enablepop3secured' in updates:
try:
conn.update('mailbox',
vars={'username': mail},
where='username=$username',
enablepop3tls=updates['enablepop3secured'])
except:
pass
# Update session immediately after updating SQL.
if profile_type == 'general':
if 'domainGlobalAdmin' not in form and \
session.get('username') == mail:
session['is_global_admin'] = False
log_activity(msg="Update user profile ({}): {}.".format(profile_type, mail),
admin=session.get('username'),
username=mail,
domain=domain,
event='update')
return True, {'discarded_aliases': discarded_aliases}
except Exception as e:
return False, repr(e)
def update_preferences(conn, mail, form, profile_type='general'):
mail = str(mail).lower()
if not session['username'] == mail:
raise web.seeother('/preferences?msg=PERMISSION_DENIED')
domain = mail.split('@', 1)[-1]
# Get domain profile, used to get disabled user preferences
qr = sql_lib_domain.simple_profile(conn=conn,
domain=domain,
columns=['settings'])
if qr[0]:
domain_profile = qr[1]
else:
return qr
domain_settings = sqlutils.account_settings_string_to_dict(domain_profile.get('settings', ''))
disabled_user_preferences = domain_settings.get('disabled_user_preferences', [])
if profile_type in disabled_user_preferences:
raise web.seeother('/preferences?msg=PERMISSION_DENIED')
# Pre-defined update key:value pairs
updates = {'modified': iredutils.get_gmttime()}
if profile_type == 'general':
if 'personal_info' not in disabled_user_preferences:
updates['name'] = form.get('cn', '')
# Get preferred language: short lang code. e.g. en_US, de_DE.
lang = form_utils.get_language(form)
updates['language'] = lang
# Update language immediately.
if session.get('username') == mail and session.get('lang') != lang:
session['lang'] = lang
# Timezone
tz_name = form_utils.get_timezone(form)
if tz_name:
sql_lib_general.update_user_settings(conn=conn,
mail=mail,
new_settings={'timezone': tz_name})
if session['username'] == mail:
session['timezone'] = TIMEZONES[tz_name]
elif profile_type == 'forwarding':
fwd_addresses = form.get('mailForwardingAddresses', '').splitlines()
fwd_addresses = list({str(v).lower() for v in fwd_addresses if iredutils.is_email(v)})
if 'savecopy' in form:
fwd_addresses += [mail]
qr = reset_forwardings(mail=mail, forwardings=fwd_addresses, conn=conn)
return qr
elif profile_type == 'password':
newpw = web.safestr(form.get('newpw', ''))
confirmpw = web.safestr(form.get('confirmpw', ''))
db_settings = iredutils.get_settings_from_db()
_min_passwd_length = db_settings['min_passwd_length']
_max_passwd_length = db_settings['max_passwd_length']
# Get password length limit from domain profile or global setting.
min_passwd_length = domain_settings.get('min_passwd_length', _min_passwd_length)
max_passwd_length = domain_settings.get('max_passwd_length', _max_passwd_length)
# Verify new passwords.
qr = iredpwd.verify_new_password(newpw=newpw,
confirmpw=confirmpw,
min_passwd_length=min_passwd_length,
max_passwd_length=max_passwd_length)
if qr[0]:
pwscheme = None
if 'store_password_in_plain_text' in form and settings.STORE_PASSWORD_IN_PLAIN_TEXT:
pwscheme = 'PLAIN'
passwd = iredpwd.generate_password_hash(qr[1], pwscheme=pwscheme)
else:
return qr
updates['password'] = passwd
updates['passwordlastchange'] = iredutils.get_gmttime()
# Always store plain password in another attribute.
if settings.STORE_PLAIN_PASSWORD_IN_ADDITIONAL_ATTR:
updates[settings.STORE_PLAIN_PASSWORD_IN_ADDITIONAL_ATTR] = newpw
# Update SQL db
try:
conn.update('mailbox',
vars={'username': mail},
where='username=$username',
**updates)
log_activity(msg="[self-service] Update profile ({}): {}.".format(profile_type, mail),
admin=mail,
username=mail,
domain=domain,
event='update')
return True,
except Exception as e:
return False, repr(e)
def get_user_alias_addresses(mail, conn=None):
"""Get per-user alias addresses of given mail user.
Returns tuple (True, [...]) or (False, '<error>').
@mail -- user mail address
@conn -- sql connection cursor
"""
if not iredutils.is_email(mail):
return False, 'INVALID_MAIL'
if not conn:
_wrap = SQLWrap()
conn = _wrap.conn
try:
qr = conn.select('forwardings',
vars={'mail': mail},
what='address',
where='forwarding=$mail AND is_alias=1')
if qr:
_addresses = []
for i in qr:
_addr = str(i.address).lower()
_addresses.append(_addr)
_addresses.sort()
return True, _addresses
else:
return True, []
except Exception as e:
return False, repr(e)
def get_bulk_user_alias_addresses(mails, conn=None):
"""Get per-user alias addresses of given mail users.
@mails -- a list/tuple/set of user mail addresses
@conn -- sql connection cursor
"""
alias_addresses = {}
mails = [str(i).lower() for i in mails if iredutils.is_email(i)]
if not mails:
return True, alias_addresses
if not conn:
_wrap = SQLWrap()
conn = _wrap.conn
try:
qr = conn.select('forwardings',
vars={'addresses': mails},
what='address, forwarding',
where='forwarding IN $addresses AND is_alias=1')
for r in qr:
_user = str(r.forwarding).lower()
_alias = str(r.address).lower()
if _user in alias_addresses:
alias_addresses[_user].append(_alias)
else:
alias_addresses[_user] = [_alias]
return True, alias_addresses
except Exception as e:
return False, repr(e)
def get_user_forwardings(mail, conn=None):
"""Get mail forwarding addresses of given mail user.
Returns (True, [<mail>, <mail>, ...]) or (False, '<error>').
@mail -- user mail address
@conn -- sql connection cursor
"""
_forwardings = []
if not iredutils.is_email(mail):
return True, _forwardings
if not conn:
_wrap = SQLWrap()
conn = _wrap.conn
try:
qr = conn.select('forwardings',
vars={'address': mail},
what='forwarding',
where='address=$address AND is_forwarding=1')
for r in qr:
_addr = str(r.forwarding).lower()
_forwardings.append(_addr)
_forwardings.sort()
return True, _forwardings
except Exception as e:
return False, repr(e)
def get_bulk_user_forwardings(mails, conn=None):
"""Get mail forwarding addresses of given mail users.
@mails -- a list/tuple/set of user mail addresses
@conn -- sql connection cursor
"""
user_forwardings = {}
mails = [str(i).lower() for i in mails if iredutils.is_email(i)]
if not mails:
return True, user_forwardings
if not conn:
_wrap = SQLWrap()
conn = _wrap.conn
try:
qr = conn.select('forwardings',
vars={'addresses': mails},
what='address,forwarding',
where='address IN $addresses AND is_forwarding=1')
for r in qr:
_user = str(r.address).lower()
_forwarding = str(r.forwarding).lower()
if _user in user_forwardings:
user_forwardings[_user].append(_forwarding)
else:
user_forwardings[_user] = [_forwarding]
return True, user_forwardings
except Exception as e:
return False, repr(e)
def get_assigned_aliases(mail, conn=None):
"""Get assigned mail aliases of given user.
:param mail: mail address
:param conn: sql connection cursor
"""
if not iredutils.is_email(mail):
return True, []
if not conn:
_wrap = SQLWrap()
conn = _wrap.conn
_groups = []
try:
qr = conn.select('forwardings',
vars={'mail': mail},
what='address',
where='forwarding=$mail AND is_list=1',
group='address')
for r in qr:
_addr = str(r.address).lower()
_groups.append(_addr)
_groups.sort()
return True, _groups
except Exception as e:
return False, repr(e)
def get_bulk_user_assigned_groups(mails, conn=None):
"""Get email addresses of mail alias accounts which have given users as a
member.
@mails -- a list/tuple/set of mail addresses of mail users
@conn -- sql connection cursor
"""
_groups = {}
mails = [str(i).lower() for i in mails if iredutils.is_email(i)]
if not mails:
return True, _groups
if not conn:
_wrap = SQLWrap()
conn = _wrap.conn
try:
qr = conn.select('forwardings',
vars={'mails': mails},
what='address, forwarding',
where='forwarding IN $mails AND is_list=1')
for r in qr:
_group = str(r.address).lower()
_user = str(r.forwarding).lower()
if _user in _groups:
_groups[_user].append(_group)
else:
_groups[_user] = [_group]
return True, _groups
except Exception as e:
return False, repr(e)
def reset_aliases(mail, aliases=None, conn=None):
"""Reset per-user alias addresses. if @aliases is empty of None, all
per-user aliases will be removed."""
domain = mail.split('@', 1)[-1]
_addresses = {str(i).lower()
for i in aliases
if iredutils.is_email(i) and i.endswith('@' + domain)}
# Remove self
_addresses.discard(mail)
if not conn:
_wrap = SQLWrap()
conn = _wrap.conn
# Remove all per-user alias addresses first
try:
conn.delete('forwardings',
vars={'mail': mail},
where='forwarding=$mail AND is_alias=1')
except Exception as e:
return False, repr(e)
# Return if no need to reset
if not _addresses:
return True,
# Get existing mail addresses
qr = sql_lib_general.filter_existing_emails(mails=_addresses, conn=conn)
_existings = qr['exist']
# Remove existing addresses, use non-existing addresses
_non_existings = [v for v in _addresses if v not in _existings]
if not _non_existings:
return True,
v = []
for i in _non_existings:
v += [{'address': i,
'forwarding': mail,
'domain': domain,
'dest_domain': mail.split('@', 1)[-1],
'is_alias': 1,
'active': 1}]
try:
conn.multiple_insert('forwardings', values=v)
return True,
except Exception as e:
return False, repr(e)
def update_aliases(mail, new=None, removed=None, conn=None):
"""Add new per-user alias addresses defined in @new_aliases to user,
remove existing aliases defined in @removed_aliases."""
domain = mail.split('@', 1)[-1]
_new = set()
if new:
_new = {str(i).lower()
for i in new
if i.endswith('@' + domain) and iredutils.is_email(i)}
_removed = []
if removed:
_removed = [str(i).lower() for i in removed if iredutils.is_email(i)]
if not (_new or _removed):
return True, 'NO_VALID_ALIAS_ADDRESS'
if not conn:
_wrap = SQLWrap()
conn = _wrap.conn
if _new:
# Remove self
_new.discard(mail)
# Query and exclude existing addresses
try:
qr = sql_lib_general.filter_existing_emails(mails=_new, conn=conn)
_existings = qr['exist']
_new = _new - set(_existings)
except Exception as e:
return False, repr(e)
# Exclude addresses in _removed from _new
if _removed:
for i in _removed:
_new.discard(i)
if _new:
# Add new per-user alias addresses by inserting a new record
for addr in _new:
try:
conn.insert('forwardings',
address=addr,
forwarding=mail,
domain=domain,
dest_domain=mail.split('@', 1)[-1],
is_alias=1,
active=1)
except Exception as e:
# We already exclude existing addresses, so no more
# error caused by duplicate sql record, it must be
# something serious.
return False, repr(e)
if _removed:
try:
conn.delete('forwardings',
vars={'removed': _removed, 'mail': mail},
where='address IN $removed AND forwarding=$mail')
except Exception as e:
return False, repr(e)
return True,
def get_basic_user_profiles(domain,
columns=None,
first_char=None,
page=0,
disabled_only=False,
email_only=False,
with_last_login=False,
with_used_quota=False,
conn=None):
"""Get basic user profiles under given domain.
Return data:
(True, [{'mail': 'list@domain.com',
'name': '...',
...other profiles in `vmail.maillists` table...
}])
"""
domain = web.safestr(domain).lower()
if not iredutils.is_domain(domain):
raise web.seeother('/domains?msg=INVALID_DOMAIN_NAME')
sql_vars = {'domain': domain}
if columns:
sql_what = ','.join(columns)
else:
if email_only:
sql_what = 'username'
else:
sql_what = '*'
if email_only:
sql_what = 'username'
additional_sql_where = ''
if first_char:
additional_sql_where = ' AND address LIKE %s' % web.sqlquote(first_char.lower() + '%')
if disabled_only:
additional_sql_where = ' AND active=0'
# Get basic profiles
try:
if not conn:
_wrap = SQLWrap()
conn = _wrap.conn
if page:
qr = conn.select('mailbox',
vars=sql_vars,
what=sql_what,
where='domain=$domain %s' % additional_sql_where,
order='username ASC',
limit=settings.PAGE_SIZE_LIMIT,
offset=(page - 1) * settings.PAGE_SIZE_LIMIT)
else:
qr = conn.select('mailbox',
vars=sql_vars,
what=sql_what,
where='domain=$domain %s' % additional_sql_where,
order='username ASC')
rows = list(qr)
emails = []
if email_only or with_last_login or with_used_quota:
emails = [str(i.username).lower() for i in rows]
if email_only:
return True, emails
else:
if with_last_login:
last_logins = sql_lib_general.get_account_last_login(accounts=emails)
for row in rows:
email = row.username
_epoch_seconds = {
"imap": 0,
"pop3": 0,
"lda": 0,
}
if email in last_logins:
i = last_logins[email]
if i["imap"]:
_epoch_seconds["imap"] = i["imap"]
if i["pop3"]:
_epoch_seconds["pop3"] = i["pop3"]
if i["lda"]:
_epoch_seconds["lda"] = i["lda"]
row['last_login'] = _epoch_seconds
if with_used_quota and emails:
used_quota_info = {}
qr = conn.select('used_quota',
vars={"emails": emails},
what="username, bytes, messages",
where='username IN $emails',
order='username ASC')
for i in qr:
used_quota_info[i.username] = {"bytes": i.bytes, "messages": i.messages}
for row in rows:
email = row.username
_bytes = 0
_messages = 0
if email in used_quota_info:
_bytes = used_quota_info[email]["bytes"]
_messages = used_quota_info[email]["messages"]
row["used_quota"] = {
"bytes": _bytes,
"messages": _messages,
}
return True, rows
except Exception as e:
return False, repr(e)
@decorators.api_require_domain_access
def api_update_profile(mail, form, conn=None):
"""Update user profile.
Optional form parameters:
@name - common name (or, display name)
@password - set new password for user
@password_hash - set new password to given hashed password
@quota - mailbox quota for this user (in MB).
@accountStatus - enable or disable user. possible value is: active, disabled.
@language - set preferred language of web UI
@employeeid - set employee id
@transport - set per-user transport
@isGlobalAdmin -- promote user to be a global admin
@forwarding -- set per-user mail forwarding addresseses
@addForwarding -- add per-user mail forwarding addresses
@removeForwarding -- remove existing per-user mail forwarding addresses
@senderBcc -- set per-user bcc for outbound emails
@recipientBcc -- set per-user bcc for inbound emails
@aliases -- reset per-user alias addresses
@addAlias -- add new per-user alias addresses
@removeAlias -- remove existing per-user alias addresses
@maildir -- full maildir path of the mailbox
"""
mail = str(mail).lower()
domain = mail.split('@', 1)[-1]
if not conn:
_wrap = SQLWrap()
conn = _wrap.conn
if not session.get('is_global_admin'):
redirect_if_user_is_global_admin(conn=conn, mail=mail)
params = {}
# Name
kv = form_utils.get_form_dict(form=form, input_name='name')
params.update(kv)
# Password
if 'password' in form:
qr = api_utils.get_form_password_dict(form=form,
domain=domain,
input_name='password')
if qr[0]:
params['password'] = qr[1]['pw_hash']
params['passwordlastchange'] = iredutils.get_gmttime()
else:
return qr
elif 'password_hash' in form:
_pw_hash = form.get('password_hash', '').strip()
params['password'] = _pw_hash
params['passwordlastchange'] = iredutils.get_gmttime()
# Account status
kv = form_utils.get_form_dict(form,
input_name='accountStatus',
key_name='active')
params.update(kv)
# Language
kv = form_utils.get_form_dict(form,
input_name='language',
to_string=True)
params.update(kv)
# Employee ID
kv = form_utils.get_form_dict(form,
input_name='employeeid',
to_string=True)
params.update(kv)
# Transport
kv = form_utils.get_form_dict(form,
input_name='transport',
to_string=True)
params.update(kv)
# Set quota
if 'quota' in form:
quota = str(form.get('quota', 0)).strip()
qr = sql_lib_domain.assign_given_mailbox_quota(domain=domain,
quota=quota,
reset_user_quota=True,
user=mail)
if qr[0]:
quota = qr[1]
params['quota'] = quota
else:
return qr
# domainGlobalAdmin
if 'isGlobalAdmin' in form:
_v = form_utils.get_single_value(form=form,
input_name='isGlobalAdmin',
default_value='no',
to_string=True)
if _v == 'yes':
promote_users_to_be_global_admin(mails=[mail],
promote=True,
conn=conn)
else:
promote_users_to_be_global_admin(mails=[mail],
promote=False,
conn=conn)
# Mail forwarding addresses.
if 'forwarding' in form:
_fwd_addresses = form_utils.get_multi_values_from_api(form=form,
input_name='forwarding',
to_string=True,
to_lowercase=True,
is_email=True)
qr = reset_forwardings(mail=mail,
forwardings=_fwd_addresses,
conn=conn)
if not qr[0]:
return qr
else:
# handle `addForwarding` and `removeForwarding`
_forwardings = []
if ('addForwarding' in form) or ('removeForwarding' in form):
_qr = get_user_forwardings(mail=mail, conn=conn)
if _qr[0]:
_forwardings = _qr[1]
else:
return _qr
if 'addForwarding' in form:
_v = form_utils.get_multi_values_from_api(form=form,
input_name='addForwarding',
to_string=True,
is_email=True)
_forwardings += _v
if 'removeForwarding' in form:
_v = form_utils.get_multi_values_from_api(form=form,
input_name='removeForwarding',
to_string=True,
is_email=True)
_forwardings = [v for v in _forwardings if v not in _v]
_forwardings = list(set(_forwardings))
_qr = reset_forwardings(mail=mail,
forwardings=_forwardings,
conn=conn)
if not _qr[0]:
return _qr
# BCC. only one bcc address is allowed.
_sbcc = None
_rbcc = None
# User requested to remove all existing bcc addresses.
_empty_sbcc = False
_empty_rbcc = False
if 'senderBcc' in form:
_sbcc = form_utils.get_single_value(form,
input_name='senderBcc',
to_lowercase=True,
is_email=True)
if not _sbcc:
_empty_sbcc = True
if 'recipientBcc' in form:
_rbcc = form_utils.get_single_value(form,
input_name='recipientBcc',
to_lowercase=True,
is_email=True)
if not _rbcc:
_empty_rbcc = True
if _sbcc or _rbcc or _empty_sbcc or _empty_rbcc:
# BCC must handle alias domains.
bcc_alias_domains = [domain]
# Get all alias domains.
_qr = sql_lib_domain.get_all_alias_domains(domain=domain,
name_only=True,
conn=conn)
if _qr[0]:
bcc_alias_domains += _qr[1]
bcc_alias_users = list({mail.split('@', 1)[0] + '@' + d for d in bcc_alias_domains})
del bcc_alias_domains
if _sbcc or _empty_sbcc:
try:
# Delete bcc records first.
for u in bcc_alias_users:
conn.delete('sender_bcc_user',
vars={'username': u},
where='username=$username')
except Exception as e:
return False, repr(e)
if _rbcc or _empty_rbcc:
try:
conn.delete('recipient_bcc_user',
vars={'username': u},
where='username=$username')
except Exception as e:
return False, repr(e)
if _sbcc:
# Insert new records.
try:
for u in bcc_alias_users:
conn.insert('sender_bcc_user',
username=u,
bcc_address=_sbcc,
domain=u.split('@', 1)[-1],
created=iredutils.get_gmttime(),
modified=iredutils.get_gmttime(),
active=1)
except Exception as e:
return False, repr(e)
if _rbcc:
try:
for u in bcc_alias_users:
conn.insert('recipient_bcc_user',
username=u,
bcc_address=_rbcc,
domain=u.split('@', 1)[-1],
created=iredutils.get_gmttime(),
modified=iredutils.get_gmttime(),
active=1)
except Exception as e:
return False, repr(e)
# Per-user alias addresses
if 'aliases' in form:
_v = form_utils.get_single_value(form=form,
input_name='aliases',
default_value='',
to_lowercase=True,
to_string=True)
_v = _v.strip(' ').split(',')
qr = reset_aliases(mail=mail, aliases=_v, conn=conn)
if not qr[0]:
return qr
else:
# Add new aliases
_new = []
# Remove existing aliases
_removed = []
# Add/remove per-user aliases
if 'addAlias' in form:
_v = form_utils.get_single_value(form=form,
input_name='addAlias',
to_lowercase=True,
to_string=True)
_v = _v.strip(' ').split(',')
_new = [i for i in _v if iredutils.is_email(i)]
if 'removeAlias' in form:
_v = form_utils.get_single_value(form=form,
input_name='removeAlias',
to_lowercase=True,
to_string=True)
_v = _v.strip(' ').split(',')
_removed = [i for i in _v if iredutils.is_email(i)]
if _new or _removed:
qr = update_aliases(mail=mail,
new=_new,
removed=_removed,
conn=conn)
if not qr[0]:
return qr
# Per-user enabled mail services
# NOTE: it requires SQL column `enable<service>` in `vmail.mailbox`.
if 'services' in form:
_v = form_utils.get_multi_values_from_api(form=form, input_name='services')
# Disable all mail services
for i in ENABLED_SERVICES:
params[i] = 0
# Enable requested services
for i in _v:
_srv = 'enable' + i
params[_srv] = 1
else:
if 'addService' in form:
_v = form_utils.get_multi_values_from_api(form=form, input_name='addService')
for i in _new:
_srv = 'enable' + i
params[_srv] = 1
if 'removeService' in form:
_v = form_utils.get_multi_values_from_api(form=form, input_name='removeService')
for i in _v:
_srv = 'enable' + i
params[_srv] = 0
# Get mailbox format and folder.
_mailbox_format = form.get('mailboxFormat', None)
if _mailbox_format:
if iredutils.is_valid_mailbox_format(_mailbox_format):
params['mailboxformat'] = _mailbox_format
_mailbox_folder = form.get('mailboxFolder', '')
if iredutils.is_valid_mailbox_folder(_mailbox_folder):
params['mailboxfolder'] = _mailbox_folder
_mailbox_maildir = form.get('maildir', '').lower().rstrip('/')
if _mailbox_maildir and os.path.isabs(_mailbox_maildir):
# Split storageBaseDirectory and storageNode
_splited = _mailbox_maildir.rstrip('/').split('/')
params['storagebasedirectory'] = '/' + _splited[0]
params['storagenode'] = _splited[1]
params['maildir'] = '/'.join(_splited[2:])
if not params:
return True,
try:
conn.update('mailbox',
vars={'mail': mail},
where='username=$mail',
**params)
try:
# Log updated parameters and values if possible
msg = str(params)
except:
msg = ', '.join(params)
log_activity(msg="Update user profile: {} -> {}".format(mail, msg),
admin=session.get('username'),
username=mail,
domain=domain,
event='update')
return True,
except Exception as e:
return False, repr(e)
Reference in New Issue View Git Blame Copy Permalink