Subject: Re: [spfbis] Experiment document

From: "Murray S. Kucherawy" <[email-masked]>
Date: Tue, 3 Apr 2012 04:16:36 +0000

Hi Rolf,

The data I've been collecting over time records, in addition to lots of DKIM details, the SPF and Sender-ID results on each message if they're available.  The answer to the question, then, is to count the number of records for each pass/fail combination of SPF and Sender-ID, and report that.  There are only four possible combinations to that truth table.  The numbers in my data show that over 95% of the time, SPF and Sender-ID come to the same conclusion about a message (that's the ratio of the pass/pass plus fail/fail rows to the total).  I wasn't able to characterize the messages in the remaining less-than-5% (e.g., they weren't all mailing list traffic).

The distribution question might be interesting, especially if we find that SPF records average a lot more (or less) in terms of resolution records compared to Sender-ID.  I believe Scott plans to run this survey based on my data.

I posted a link to my database dump earlier today.  Feel free to experiment with the data if you like, and let us know if you find anything interesting.

-MSK

From: [email-masked] [mailto:[email-masked]] On Behalf Of Rolf E. Sonneveld
Sent: Friday, March 30, 2012 2:33 PM
To: Murray S. Kucherawy
Cc: [email-masked]
Subject: Re: [spfbis] Experiment document

-          What is the relative deployment of SPF vs TXT records?

-          How many clients appear to be querying each record type?

-          What are counts on records that are specific to Sender-ID (i.e., spf2.0/pra) versus others?

How much do the Sender-ID results deviate from the SPF results in terms of pass/fail?

Not sure I understand this correctly: the output of questions 1 and 3 can be derived easily by some database queries, but am I correct that question 4 requires processing of IP addresses/ranges to see what the results are? If so, what is the test set to test with? How do you create test sets when include:, redirect: or macro's are used?

-          How often do the validated identifiers differ between SPF and Sender-ID?

How many implementations of each of the four RFCs are known to exist?  (This is anecdotal, not survey-based.)

Add this one:

- distribution of number of DNS queries required to parse the SPF record.

And the following appendix topics:

-          What operational considerations were observed (e.g., firewalls, dropped packets for unknown types)?

-          The whole SPF vs. TXT evolution discussion

-          How often were non-SPF records found in TXT queries?

Please bash the list now.

I also need to add a paragraph that describes the surveys we've done.  I'll add that for mine; Philip, please send me a description of yours, on-list or off.

/rolf

---

From: "Rolf E. Sonneveld" <[email-masked]>
Date: Tue, 03 Apr 2012 21:59:15 +0200

Hi, Murray,

On 4/3/12 6:16 AM, Murray S. Kucherawy wrote:
>
> Hi Rolf,
>
> The data I've been collecting over time records, in addition to lots 
> of DKIM details, the SPF and Sender-ID results on each message if 
> they're available.  The answer to the question, then, is to count the 
> number of records for each pass/fail combination of SPF and Sender-ID, 
> and report that.  There are only four possible combinations to that 
> truth table.  The numbers in my data show that over 95% of the time, 
> SPF and Sender-ID come to the same conclusion about a message (that's 
> the ratio of the pass/pass plus fail/fail rows to the total).  I 
> wasn't able to characterize the messages in the remaining less-than-5% 
> (e.g., they weren't all mailing list traffic).
>

Aah, I thought you had gathered data by querying 'n' domains for SPF and 
Sender-ID DNS records, but now I understand you have been collecting 
data by looking at real messages, sorry for any confusion I might have 
caused.

/rolf

---

From: Philip Gladstone <[email-masked]>
Date: Tue, 03 Apr 2012 20:40:37 -0400

On 4/3/2012 3:59 PM, Rolf E. Sonneveld wrote:
>
> Aah, I thought you had gathered data by querying 'n' domains for SPF 
> and Sender-ID DNS records, but now I understand you have been 
> collecting data by looking at real messages, sorry for any confusion I 
> might have caused.
>
> /rolf
>
You may have been confusing Murray's data with my data. My data was 
gathered by querying 1 million domains as you describe. The repository 
at http://www.blackops.org/~msk/spfbis contains both data sets.

Philip
> -- 
> Philip Gladstone
> Distinguished Engineer
> Product Development
> [email-masked]
> Phone: +1 978-ZEN-TOAD (+1 978 936 8623)
> Google: +1 978 800 1010
> Ham radio: N1DQ

---

From: Hector Santos <[email-masked]>
Date: Tue, 03 Apr 2012 20:53:44 -0400

> at http://www.blackops.org/~msk/spfbis contains both data sets.

Hi Philip,

It would help to indicate the your data is an SQLITE3 database table
and Murray's is an MySQL dump that requires an creating a database
first.   Posting the schema and meaning of the fields would help too. :)

-- 
Hector Santos, CTO
http://www.santronics.com
http://santronics.blogspot.com

---

From: "Murray S. Kucherawy" <[email-masked]>
Date: Wed, 4 Apr 2012 04:13:44 +0000

> -----Original Message-----
> From: [email-masked] [mailto:[email-masked]] On Behalf Of Hector Santos
> Sent: Tuesday, April 03, 2012 5:54 PM
> To: [email-masked]

It is indeed SQLITE3.  You can get the schema from the .schema command:

% sqlite3 spf.db
SQLite version 3.6.14.2
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .schema
CREATE TABLE "spf"(domain varchar(255), record varchar(255), rrtype varchar(3));
CREATE UNIQUE INDEX spfrr2 on spf(domain, rrtype);

Philip's survey was of one million domains, though I don't know how he selected for them.  The columns are (obviously) the domain name, the content of the retrieved record, and the type of the retrieved record.  His survey only recorded answers he actually received, and did not record multiple instances of each record type because of the unique index.

My survey used MySQL.  For the full schema, use either "DESCRIBE spf" or "SHOW CREATE TABLE spf".  It polled 250,000+ domains that were reported through the OpenDKIM statistics reporting system, and all were domains for which at some point both an SPF and Sender-ID result had been reported.  It includes both replies received and those for which an error or NXDOMAIN were returned (the "nxdomain" column) as well as the round-trip time measured in microseconds for the query.  If multiple records were returned for a single query, all of them were recorded, though order cannot be preserved because the returned order is randomized by the nameservers.  It only reported the returned content; it did not expand any macros (since this survey was not done during message receipt).  Every domain should have at least one TXT row and at least one SPF row, since a row is also used to log non-responses.  The columns are:

	id: unique row ID
	date: the date/time when the record was retrieved
	domain: the domain name that was queried
	type: either 16 (TXT) or 99 (SPF)
	data: the record's content
	nxdomain: 1 if there was an error or if a non-reply was returned, 0 otherwise
	rtt: round-trip time, in microseconds

I periodically re-run my survey to collect records for new domains not seen since the previous pass, but there aren't that many new ones going in now.

Despite the Subject: of this message, these reports will be very useful input to the SPFbis document itself when considering what's in use and what isn't.

Let me know if there are any other questions. 

-MSK