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 install(self): '''Create database schema for Quota plugin''' _sql = ''' CREATE TYPE quota_key AS ENUM ( 'sasl_username', 'sasl_sender', 'sender', 'sender_domain', 'recipient', 'recipient_count', 'recipient_domain', 'client_address', 'size' ); CREATE TYPE quota_limit AS ENUM ( 'emails', 'recipients', 'size' ); CREATE TABLE quotas ( id SERIAL PRIMARY KEY, name text NOT NULL UNIQUE, description text, interval_ integer, -- interval in seconds limit_type quota_limit NOT NULL, limit_ integer NOT NULL, msg text ); CREATE TABLE quota_members ( id SERIAL PRIMARY KEY, quota_id integer REFERENCES quotas(id), key_type quota_key NOT NULL, key text NOT NULL, UNIQUE(quota_id, key_type, key) ); CREATE TABLE quota_tracking ( id SERIAL PRIMARY KEY, quota_id integer references quotas(id) NOT NULL, key_type quota_key NOT NULL, key text NOT NULL, counter double precision, last_seen timestamp DEFAULT now(), UNIQUE(quota_id, key_type, key) ); ''' _sql_data = ''' INSERT INTO quotas(name, description, interval_, limit_type, limit_, msg) VALUES ('100/hour', 'Hourly quota for outgoing email', 3600, 'emails', 100, 'You have reached your hourly limit for sending email.'), ('1000/day', 'Daily quota for outgoing email', 86400, 'emails',1000, 'You have reached your daily limit for sending email'), ('5000/week', 'Weekly quota for outgoing email', 604800,'emails', 5000, 'You have reached your weekly limit for sending email'), ('10000/month', 'Monthly quota for outgoing email', 2592000, 'emails', 10000, 'You have reached your monthly limit for sending email') ; INSERT INTO quota_members(quota_id, key_type, key) VALUES (1, 'sasl_username', '*'), (2, 'sasl_username', '*'), (3, 'sasl_username', '*'), (4, 'sasl_username', '*') ; -- DO NOT assign "real" members to defalt quota rules --- e.g. -- INSERT INTO quota_members(quota_id, key_type, key) -- VALUES(1, 'sasl_username', '*****@*****.**'); --- ^^^ don't do this ^^^ ''' _sql_drop = ''' DROP TABLE IF EXISTS quota_tracking, quota_members, quotas; DROP TYPE quota_key; DROP TYPE quota_limit; ''' db_name = self.conf.get('database', 'plugin:quota', 'default', mandatory=False) db = connect(self.conf, db_name) cur = db.cursor() cur.execute(_sql_drop) db.commit() cur.execute(_sql) cur.execute(_sql_data) db.commit() cur.close() db.close()
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'))