# according to odbx_example.sql

# check these:
db_backend mysql
db_host /var/run/mysqld/mysqld.sock

db_opt_multi_statements

db_database test_zfilter
db_user zfilter
# (end installation dependent)

##########################################################
# The following settings can be tweaked or changed at will
# yet, doing so requires some thought and some testing.
# See zfilter_db(1) man page.

# cumbersome expression to have shoot-on-sight work as intended
db_sql_domain_flags SELECT whitelisted, add_dmarc, add_adsp, 1\
 FROM domain WHERE domain = '$(domain)'

# using IF to set NULL for empty vars
# prefix_len is empty if domain is not aligned or there is no org_domain
# dmarc_{ri,rua,rec} are empty except for the dmarc domain
db_sql_select_domain CALL recv_from_domain(\
 '$(domain)',\
 $(dkim_result), \
 IF(ASCII('$(prefix_len)'), '$(prefix_len)', NULL),\
 IF(ASCII('$(dmarc_ri)'), '$(dmarc_ri)', NULL),\
 IF(ASCII('$(original_ri)'), '$(original_ri)', NULL),\
 IF(ASCII('$(dmarc_rec)'), '$(dmarc_rec)', NULL),\
 IF(ASCII('$(dmarc_rua)'), '$(dmarc_rua)', NULL))

db_sql_insert_msg_ref INSERT INTO msg_ref SET \
 message_in = $(message_ref), \
 domain = $(domain_ref), \
 auth = ('$(auth_type)'), \
 dkim = '$(dkim_result)', \
 dkim_trans = $(dkim_trans), \
 dkim_order = $(dkim_order), \
 dkim_selector = '$(dkim_selector)', \
 spf = '$(spf_result)', \
 reputation = $(reputation), \
 vbr = IF(STRCMP('$(vbr_mv)', 'dwl.spamhaus.org') = 0, '(spamhaus)', '()');

db_sql_insert_message INSERT INTO message_in SET\
 ino = 0x$(ino),\
 mtime = 0x$(mtime),\
 pid = 0x$(pid),\
 ip = X'$(ip)',\
 date = '$(date)',\
 message_id = '$(message_id)',\
 dmarc_dkim = '$(dmarc_dkim)',\
 dmarc_spf = '$(dmarc_spf)',\
 dmarc_reason = '$(dmarc_reason)',\
 dmarc_dispo =  '$(dmarc_dispo)',\
 envelope_sender = '$(envelope_sender)',\
 content_type = '$(content_type)',\
 content_encoding = '$(content_encoding)',\
 received_count = $(received_count),\
 signatures_count = $(signatures_count),\
 mailing_list = $(mailing_list); SELECT LAST_INSERT_ID()

db_sql_select_user CALL sent_message('$(local_part)@$(domain)',\
 0x$(ino),\
 0x$(mtime),\
 0x$(pid),\
 0x$(ip),\
 '$(date)',\
 '$(message_id)',\
 '$(envelope_sender)',\
 '$(content_type)',\
 '$(content_encoding)',\
 $(rcpt_count))

db_sql_insert_target_ref CALL sent_to_domain($(message_ref), $(complaint_flag), '$(domain)')

db_sql_check_user SELECT IF ((SELECT SUM(rcpt_count)\
 FROM message_out WHERE user = $(user_ref) AND\
 mtime > UNIX_TIMESTAMP(NOW() - INTERVAL 1 DAY)) > \
 (SELECT rcpt_max_perday FROM user WHERE id = $(user_ref)), \
 'Block this guy', 0) AS reason
 
# find domains that may need a dmarc report.
# we select domains such that:
# they have dmarc_ri > 0 (that is, have a rua and want reports),
# the last report was generated earlier than dmarc_ri seconds ago, and
# they sent messages after that report or at least in the last 24 hours.

