home olddb
documentation:zdkimfilter zdkimsign redact zaggregate zfilter_db zdkimfilter.conf

Database

user INT UNSIGNED NOT NULL COMMENT 'Foreign key to user',

Matches user.id of the corresponding record.

table domain

dmarc_ri MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,

A multiple of original_ri that matches the honored_report_interval.

content_encoding VARCHAR(63) NOT NULL DEFAULT '7bit',

Set in db_sql_insert_message after the value found in the incoming message. See Traits and variables common to incoming and outgoing messages.

table msg_ref

add_adsp TINYINT NOT NULL DEFAULT 0,

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.

sent INT UNSIGNED NOT NULL DEFAULT 0,

Updated by stored procedure sent_to_domain, it grows indefinitely.

last_sent INT UNSIGNED NOT NULL DEFAULT 0,

The actual time when an outgoing message to this domain was filtered.

Updated by stored procedure sent_to_domain.

domain VARCHAR(63) NOT NULL,

The full domain name, presumably in ASCII.

pid INT UNSIGNED NOT NULL,

Process id. See Traits and variables common to incoming and outgoing messages

score SMALLINT DEFAULT NULL COMMENT 'NULL if not tested',

It's there for content filters which can determine the spamminess of received messages.

Neither set nor used by zdkim.

message_in INT UNSIGNED NOT NULL COMMENT 'Foreign key to message_in',

Matches message_in.id of the corresponding record.

id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,

This is an auto incremented numeric primary key. It is used to unambiguously refer to records of this table.

days INT UNSIGNED NOT NULL DEFAULT 40 COMMENT 'quarantine period'
content_type VARCHAR(63) NOT NULL DEFAULT 'text/plain',

Value of Content-Type truncated at the semicolon.

Set by stored procedure sent_message. See Traits and variables common to incoming and outgoing messages.

id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,

This is an auto incremented numeric primary key. It is used to unambiguously refer to records of this table.

dmarc_dispo ENUM ('none', 'quarantine', 'reject') NOT NULL DEFAULT 'none',

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.

recv INT UNSIGNED NOT NULL DEFAULT 0,

Updated by stored procedure recv_from_domain, it grows indefinitely.

add_dmarc TINYINT NOT NULL DEFAULT 0,

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.

mtime INT UNSIGNED NOT NULL,

Unix epoch. See Traits and variables common to incoming and outgoing messages

signatures_count SMALLINT UNSIGNED NOT NULL,

The number of DKIM signatures.

dkim_trans TINYINT UNSIGNED NOT NULL DEFAULT 0,

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.

dkim_selector VARCHAR(63) NOT NULL DEFAULT '',

The signature selector, s= tag.

message_id VARCHAR(63),

String value of Message-Id.

Set in db_sql_insert_message. See Traits and variables common to incoming and outgoing messages.

ino INT UNSIGNED NOT NULL,

Inode number. See Traits and variables common to incoming and outgoing messages

ip BINARY(4) NOT NULL COMMENT 'Ok for IPv4. For IPv6 use VARBINARY(16)',

The IP number of the sending relay. It is either read from Received: or from REMOTE_ADDR. Since it is a binary field, you have to resort to a function in order to display it. For example:

SELECT COUNT(*) AS cnt, INET_NTOA(CONV(HEX(ip),16,10)) AS submit from message_out GROUP BY IP ORDER BY cnt DESC LIMIT 10;

table dmarc_bounce

id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,

This is an auto incremented numeric primary key. It is used to unambiguously refer to records of this table.

spamtrap INT UNSIGNED NOT NULL DEFAULT 0,
dmarc_dkim ENUM ('none', 'fail', 'pass') DEFAULT 'none',

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.

rcpt_max_perday MEDIUMINT UNSIGNED NOT NULL DEFAULT 10000,

table message_in

message_id VARCHAR(63),

String value of Message-Id.

Set by stored procedure sent_message. See Traits and variables common to incoming and outgoing messages.

domain INT UNSIGNED NOT NULL COMMENT 'Foreign key to domain',

Matches domain.id of the corresponding record.

id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,

This is an auto incremented numeric primary key. It is used to unambiguously refer to records of this table.

addr VARBINARY(320) NOT NULL PRIMARY KEY,
spamtrap TINYINT NOT NULL DEFAULT 0,
content_type VARCHAR(63) NOT NULL DEFAULT 'text/plain',

Value of Content-Type truncated at the semicolon.

Set in db_sql_insert_message. See Traits and variables common to incoming and outgoing messages.

envelope_sender VARCHAR(63) NOT NULL DEFAULT '',

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.

dmarc_rua VARCHAR(64) NOT NULL DEFAULT '',

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.

id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,

This is an auto incremented numeric primary key. It is used to unambiguously refer to records of this table.

