-
-
Notifications
You must be signed in to change notification settings - Fork 1
/
odbx_example-1.6.conf
124 lines (107 loc) · 4.62 KB
/
odbx_example-1.6.conf
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
# 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
db_sql_whitelisted SELECT whitelisted FROM domain WHERE domain = '$(domain)'
db_sql_domain_flags SELECT MAX(whitelisted), MAX(add_dmarc), MAX(add_adsp)\
FROM domain WHERE domain = '$(domain)' OR domain = '$(org_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_insert_msg_ref CALL recv_from_domain(\
$(message_ref),\
'$(domain)',\
('$(auth_type)'),\
'$(dkim_result)',\
$(dkim_order),\
'$(spf_result)',\
'$(vbr_mv)',\
$(reputation),\
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_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(*) 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 AS dkim1_re,\
d2.domain AS dkim2, r2.dkim AS dkim2_re,\
d3.domain AS dkim3, r3.dkim AS dkim3_re,\
d4.domain AS dkim4, r4.dkim AS dkim4_re\
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_re, dkim2, dkim2_re, dkim3, dkim3_re, dkim4, dkim4_re
# 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