def is_valid_sender(sender): if utils.is_ip(sender) or \ utils.is_valid_amavisd_address(sender) in ['catchall', 'top_level_domain', 'domain', 'subdomain', 'email']: return True else: return False
def create_mailaddr(conn, addresses) -> bool: for addr in addresses: addr_type = utils.is_valid_amavisd_address(addr) if addr_type in utils.MAILADDR_PRIORITIES: priority = utils.MAILADDR_PRIORITIES[addr_type] try: sql = "INSERT INTO mailaddr (email, priority) VALUES ({}, {})".format(sqlquote(addr), sqlquote(priority)) conn.execute(sql) except: pass return True
def create_user(conn, account, return_record=True): # Create a new record in `amavisd.users` addr_type = utils.is_valid_amavisd_address(account) try: # Use policy_id=0 to make sure it's not linked to any policy. sql = "INSERT INTO users (policy_id, email, priority) VALUES (%d, '%s', %d)" % (0, account, utils.MAILADDR_PRIORITIES[addr_type]) conn.execute(sql) if return_record: sql = "SELECT id, priority, policy_id, email FROM users WHERE email='%s' LIMIT 1" % account qr = conn.execute(sql) sql_record = qr.fetchone() return (True, sql_record) else: return (True, ) except Exception as e: return (False, str(e))
def delete_all_wblist(conn, account, wl_senders=False, bl_senders=False, wl_rcpts=False, bl_rcpts=False): if not utils.is_valid_amavisd_address(account): return (False, 'INVALID_ACCOUNT') # Get account id from `amavisd.users` qr = get_user_record(conn=conn, account=account) if qr[0]: user_id = qr[1]['id'] else: return qr # Remove ALL wblist. # No need to remove unused senders in `mailaddr` table, because we # have daily cron job to delete them (tools/cleanup_amavisd_db.py). try: if wl_senders: conn.delete('wblist', vars={'user_id': user_id}, where="rid=$user_id AND wb='W'") if bl_senders: conn.delete('wblist', vars={'user_id': user_id}, where="rid=$user_id AND wb='B'") if wl_rcpts: conn.delete('outbound_wblist', vars={'user_id': user_id}, where="sid=$user_id AND wb='W'") if bl_rcpts: conn.delete('outbound_wblist', vars={'user_id': user_id}, where="sid=$user_id AND wb='B'") except Exception as e: return (False, str(e)) return (True, )
def delete_all_wblist(conn, account, wl_senders=False, bl_senders=False, wl_rcpts=False, bl_rcpts=False): if not utils.is_valid_amavisd_address(account): return (False, 'INVALID_ACCOUNT') # Get account id from `amavisd.users` qr = get_user_record(conn=conn, account=account) if qr[0]: user_id = qr[1]['id'] else: return qr # Remove ALL wblist. # No need to remove unused senders in `mailaddr` table, because we # have daily cron job to delete them (tools/cleanup_amavisd_db.py). try: if wl_senders: sql = "DELETE FROM wblist WHERE rid=%d AND wb='W'" % int(user_id) conn.execute(sql) if bl_senders: sql = "DELETE FROM wblist WHERE rid=%d AND wb='B'" % int(user_id) conn.execute(sql) if wl_rcpts: sql = "DELETE FROM outbound_wblist WHERE sid=%d AND wb='W'" % int( user_id) conn.execute(sql) if bl_rcpts: sql = "DELETE FROM outbound_wblist WHERE sid=%d AND wb='B'" % int( user_id) conn.execute(sql) except Exception as e: return (False, str(e)) return (True, )
def add_whitelist_sender(conn, account, sender, comment=None): if not is_valid_sender(sender): return (False, 'INVALID_SENDER') if utils.is_valid_amavisd_address(account) not in [ 'catchall', 'domain', 'subdomain', 'email' ]: return (False, 'INVALID_ACCOUNT') comment = comment or '' try: sql = """INSERT INTO greylisting_whitelists (account, sender, comment) VALUES ('%s', '%s', '%s')""" % ( account, sender, comment) conn.execute(sql) except Exception as e: error = str(e).lower() if 'duplicate key' in error or 'duplicate entry' in error: pass else: return (False, str(e)) return (True, )
inout_type = 'outbound' args.remove('--outbound') # Get wblist account, verify whether it's hosted locally. account = '@.' if '--account' in args: # per-domain or per-user account index = args.index('--account') account = args[index + 1] # Remove them. args.pop(index) args.pop(index) wb_account = account wb_account_type = utils.is_valid_amavisd_address(wb_account) if '@' not in account: sys.exit('<<< ERROR >>> Invalid account format.') # Get wblist type. wblist_type = '' for_whitelist = False for_blacklist = False if '--whitelist' in args: wblist_type = 'whitelist' for_whitelist = True args.remove('--whitelist') elif '--blacklist' in args: wblist_type = 'blacklist' for_blacklist = True
# Remove them. args.pop(index) args.pop(index) if not lib_gl.is_valid_sender(sender): sys.exit('<<< ERROR >>> Invalid sender address.') if '@' not in rcpt: sys.exit('<<< ERROR >>> Invalid recipient address.') # Check whether sender address is a domain name. sender_is_domain = False sender_domain = '' if utils.is_valid_amavisd_address(sender) in ['domain', 'subdomain']: sender_is_domain = True sender_domain = sender.split('@', 1)[-1] # Connection cursor with web.py conn = get_db_conn('iredapd') # Connection cursor with SQLAlchemy conn2 = get_db_conn2('iredapd') gl_setting = lib_gl.get_gl_base_setting(account=rcpt, sender=sender) # Perform the operations if action == 'enable': logger.info("* Enable greylisting: {} -> {}".format(sender, rcpt))
def add_wblist(conn, account, wl_senders=None, bl_senders=None, wl_rcpts=None, bl_rcpts=None, flush_before_import=False): """Add white/blacklists for specified account. wl_senders -- whitelist senders (inbound) bl_senders -- blacklist senders (inbound) wl_rcpts -- whitelist recipients (outbound) bl_rcpts -- blacklist recipients (outbound) flush_before_import -- Delete all existing wblist before importing new wblist """ if not utils.is_valid_amavisd_address(account): return (False, 'INVALID_ACCOUNT') # Remove duplicate. if wl_senders: wl_senders = set([ str(s).lower() for s in wl_senders if utils.is_valid_amavisd_address(s) ]) else: wl_senders = set() # Whitelist has higher priority, don't include whitelisted sender. if bl_senders: bl_senders = set([ str(s).lower() for s in bl_senders if utils.is_valid_amavisd_address(s) ]) else: bl_senders = set() if wl_rcpts: wl_rcpts = set([ str(s).lower() for s in wl_rcpts if utils.is_valid_amavisd_address(s) ]) else: wl_rcpts = set() if bl_rcpts: bl_rcpts = set([ str(s).lower() for s in bl_rcpts if utils.is_valid_amavisd_address(s) ]) else: bl_rcpts = set() if flush_before_import: if wl_senders: bl_senders = set([s for s in bl_senders if s not in wl_senders]) if wl_rcpts: bl_rcpts = set([s for s in bl_rcpts if s not in wl_rcpts]) sender_addresses = set(wl_senders) | set(bl_senders) rcpt_addresses = set(wl_rcpts) | set(bl_rcpts) all_addresses = list(sender_addresses | rcpt_addresses) # Get current user's id from `amavisd.users` qr = get_user_record(conn=conn, account=account) if qr[0]: user_id = qr[1]['id'] else: return qr # Delete old records if flush_before_import: # user_id = wblist.rid conn.execute('DELETE FROM wblist WHERE rid=%s' % sqlquote(user_id)) # user_id = outbound_wblist.sid conn.execute('DELETE FROM outbound_wblist WHERE sid=%s' % sqlquote(user_id)) if not all_addresses: return (True, ) # Insert all senders into `amavisd.mailaddr` create_mailaddr(conn=conn, addresses=all_addresses) # Get `mailaddr.id` of senders sender_records = {} if sender_addresses: sql = "SELECT id, email FROM mailaddr WHERE email IN %s" % sqlquote( list(sender_addresses)) qr = conn.execute(sql) sql_records = qr.fetchall() for r in sql_records: (_id, _email) = r sender_records[_email.decode()] = int(_id) del qr # Get `mailaddr.id` of recipients rcpt_records = {} if rcpt_addresses: sql = "SELECT id, email FROM mailaddr WHERE email IN %s" % sqlquote( list(rcpt_addresses)) qr = conn.execute(sql) sql_records = qr.fetchall() for r in sql_records: (_id, _email) = r rcpt_records[_email.decode()] = int(_id) del qr # Remove existing records of current submitted records then insert new. try: if sender_records: sql = "DELETE FROM wblist WHERE rid=%d AND sid IN %s" % ( user_id, sqlquote(list(sender_records.values()))) conn.execute(sql) if rcpt_records: sql = "DELETE FROM outbound_wblist WHERE sid=%d AND rid IN %s" % ( user_id, sqlquote(list(rcpt_records.values()))) conn.execute(sql) except Exception as e: return (False, e) # Generate dict used to build SQL statements for importing wblist values = [] if sender_addresses: for s in wl_senders: if sender_records.get(s): values.append({ 'rid': user_id, 'sid': sender_records[s], 'wb': 'W' }) for s in bl_senders: # Filter out same record in blacklist if sender_records.get(s) and s not in wl_senders: values.append({ 'rid': user_id, 'sid': sender_records[s], 'wb': 'B' }) rcpt_values = [] if rcpt_addresses: for s in wl_rcpts: if rcpt_records.get(s): rcpt_values.append({ 'sid': user_id, 'rid': rcpt_records[s], 'wb': 'W' }) for s in bl_rcpts: # Filter out same record in blacklist if rcpt_records.get(s) and s not in wl_rcpts: rcpt_values.append({ 'sid': user_id, 'rid': rcpt_records[s], 'wb': 'B' }) try: if values: for v in values: try: conn.execute( "INSERT INTO wblist (sid, rid, wb) VALUES (%s, %s, %s)" % (sqlquote(v['sid']), sqlquote( v['rid']), sqlquote(v['wb']))) except Exception as e: logger.error(e) if rcpt_values: for v in rcpt_values: try: conn.execute( "INSERT INTO outbound_wblist (sid, rid, wb) VALUES (%s, %s, %s)" % (sqlquote(v['sid']), sqlquote( v['rid']), sqlquote(v['wb']))) except Exception as e: logger.error(e) except Exception as e: return (False, e) return (True, )
def delete_wblist(conn, account, wl_senders=None, bl_senders=None, wl_rcpts=None, bl_rcpts=None): if not utils.is_valid_amavisd_address(account): return (False, 'INVALID_ACCOUNT') # Remove duplicate. if wl_senders: wl_senders = list( set([ str(s).lower() for s in wl_senders if utils.is_valid_amavisd_address(s) ])) # Whitelist has higher priority, don't include whitelisted sender. if bl_senders: bl_senders = list( set([ str(s).lower() for s in bl_senders if utils.is_valid_amavisd_address(s) ])) if wl_rcpts: wl_rcpts = list( set([ str(s).lower() for s in wl_rcpts if utils.is_valid_amavisd_address(s) ])) if bl_rcpts: bl_rcpts = list( set([ str(s).lower() for s in bl_rcpts if utils.is_valid_amavisd_address(s) ])) # Get account id from `amavisd.users` qr = get_user_record(conn=conn, account=account) if qr[0]: user_id = qr[1]['id'] else: return qr # Remove wblist. # No need to remove unused senders in `mailaddr` table, because we # have daily cron job to delete them (tools/cleanup_amavisd_db.py). wl_smails = [] wl_rmails = [] bl_smails = [] bl_rmails = [] try: # Get `mailaddr.id` for wblist senders if wl_senders: sids = [] sql = "SELECT id, email FROM mailaddr WHERE email in %s" % sqlquote( wl_senders) qr = conn.execute(sql) sql_records = qr.fetchall() for r in sql_records: (_id, _email) = r sids.append(int(_id)) wl_smails.append(str(_email)) if sids: conn.execute( "DELETE FROM wblist WHERE rid=%s AND sid IN %s AND wb='W'" % (sqlquote(user_id), sqlquote(sids))) if bl_senders: sids = [] sql = "SELECT id, email FROM mailaddr WHERE email IN %s" % sqlquote( bl_senders) qr = conn.execute(sql) sql_records = qr.fetchall() for r in sql_records: (_id, _email) = r sids.append(int(_id)) bl_smails.append(str(_email)) if sids: conn.execute( "DELETE FROM wblist WHERE rid=%s AND sid IN %s AND wb='B'" % (sqlquote(user_id), sqlquote(sids))) if wl_rcpts: rids = [] sql = "SELECT id, email FROM mailaddr WHERE email IN %s" % sqlquote( wl_rcpts) qr = conn.execute(sql) sql_records = qr.fetchall() for r in sql_records: (_id, _email) = r rids.append(int(_id)) wl_rmails.append(str(_email)) if rids: conn.execute( "DELETE FROM outbound_wblist WHERE sid=%s AND rid IN %s AND wb='W'" % (sqlquote(user_id), sqlquote(rids))) if bl_rcpts: rids = [] sql = "SELECT id, email FROM mailaddr WHERE email IN %s" % sqlquote( bl_rcpts) qr = conn.execute(sql) sql_records = qr.fetchall() for r in sql_records: (_id, _email) = r rids.append(int(_id)) bl_rmails.append(str(_email)) if rids: conn.execute( "DELETE FROM outbound_wblist WHERE sid=%s AND rid IN %s AND wb='B'" % (sqlquote(user_id), sqlquote(rids))) except Exception as e: return (False, str(e)) return (True, { 'wl_senders': wl_smails, 'wl_rcpts': wl_rmails, 'bl_senders': bl_smails, 'bl_rcpts': bl_rmails })
def apply_throttle(conn, conn_vmail, user, client_address, protocol_state, size, recipient_count, instance_id, is_sender_throttling=True, is_external_sender=False): possible_addrs = [client_address, '@ip'] if user: possible_addrs += utils.get_policy_addresses_from_email(mail=user) (_username, _domain) = user.split('@', 1) alias_target_sender_domain = get_alias_target_domain( alias_domain=_domain, conn=conn_vmail) if alias_target_sender_domain: _mail = _username + '@' + alias_target_sender_domain possible_addrs += utils.get_policy_addresses_from_email(mail=_mail) sql_user = sqlquote(user) if utils.is_ipv4(client_address): possible_addrs += utils.wildcard_ipv4(client_address) if is_sender_throttling: throttle_type = 'sender' throttle_kind = 'outbound' if is_external_sender: throttle_kind = 'external' else: throttle_type = 'recipient' throttle_kind = 'inbound' sql = """ SELECT id, account, priority, period, max_msgs, max_quota, msg_size FROM throttle WHERE kind=%s AND account IN %s ORDER BY priority DESC """ % (sqlquote(throttle_kind), sqlquote(possible_addrs)) logger.debug('[SQL] Query throttle setting: {}'.format(sql)) qr = conn.execute(sql) throttle_records = qr.fetchall() logger.debug('[SQL] Query result: {}'.format(throttle_records)) if not throttle_records: logger.debug('No {} throttle setting.'.format(throttle_type)) return SMTP_ACTIONS['default'] # Time of now. used for init_time and last_time. now = int(time.time()) # construct the throttle setting t_settings = {} t_setting_ids = {} t_setting_keys = {} # Inherit throttle settings with lower priority. continue_check_msg_size = True continue_check_max_msgs = True continue_check_max_quota = True # print detailed throttle setting throttle_info = '' # sql where statements used to track throttle. # (tid = tid AND account = `user`) tracking_sql_where = set() for rcd in throttle_records: (_id, _account, _priority, _period, _max_msgs, _max_quota, _msg_size) = rcd # Skip throttle setting which doesn't have period if not _period: continue t_setting_keys[(_id, _account)] = [] t_setting_ids[_id] = _account tracking_sql_where.add('(tid=%d AND account=%s)' % (_id, sqlquote(client_address))) if continue_check_msg_size and _msg_size >= 0: continue_check_msg_size = False t_settings['msg_size'] = { 'value': _msg_size, 'period': _period, 'tid': _id, 'account': _account, 'tracking_id': None, 'track_key': [], 'expired': False, 'cur_msgs': 0, 'cur_quota': 0, 'init_time': 0 } t_setting_keys[(_id, _account)].append('msg_size') tracking_sql_where.add('(tid=%d AND account=%s)' % (_id, sql_user)) throttle_info += 'msg_size=%(value)d (bytes)/id=%(tid)d/account=%(account)s; ' % t_settings[ 'msg_size'] if continue_check_max_msgs and _max_msgs >= 0: continue_check_max_msgs = False t_settings['max_msgs'] = { 'value': _max_msgs, 'period': _period, 'tid': _id, 'account': _account, 'tracking_id': None, 'track_key': [], 'expired': False, 'cur_msgs': 0, 'cur_quota': 0, 'init_time': 0 } t_setting_keys[(_id, _account)].append('max_msgs') tracking_sql_where.add('(tid=%d AND account=%s)' % (_id, sql_user)) throttle_info += 'max_msgs=%(value)d/id=%(tid)d/account=%(account)s; ' % t_settings[ 'max_msgs'] if continue_check_max_quota and _max_quota >= 0: continue_check_max_quota = False t_settings['max_quota'] = { 'value': _max_quota, 'period': _period, 'tid': _id, 'account': _account, 'tracking_id': None, 'track_key': [], 'expired': False, 'cur_msgs': 0, 'cur_quota': 0, 'init_time': 0 } t_setting_keys[(_id, _account)].append('max_quota') tracking_sql_where.add('(tid=%d AND account=%s)' % (_id, sql_user)) throttle_info += 'max_quota=%(value)d (bytes)/id=%(tid)d/account=%(account)s; ' % t_settings[ 'max_quota'] if not t_settings: logger.debug('No valid {} throttle setting.'.format(throttle_type)) return SMTP_ACTIONS['default'] else: logger.debug('{} throttle setting: {}'.format(throttle_type, throttle_info)) # Update track_key. for (_, v) in list(t_settings.items()): t_account = v['account'] addr_type = utils.is_valid_amavisd_address(t_account) if addr_type in ['ip', 'catchall_ip']: # Track based on IP address v['track_key'].append(client_address) elif addr_type in ['wildcard_ip', 'wildcard_addr']: # Track based on wildcard IP or sender address v['track_key'].append(t_account) else: # Track based on sender email address v['track_key'].append(user) # Get throttle tracking data. # Construct SQL query WHERE statement sql = """SELECT id, tid, account, cur_msgs, cur_quota, init_time, last_time, last_notify_time FROM throttle_tracking WHERE %s """ % ' OR '.join(tracking_sql_where) logger.debug('[SQL] Query throttle tracking data: {}'.format(sql)) qr = conn.execute(sql) tracking_records = qr.fetchall() logger.debug('[SQL] Query result: {}'.format(tracking_records)) # `throttle.id`. syntax: {(tid, account): id} tracking_ids = {} for rcd in tracking_records: (_id, _tid, _account, _cur_msgs, _cur_quota, _init_time, _last_time, _last_notify_time) = rcd tracking_ids[(_tid, _account)] = _id if not _init_time: _init_time = now # Get special throttle setting name: msg_size, max_msgs, max_quota t_setting_account = t_setting_ids[_tid] for t_name in t_setting_keys.get((_tid, t_setting_account)): if t_name in t_settings: t_settings[t_name]['tracking_id'] = _id t_settings[t_name]['cur_msgs'] = _cur_msgs t_settings[t_name]['cur_quota'] = _cur_quota t_settings[t_name]['init_time'] = _init_time t_settings[t_name]['last_time'] = _last_time t_settings[t_name]['last_notify_time'] = _last_notify_time logger.debug('Tracking IDs: {}'.format(tracking_ids)) if 'msg_size' in t_settings: ts = t_settings['msg_size'] msg_size = ts['value'] _tracking_id = ts['tracking_id'] _period = int(ts.get('period', 0)) _init_time = int(ts.get('init_time', 0)) _last_time = int(ts.get('last_time', 0)) _last_notify_time = int(ts.get('last_notify_time', 0)) # Check message size if size > msg_size > 0: logger.info('[{}] [{}] Quota exceeded: {} throttle for ' 'msg_size, current: {} bytes. ' '({})'.format(client_address, user, throttle_type, size, throttle_info)) if (not _last_notify_time) or ( not (_init_time < _last_notify_time <= (_init_time + _period))): __sendmail(conn=conn, user=user, client_address=client_address, throttle_tracking_id=_tracking_id, throttle_name='msg_size', throttle_value=msg_size, throttle_kind=throttle_kind, throttle_info=throttle_info, throttle_value_unit='bytes') # Construct and send notification email try: _subject = 'Throttle quota exceeded: %s, mssage_size=%d bytes' % ( user, size) _body = '- User: '******'\n' _body += '- Throttle type: ' + throttle_kind + '\n' _body += '- Client IP address: ' + client_address + '\n' _body += '- Limit of single message size: %d bytes\n' % msg_size _body += '- Throttle setting(s): ' + throttle_info + '\n' utils.sendmail(subject=_subject, mail_body=_body) except Exception as e: logger.error( 'Error while sending notification email: {}'.format(e)) return SMTP_ACTIONS['reject_quota_exceeded'] else: # Show the time tracking record is about to expire _left_seconds = _init_time + _period - _last_time logger.info('[{}] {} throttle, {} -> msg_size ' '({}/{}, period: {} seconds, ' '{})'.format(client_address, throttle_type, user, size, msg_size, _period, utils.pretty_left_seconds(_left_seconds))) if 'max_msgs' in t_settings: ts = t_settings['max_msgs'] max_msgs = ts['value'] _cur_msgs = ts['cur_msgs'] _tracking_id = ts['tracking_id'] _period = int(ts.get('period', 0)) _init_time = int(ts.get('init_time', 0)) _last_time = int(ts.get('last_time', 0)) _last_notify_time = int(ts.get('last_notify_time', 0)) if _period and now > (_init_time + _period): logger.debug('Existing max_msg tracking expired, reset.') ts['expired'] = True _init_time = now _last_time = now _cur_msgs = 0 _requested_max_msgs = _cur_msgs + recipient_count if _requested_max_msgs >= max_msgs > 0: logger.info('[{}] [{}] Quota exceeded: {} throttle for ' 'max_msgs, recipient_count={}, {}->{}/{}. ' '({})'.format(client_address, user, throttle_type, recipient_count, _cur_msgs, _requested_max_msgs, max_msgs, throttle_info)) # Send notification email if matches any of: # 1: first exceed # 2: last notify time is not between _init_time and (_init_time + _period) if (not _last_notify_time) or ( not (_init_time < _last_notify_time <= (_init_time + _period))): __sendmail(conn=conn, user=user, client_address=client_address, throttle_tracking_id=_tracking_id, throttle_name='max_msgs', throttle_value=max_msgs, throttle_kind=throttle_kind, throttle_info=throttle_info) return SMTP_ACTIONS['reject_quota_exceeded'] else: # Show the time tracking record is about to expire _left_seconds = _init_time + _period - _last_time logger.info('[{}] {} throttle, {} -> max_msgs ' '({}->{}/{}, period: {} seconds, ' '{})'.format(client_address, throttle_type, user, _cur_msgs, _requested_max_msgs, max_msgs, _period, utils.pretty_left_seconds(_left_seconds))) if 'max_quota' in t_settings: ts = t_settings['max_quota'] max_quota = ts['value'] _cur_quota = ts.get('cur_quota', 0) _tracking_id = ts['tracking_id'] _period = int(ts.get('period', 0)) _init_time = int(ts.get('init_time', 0)) _last_time = int(ts.get('last_time', 0)) if _period and now > (_init_time + _period): # tracking record expired logger.info('Period of max_quota expired, reset.') ts['expired'] = True _init_time = now _last_time = now _cur_quota = 0 if _cur_quota > max_quota > 0: logger.info('[{}] [{}] Quota exceeded: {} throttle for ' 'max_quota, current: {}. ({})'.format( client_address, user, throttle_type, _cur_quota, throttle_info)) if (not _last_notify_time) or ( not (_init_time < _last_notify_time <= (_init_time + _period))): __sendmail(conn=conn, user=user, client_address=client_address, throttle_tracking_id=_tracking_id, throttle_name='max_quota', throttle_value=max_quota, throttle_kind=throttle_kind, throttle_info=throttle_info, throttle_value_unit='bytes') return SMTP_ACTIONS['reject_quota_exceeded'] else: # Show the time tracking record is about to expire _left_seconds = _init_time + _period - _last_time logger.info('[{}] {} throttle, {} -> max_quota ' '({}/{}, period: {} seconds, ' '{})'.format(client_address, throttle_type, user, _cur_quota, max_quota, _period, utils.pretty_left_seconds(_left_seconds))) # Update tracking record. # # SQL statements used to update tracking data if not rejected: # init_time, cur_msgs, cur_quota, last_time sql_inserts = [] # {tracking_id: ['last_time=xxx', 'init_time=xxx', ...]} sql_updates = {} for (_, v) in list(t_settings.items()): tid = v['tid'] for k in v['track_key']: if (tid, k) in tracking_ids: # Update existing tracking records tracking_id = tracking_ids[(tid, k)] if tracking_id not in sql_updates: sql_updates[tracking_id] = {'id': tracking_id} # Store period, used while cleaning up old tracking records. sql_updates[tracking_id]['period'] = v['period'] sql_updates[tracking_id]['last_time'] = now if v['expired']: sql_updates[tracking_id]['init_time'] = now sql_updates[tracking_id]['cur_msgs'] = recipient_count sql_updates[tracking_id]['cur_quota'] = size else: sql_updates[tracking_id]['init_time'] = v['init_time'] sql_updates[tracking_id][ 'cur_msgs'] = 'cur_msgs + %d' % recipient_count sql_updates[tracking_id][ 'cur_quota'] = 'cur_quota + %d' % size else: # no tracking record. insert new one. # (tid, account, cur_msgs, period, cur_quota, init_time, last_time) if not (tid, k) in sql_inserts: _sql = '(%d, %s, %d, %d, %d, %d, %d)' % (tid, sqlquote( k), recipient_count, v['period'], size, now, now) sql_inserts.append(_sql) if sql_inserts: sql = """INSERT INTO throttle_tracking (tid, account, cur_msgs, period, cur_quota, init_time, last_time) VALUES """ sql += ','.join(set(sql_inserts)) logger.debug('[SQL] Insert new tracking record(s): {}'.format(sql)) conn.execute(sql) for (_tracking_id, _kv) in list(sql_updates.items()): _sql = """UPDATE throttle_tracking SET period={}, last_time={}, init_time={}, cur_msgs={}, cur_quota={} WHERE id={}""".format(_kv['period'], _kv['last_time'], _kv['init_time'], _kv['cur_msgs'], _kv['cur_quota'], _tracking_id) logger.debug('[SQL] Update tracking record: {}'.format(_sql)) conn.execute(_sql) logger.debug('[OK] Passed all {} throttle settings.'.format(throttle_type)) return SMTP_ACTIONS['default']
active=1, ) # # no_greylisting settings # logger.info('* Migrate per-domain and per-user no-greylisting settings.') logger.info('\t- Query no-greylisting settings') qr = conn_cb.select(['policy_groups', 'policy_group_members'], what='policy_group_members.member AS member', where="policy_groups.name='no_greylisting_for_internal' AND policy_group_members.policygroupid=policy_groups.id") for r in qr: _account = str(r.member) _account_type = is_valid_amavisd_address(_account) if _account_type: _priority = ACCOUNT_PRIORITIES[_account_type] else: continue try: conn_iredapd.insert( 'greylisting', account=r.member, priority=_priority, sender='@.', sender_priority=0, active=1, ) logger.info("\t+ Migrated account setting: {}".format(_account))