id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,

This is an auto incremented numeric primary key. It is used to unambiguously refer to records of this table.

dkim_order TINYINT UNSIGNED NOT NULL DEFAULT 0,

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.

date VARCHAR(63),

String value of header field Date:

Set by stored procedure sent_message. See Traits and variables common to incoming and outgoing messages.

since TIMESTAMP NOT NULL DEFAULT NOW(),

Set on record creation, this field is never modified.

table message_out

date VARCHAR(63),

String value of header field Date:

Set in db_sql_insert_message. See Traits and variables common to incoming and outgoing messages.

mailing_list TINYINT NOT NULL DEFAULT 0,

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.

table user

rcpt_count INT UNSIGNED NOT NULL DEFAULT 1,

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.

content_encoding VARCHAR(63) NOT NULL DEFAULT '7bit',

Set by stored procedure sent_message after the value found in the outgoing message. See Traits and variables common to incoming and outgoing messages.

envelope_sender VARCHAR(63) NOT NULL DEFAULT '',

String value of header field Date:

Set by stored procedure sent_message. See Traits and variables common to incoming and outgoing messages.

since TIMESTAMP NOT NULL DEFAULT NOW() COMMENT 'bounce date',
pid INT UNSIGNED NOT NULL,

Process id. See Traits and variables common to incoming and outgoing messages

auth SET ('author', 'spf_helo', 'spf', 'dkim', 'org', 'dmarc', 'aligned', 'vbr', 'rep', 'rep_s', 'dnswl', 'nx') NOT NULL,

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.

vbr

The seldom used Vouch By Reference. This relationship is flagged only if the vouch was verified according to trusted_vouchers.

rep and rep_s

For domains involved in the seemingly defunct DKIM reputation, as subjects and/or signers respectively.

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;

reputation INT NOT NULL,

The integer that could be found if DKIM-reputation queries worked.

ip BINARY(4) NOT NULL COMMENT 'Ok for IPv4. For IPv6 use VARBINARY(16)',

The IP number of the sending relay. It is either read from Received: or from REMOTE_ADDR. Since it is a binary field, you have to resort to a function in order to display it. For example:

SELECT COUNT(*) AS cnt, INET_NTOA(CONV(HEX(ip),16,10)) AS submit from message_out GROUP BY IP ORDER BY cnt DESC LIMIT 10;

last_report INT UNSIGNED NOT NULL DEFAULT 0,

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.

message_out INT UNSIGNED NOT NULL COMMENT 'Foreign key to message_out',

Matches message_out.id of the corresponding record.

dmarc_reason ENUM ('none', 'forwarded', 'sampled_out',

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 (>= 2), is in DNSWL, or VBR.

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.

last_recv INT UNSIGNED NOT NULL DEFAULT 0,

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.

dmarc_spf ENUM ('none', 'fail', 'pass') DEFAULT 'none',

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.

addr VARCHAR(63) NOT NULL,

User's email address. Set by stored procedure sent_message after variables local_part and domain.

received_count SMALLINT UNSIGNED NOT NULL,

The number of Received: header fields.

domain INT UNSIGNED NOT NULL COMMENT 'Foreign key to domain',

Matches domain.id of the corresponding record.

ino INT UNSIGNED NOT NULL,

Inode number. See Traits and variables common to incoming and outgoing messages

mtime INT UNSIGNED NOT NULL,

Unix epoch. See Traits and variables common to incoming and outgoing messages

dmarc_rec VARCHAR(63) NOT NULL DEFAULT '',

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).

prefix_len TINYINT NOT NULL DEFAULT 0,

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.

whitelisted TINYINT NOT NULL DEFAULT 0,

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.

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.

table msg_out_ref

vbr ENUM ('spamhaus', 'who_else') NOT NULL,

A flag indicating whether a vouch from the vestigial Spamhaus whitelist. Normally, the empty string.

spf ENUM ('none', 'neutral', 'pass', 'fail', 'softfail', 'temperror', 'permerror') NOT NULL,

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.

original_ri MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,

The Report Interval requested by the domain.

Updated by stored procedure recv_from_domain.

dkim ENUM ('none', 'pass', 'fail', 'policy', 'neutral', 'temperror', 'permerror') NOT NULL,

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.

Installation

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.

Tables

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:

domain

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.

msg_ref

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.

message_in

Incoming messages; that is, checked rather than signed by this filter. One record per message.

msg_out_ref

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.

message_out

Outgoing messages; that is, possibly signed by this filter. One record per message.

user

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.

table dmarc_bounce

addr
days
since

table domain

add_adsp

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_dmarc

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.

dmarc_rec

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).

dmarc_ri

A multiple of original_ri that matches the honored_report_interval.

