Example files serve as a guide, you may want to arrange and complete as needed. The postinst script implements the following guide for Debian install.
1. Create MySQL database
For example, in an interactive, privileged mysql session:
CREATE DATABASE IF NOT EXISTS test_zfilter;
CREATE USER IF NOT EXISTS zfilter;
GRANT SELECT, INSERT, UPDATE, EXECUTE, DELETE, DROP, CREATE, ALTER ROUTINE, CREATE ROUTINE ON test_zfilter.* TO 'zfilter'@'localhost'
And then:
mysql -u zfilter < odbx_example.sql
To use IPv6, change field ip in tables message_in and message_out.
(see comments in odbx_example.sql)
That sql uses the test database. You may want to create a permanent
one when you're done testing.
2. Edit zdkimfilter.conf
cat odbx_example.conf >> zdkimfilter.conf
Change DB-specific configuration items such as db_user and db_password. They are documented in zfilter_db(1) rather than zdkimfilter.conf(5).
3. Set up DMARC aggregate reports
Reporting is an integral part of DMARC. Edit zaggregate-example.sh, changing at least ORG_* settings, and save it in a place suitable to be executed daily. The script calls zdkimsign, so it needs permissions to read zdkim config file and private key(s).
Please realize that the script consists of a single pipeline, albeit longish. The longest part of it is a printf template for the actual report. Be careful with newlines. If you work this script before step 2 above, you can use odbx_example.sh to generate sample data for manually testing the script offline.
See zaggregate(1) for more information.
4. Schedule database clean up
Two queries starting with "DELETE r, m FROM..." are given in odbx_example.sql
to delete old incoming and outgoing message data, respectively. They need to
be actually called by a cron script, in order to get rid of useless details.
Tables domain and user are designed to grow indefinitely. Use your wit to get worthwhile reports from that data.
Hover or click on picture to get tables or fields descriptions.
Each table has a numeric primary key called id. Foreign key fields are named after the referred table name, and store the id of the referred record. For the sake of simplicity, the example declares no referential relationship and requires no referential integrity constraint.
odbx_example.sql creates the following tables:
This is the focal data table. A record is stored after each domain name authentication or attempt thereof. In addition, if enabled, the administrative domain name (ADMD) is also stored. The table structure is flat, ignoring the lure of fully normalizing it according to delegations.
The example includes a few data fields, but does not yet support a decent statistical treatment. This table is the only "precious" piece of collected data, in the sense that it may be worth to avoid purging records older than one week or month.
A cross-reference table between domain and message_in, which are linked by a many-to-many relationship. Each record corresponds to a form of authentication of the given domain in the given message.
Incoming messages; that is, checked rather than signed by this filter. One record per message.
A cross-reference table between domain and user, through message_out, which are linked by a many-to-many relationship. Each record corresponds to a target domain of a given message submitted by an authenticated user.
Outgoing messages; that is, possibly signed by this filter. One record per message.
Local users. Each record represents an authenticated user who submitted a message. It may or may not be the same table used for user authentication; the example adds records as needed. This table can be used to implement per-user daily limits on outgoing messages.
Add to the global honor_author_domain setting. Like the previous item, this is added (with sign) to the global value.
Unless domain matches the From: address, this result is discarded.
This item is the third result from query db_sql_domain_flags, see Per-domain settings.
Add to the global honor_dmarc setting. If DMARC is globally honored, a value of -1 disables it for this domain. A value of 1 enables it for this message, even if DMARC is not globally enabled.
Unless domain is aligned with org_domain, this result is discarded.
This item is the second result from query db_sql_domain_flags, see Per-domain settings.
A rewritten digest of the DMARC record, used for aggregate reports. Targets
and interval are moved to their own variables, so this digest shouldn't take
more than 60 bytes, including the appended sentinel (see dmarc_rua below).
A multiple of original_ri that matches the honored_report_interval.
A rewritten subset of aggregate report targets. Only mailto: targets are
considered, and stored as a list of mail addresses.
A sentinel is appended to the list, to know if excessive length caused truncation. In that case, the DMARC record is looked up anew. Even if DB queries are usually faster than DNS ones, reserving too much room might degrade performance, so a compromise length that fits most domains could be preferred.
The full domain name, presumably in ASCII.
This is an auto incremented numeric primary key. It is used to unambiguously refer to records of this table.
The actual time when an incoming message from this domain was filtered.
Updated by stored procedure recv_from_domain, it is used to select candidate DMARC record targets in db_sql_dmarc_agg_domain.
The end-of-period of the last DMARC report sent to this domain.
Updated by in db_sql_set_dmarc_agg, it is used to select candidate DMARC record targets in db_sql_dmarc_agg_domain.
The actual time when an outgoing message to this domain was filtered.
Updated by stored procedure sent_to_domain.
The Report Interval requested by the domain.
Updated by stored procedure recv_from_domain.
The number of bytes that must be removed from the domain name in order to obtain the organizational domain name, as resulting from the Public Suffix List.
Updated by stored procedure recv_from_domain, it grows indefinitely.
Updated by stored procedure sent_to_domain, it grows indefinitely.
Set on record creation, this field is never modified.
This is the internal whitelisting, not to be conflated with dnswl.
A negative value, -1, triggers shoot_on_sight mode. The message is rejected "for policy reasons", whether the domain is authenticated or not, unless overridden by another whitelisting (see whitelisted_pass below) or by dnswl (greater or equal to dnswl_worthiness_pass).
A value of 0 is ignored.
A value of 1 or higher affects the order in which DKIM signatures verification is attempted and reported. This value can be increased automatically to 1 when a DKIM signature of the domain verifies successfully, to 2 when sending to a domain from a personal address (not an rfc2142 address.)
When this value is greater or equal to the whitelisted_pass configured value, it prevents a message from being rejected or dropped due to DMARC or ADSP policy, shoot_on_sight or action_header. The domain needs to be authenticated and not flagged shoot_on_sight itself.
This item is the first result from query db_sql_domain_flags, see Per-domain settings.
Set in db_sql_insert_message after the value found in the incoming message. See Traits and variables common to incoming and outgoing messages.
Value of Content-Type truncated at the semicolon.
Set in db_sql_insert_message. See Traits and variables common to incoming and outgoing messages.
String value of header field Date:
Set in db_sql_insert_message. See Traits and variables common to incoming and outgoing messages.
The message disposition, as far as DMARC is concerned. It is not necessarily consistent with message_status. The value is one of:
none
No policy impact on message disposition.
quarantine
The message is treated "as suspicious", to use RFC 7489 words. (All what zdkimfilter does is to write "(QUARANTINE)" in the Authentication-Results. Any enforcement is passed on to mail delivery.)
reject
The message is rejected because of remote DMARC policy.
Set in db_sql_insert_message, it is reported to domains accepting aggregate reports.
DMARC view of DKIM validation of the whole message, it is one of:
pass
if a signature by an aligned domain passed.
fail
if no aligned signature passed.
none
if no aligned signature or no DMARC record were found.
Set in db_sql_insert_message, it is reported to domains accepting aggregate reports.
Currently, zdkimfilter sets this value as follows:
none
dmarc_dispo is in agreement with the remote DMARC policy.
forwarded
Not used.
sampled_out
Remote DMARC policy applies to a percentage only, and the luck of the draw exempts this message.
trusted_forwarder
An authenticated domain is whitelisted >= whitelisted_pass or is in DNSWL >= dnswl_worthiness_pass.
mailing_list
MLM transformation applied and at least one signature was recovered that way.
local_policy
None if DMARC, ADSP, or reject_on_nxdomain applies. Remote DMARC policy is not being honored for this message.
other
DMARC policy failed, but BOFHSPFFROM passed. Note that SPF protocol does not
provide a scope for From:; however, it may happen that a message has no
signature, an empty envelope sender, a mismatched BOFHSPFHELO, but a good
BOFHSPFFROM.
Set in db_sql_insert_message, it is reported to domains accepting aggregate reports.
DMARC view of SPF authentication, it is one of:
pass
if a domain is authenticated with either BOFHSPFHELO or BOFHSPFMAILFROM and it is aligned in the way (relaxed or strict) specified by the DMARC record of the author's domain.
fail
if SPF authentication did not pass, or the authenticated domain was not aligned as required.
none
if no DMARC record was found.
Set in db_sql_insert_message, it is reported to domains accepting aggregate reports.
Envelope sender email address. It is empty for bounce messages. It gets truncated when it is longer than the field length.
Set in db_sql_insert_message. See Traits and variables common to incoming and outgoing messages.
Inode number. See Traits and variables common to incoming and outgoing messages
The IP number of the sending relay. It is either read from Received: or
from REMOTE_ADDR.
1 or 0, according the presence of mailing list header fields. Set in db_sql_insert_message. See Traits and variables common to incoming and outgoing messages.
String value of Message-Id.
Set in db_sql_insert_message. See Traits and variables common to incoming and outgoing messages.
Unix epoch. See Traits and variables common to incoming and outgoing messages
Process id. See Traits and variables common to incoming and outgoing messages
The number of Received: header fields.
It's there for content filters which can determine the spamminess of received messages.
Neither set nor used by zdkim.
The number of DKIM signatures.
Set by stored procedure sent_message after the value found in the outgoing message. See Traits and variables common to incoming and outgoing messages.
Value of Content-Type truncated at the semicolon.
Set by stored procedure sent_message. See Traits and variables common to incoming and outgoing messages.
String value of header field Date:
Set by stored procedure sent_message. See Traits and variables common to incoming and outgoing messages.
String value of header field Date:
Set by stored procedure sent_message. See Traits and variables common to incoming and outgoing messages.
String value of Message-Id.
Set by stored procedure sent_message. See Traits and variables common to incoming and outgoing messages.
The number of envelope recipients that the message is to be sent to.
Set by stored procedure sent_message, it is used in db_sql_check_user to limit per user sending. See Storing outgoing messages.
The default value in odbx_example.conf is too high to cover the risk of a user's identity being stolen by spammers. Blocking can be tailored on a per user basis, possibly allowing users to schedule exceptions using web forms.
Matches user.id of the corresponding record.
Matches message_out.id of the corresponding record.
Sometimes called auth_type, it is the relationship(s) between message and domain, as claimed by the use of a domain name in a message. In case publicsuffix and save_from_anyway are both disabled --not a recommended setting-- only authenticated relationships are flagged.
Relationship flags are expressed as a comma separated list of the following tokens:
author
The author domain is the one appearing in the From: field.
spf_helo, spf, and dkim
The corresponding authentication methods, whose results are given in separate variables. SPF results originate from Courier's BOFHSPFHELO and BOFHSPFMAILFROM.
org
The domain is the organizational domain of the author domain, retrieved using the Public Suffix List.
dmarc
The domain where the DMARC record was retrieved from.
aligned
The domain is aligned with with the author domain, in the sense that they share the same organizational domain.
dnswl
The domain is whitelisted in the configured zone. This result originates from the -allow option of couriertcpd. See couriertcpd(8).
nx
The domain, either the author domain or the organizational domain thereof, does not actually exist because NXDOMAIN was returned on querying its Name Servers.
For example, to count what combinations of relationships exist for organizational domains only, one can query:
SELECT COUNT(*) as cnt, auth FROM msg_ref WHERE FIND_IN_SET('org', auth) GROUP BY auth ORDER BY cnt DESC;
Sometimes called dkim_result, it is the verification result proper, one of none, pass, fail, policy, neutral, temperror, permerror. Note that a variable with the same name but different content is available in the domain calls.
The 1-based position of this domain's signature(s), preferred domains first.
Ordering is done before verifying signatures, and it is reported to domains which accept DMARC aggregate reports.
The signature selector, s= tag.
A tiny integer that is non-zero if a signature was recovered after undoing a mailing list manager transformation.
Aggregate reports can output a human_result like
through MLM transformation when this datum is present for a signature.
Matches domain.id of the corresponding record.
Matches message_in.id of the corresponding record.
Sometimes called spf_result, it is the SPF result, if the domain appears in any of helo, mailfrom, or From:. SPF results originate from Courier's BOFHSPFHELO and BOFHSPFMAILFROM.
User's email address. Set by stored procedure sent_message after variables local_part and domain.