def install(self): _sql = ''' CREATE TABLE greylist_tracking ( id SERIAL PRIMARY KEY, sender text, sender_address text, recipient text, first_seen timestamp DEFAULT NOW(), UNIQUE(sender, sender_address, recipient) ); CREATE INDEX greylist_track_sender_idx ON greylist_tracking(sender); CREATE INDEX greylist_track_sender_addr_idx ON greylist_tracking(sender_address); CREATE TABLE greylist_sender_awl ( id SERIAL PRIMARY KEY, sender_domain text, sender_address text, first_seen timestamp, last_seen timestamp, UNIQUE(sender_domain, sender_address) ); CREATE TABLE greylist_sender_wl ( id SERIAL PRIMARY KEY, sender text UNIQUE -- domain or IP address ); CREATE TABLE greylist_recipient_wl ( id SERIAL PRIMARY KEY, recipient text UNIQUE -- email or domain ); ''' _sql_drop = '''DROP TABLE greylist_recipient_wl, greylist_sender_wl, greylist_sender_awl, greylist_tracking; ''' db_name = self.conf.get('database', 'plugin:greylist', 'default', mandatory=False) db = connect(self.conf, db_name) cur = cursor(db) cur.execute(_sql_drop) db.commit() cur.execute(_sql) db.commit() db.close()
def __call__(self, request): conf = self.conf db_name = conf.get('database', 'plugin:quota', 'default', mandatory=False) db = connect(conf, db_name) if not db: return PolicyResponse().dunno() try: quota_keys = { 'sasl_username': request['sasl_username'], 'sasl_sender': request['sasl_sender'], 'sender': request['sender'], 'sender_domain': None, 'recipient': request['recipient'], 'recipient_count': request['recipient_count'], 'recipient_domain': request['recipient'].rsplit('@', 1)[1], 'client_address': request['client_address'], 'size': request['size'], } except (KeyError, IndexError): error('Missing policy request attribute') return PolicyResponse().dunno() if '@' in request['sasl_username']: quota_keys['sender_domain'] = request['sasl_username'].rsplit('@', 1)[1] elif '@' in request['sasl_sender']: quota_keys['sender_domain'] = request['sasl_sender'].rsplit('@', 1)[1] else: quota_keys['sender_domain'] = request['sender'].rsplit('@', 1)[1] hd = hosted_domains() sd = quota_keys['sender_domain'] rd = quota_keys['recipient_domain'] if conf.get('ignore_local', 'plugin:quota') == 'true' and \ (sd == rd or ((sd in hd) and (rd in hd))): return PolicyResponse().dunno() c = cursor(db) action = None for item in self.targeted_policies(c, quota_keys): if item['tracking_id'] is None: self.new_quota_tracking_item(db, c, item, quota_keys) resp = self.quota_for(db, c, item, quota_keys) action = resp.action if action == 'reject': return resp # Stop processing if we've passed positive targeted policies if action: return resp # Maybe our user doesn't exist in quota table? for item in self.new_default_policies(c): self.new_quota_tracking_item(db, c, item, quota_keys) # Process default policies (if any) for item in self.default_policies(c, quota_keys): resp = self.quota_for(db, c, item, quota_keys) if resp.action == 'reject': return resp return PolicyResponse().dunno()
def __call__(self, request): conf = self.conf db_name = conf.get('database', 'plugin:greylist', 'default', mandatory=False) db = connect(conf, db_name) resp = PolicyResponse() if not db: return resp.dunno() try: gk = { 'sender': request['sender'], 'sender_domain': request['sender'].rsplit('@', 1)[1], 'recipient': request['recipient'], 'client_address': request['client_address'], } except (KeyError, IndexError): error('Missing policy request attribute') return resp.dunno() # Don't greylist SASL authenticated users if 'sasl_username' in request and '@' in request['sasl_username']: return resp.dunno() c = cursor(db) # Client whitelist c.execute('''SELECT id FROM greylist_sender_wl WHERE sender=%s OR sender=%s''', (gk['sender_domain'], gk['client_address'])) if c.rowcount > 0: resp.prepend('X-Greylist', 'Whitelisted by djmd') return resp.accept() # Auto whitelisted (per domain) c.execute('''SELECT id FROM greylist_sender_awl WHERE sender_domain=%s AND sender_address=%s''', (gk['sender_domain'], gk['client_address'])) if c.rowcount > 0: c.execute('''UPDATE greylist_sender_awl SET last_seen=NOW() WHERE sender_domain=%s AND sender_address=%s''', (gk['sender_domain'], gk['client_address'])) db.commit() resp.prepend('X-Greylist', 'AWL by djmd') return resp.accept() # New connection or second connection (AWL candidate) try: c.execute('''INSERT INTO greylist_tracking(sender, sender_address, recipient) VALUES(%s, %s, %s)''', (gk['sender'], gk['client_address'], gk['recipient'])) db.commit() except IntegrityError as e: db.rollback() c.execute('''SELECT first_seen, (extract(epoch from (NOW() - first_seen)))::integer as elapsed_time FROM greylist_tracking WHERE sender=%s AND sender_address=%s AND recipient=%s''', (gk['sender'], gk['client_address'], gk['recipient'])) gt = c.fetchone() greylist_delay = int(conf.get('greylist_delay', 'plugin:greylist')) if gt[1] > greylist_delay: try: c.execute('''INSERT INTO greylist_sender_awl(sender_domain, sender_address, first_seen, last_seen) VALUES(%s, %s, %s, NOW())''', (gk['sender_domain'], gk['client_address'], gt[0])) db.commit() except IntegrityError as e: # Already whitelisted, move on db.rollback() # AWL resp.prepend('X-Greylist', 'AWL by djmd') return resp.accept() return resp.reject(msg=conf.get('msg', 'plugin:greylist'))