db_sql_dmarc_agg_domain SELECT id, domain, last_report, dmarc_ri, dmarc_rua, dmarc_rec\
 FROM domain\
 WHERE dmarc_ri > 0 AND\
 last_report <= $(period_end) - dmarc_ri AND\
 last_recv > last_report AND\
 last_recv > $(period_end) - 86400


# find aggregate authentication results of a given dmarc domain (rd.domain)
log_dkim_order_above 4
db_sql_dmarc_agg_record SELECT INET_NTOA(CONV(HEX(m.ip),16,10)) AS source, COUNT(DISTINCT(m.id)) AS n,\
m.dmarc_dispo AS disposition, m.dmarc_dkim AS d_dkim, m.dmarc_spf AS d_spf,\
m.dmarc_reason AS reason, da.domain AS author,\
dspf.domain AS spf, rspf.spf AS spf_re,\
dhelo.domain AS helo, rhelo.spf AS helo_re,\
d1.domain AS dkim1, r1.dkim_selector AS dkim1_se, r1.dkim AS dkim1_re, r1.dkim_trans AS dkim1_t,\
d2.domain AS dkim2, r2.dkim_selector AS dkim2_se, r2.dkim AS dkim2_re, r2.dkim_trans AS dkim2_t,\
d3.domain AS dkim3, r3.dkim_selector AS dkim3_se, r3.dkim AS dkim3_re, r3.dkim_trans AS dkim3_t,\
d4.domain AS dkim4, r4.dkim_selector AS dkim4_se, r4.dkim AS dkim4_re, r4.dkim_trans AS dkim4_t\
FROM message_in AS m\
LEFT JOIN (msg_ref AS rd INNER JOIN domain AS dd ON rd.domain = dd.id)\
  ON m.id = rd.message_in AND FIND_IN_SET('dmarc', rd.auth)\
LEFT JOIN (msg_ref AS ra INNER JOIN domain AS da ON ra.domain = da.id)\
  ON m.id = ra.message_in AND FIND_IN_SET('author', ra.auth)\
LEFT JOIN (msg_ref AS rspf INNER JOIN domain AS dspf ON rspf.domain = dspf.id)\
  ON m.id = rspf.message_in AND FIND_IN_SET('spf', rspf.auth)\
LEFT JOIN (msg_ref AS rhelo INNER JOIN domain AS dhelo ON rhelo.domain = dhelo.id)\
  ON m.id = rhelo.message_in AND FIND_IN_SET('spf_helo', rhelo.auth)\
LEFT JOIN (msg_ref AS r1 INNER JOIN domain AS d1 ON r1.domain = d1.id)\
  ON m.id = r1.message_in AND r1.dkim_order = 1\
LEFT JOIN (msg_ref AS r2 INNER JOIN domain AS d2 ON r2.domain = d2.id)\
  ON m.id = r2.message_in  AND r2.dkim_order = 2\
LEFT JOIN (msg_ref AS r3 INNER JOIN domain AS d3 ON r3.domain = d3.id)\
  ON m.id = r3.message_in AND r3.dkim_order = 3\
LEFT JOIN (msg_ref AS r4 INNER JOIN domain AS d4 ON r4.domain = d4.id)\
  ON m.id = r4.message_in  AND r4.dkim_order = 4\
WHERE rd.domain = $(domain_ref) AND $(period_start) <= m.mtime AND m.mtime < $(period_end)\
GROUP BY source, disposition, d_dkim, d_spf, reason, author,\
 spf, spf_re, helo, helo_re, dkim1, dkim1_se, dkim1_re,\
 dkim2, dkim2_se, dkim2_re, dkim3, dkim3_se, dkim3_re, dkim4, dkim4_se, dkim4_re

# check recipient address
db_sql_check_dmarc_rcpt SELECT COUNT(*) AS blocked FROM dmarc_bounce \
WHERE addr = '$(dmarc_rcpt)' AND DATE_ADD(since, INTERVAL days DAY) > NOW();

# update last report
db_sql_set_dmarc_agg UPDATE domain SET last_report = $(period_end) WHERE id = $(domain_ref)

honored_report_interval 300
verbose 9