dmarc_rua

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.

domain

The full domain name, presumably in ASCII.

id

This is an auto incremented numeric primary key. It is used to unambiguously refer to records of this table.

last_recv

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.

last_report

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.

last_sent

The actual time when an outgoing message to this domain was filtered.

Updated by stored procedure sent_to_domain.

original_ri

The Report Interval requested by the domain.

Updated by stored procedure recv_from_domain.

prefix_len

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.

recv

Updated by stored procedure recv_from_domain, it grows indefinitely.

sent

Updated by stored procedure sent_to_domain, it grows indefinitely.

since

Set on record creation, this field is never modified.

spamtrap
whitelisted

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.

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.

table message_in

content_encoding

Set in db_sql_insert_message after the value found in the incoming message. See Traits and variables common to incoming and outgoing messages.

content_type

Value of Content-Type truncated at the semicolon.

Set in db_sql_insert_message. See Traits and variables common to incoming and outgoing messages.

date

String value of header field Date:

Set in db_sql_insert_message. See Traits and variables common to incoming and outgoing messages.

dmarc_dispo

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_dkim

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.

dmarc_reason

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 (>= 2), is in DNSWL, or VBR.

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_spf

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

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.

id

This is an auto incremented numeric primary key. It is used to unambiguously refer to records of this table.

ino

Inode number. See Traits and variables common to incoming and outgoing messages

ip

The IP number of the sending relay. It is either read from Received: or from REMOTE_ADDR. Since it is a binary field, you have to resort to a function in order to display it. For example:

SELECT COUNT(*) AS cnt, INET_NTOA(CONV(HEX(ip),16,10)) AS submit from message_out GROUP BY IP ORDER BY cnt DESC LIMIT 10;

mailing_list

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.

message_id

String value of Message-Id.

Set in db_sql_insert_message. See Traits and variables common to incoming and outgoing messages.

mtime

Unix epoch. See Traits and variables common to incoming and outgoing messages

pid

Process id. See Traits and variables common to incoming and outgoing messages

received_count

The number of Received: header fields.

score

It's there for content filters which can determine the spamminess of received messages.

Neither set nor used by zdkim.

signatures_count

The number of DKIM signatures.

spamtrap

table message_out

content_encoding

Set by stored procedure sent_message after the value found in the outgoing message. See Traits and variables common to incoming and outgoing messages.

content_type

Value of Content-Type truncated at the semicolon.

Set by stored procedure sent_message. See Traits and variables common to incoming and outgoing messages.

date

String value of header field Date:

Set by stored procedure sent_message. See Traits and variables common to incoming and outgoing messages.

envelope_sender

String value of header field Date:

Set by stored procedure sent_message. See Traits and variables common to incoming and outgoing messages.

id

This is an auto incremented numeric primary key. It is used to unambiguously refer to records of this table.

ino

Inode number. See Traits and variables common to incoming and outgoing messages

ip

The IP number of the sending relay. It is either read from Received: or from REMOTE_ADDR. Since it is a binary field, you have to resort to a function in order to display it. For example:

SELECT COUNT(*) AS cnt, INET_NTOA(CONV(HEX(ip),16,10)) AS submit from message_out GROUP BY IP ORDER BY cnt DESC LIMIT 10;

message_id

String value of Message-Id.

Set by stored procedure sent_message. See Traits and variables common to incoming and outgoing messages.

mtime

Unix epoch. See Traits and variables common to incoming and outgoing messages

pid

Process id. See Traits and variables common to incoming and outgoing messages

rcpt_count

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.

user

Matches user.id of the corresponding record.

table msg_out_ref

domain

Matches domain.id of the corresponding record.

id

This is an auto incremented numeric primary key. It is used to unambiguously refer to records of this table.

message_out

Matches message_out.id of the corresponding record.

table msg_ref

auth

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.

vbr

The seldom used Vouch By Reference. This relationship is flagged only if the vouch was verified according to trusted_vouchers.

rep and rep_s

For domains involved in the seemingly defunct DKIM reputation, as subjects and/or signers respectively.

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;

dkim

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.

dkim_order

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.

dkim_selector

The signature selector, s= tag.

dkim_trans

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.

domain

Matches domain.id of the corresponding record.

id

This is an auto incremented numeric primary key. It is used to unambiguously refer to records of this table.

message_in

Matches message_in.id of the corresponding record.

reputation

The integer that could be found if DKIM-reputation queries worked.

spf

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.

vbr

A flag indicating whether a vouch from the vestigial Spamhaus whitelist. Normally, the empty string.

table user

addr

User's email address. Set by stored procedure sent_message after variables local_part and domain.

id

This is an auto incremented numeric primary key. It is used to unambiguously refer to records of this table.

rcpt_max_perday