Recently I followed a fantastic article from Skeleton on how to set up DMARC. It all works mostly, and I’m grateful to OpenDMARC developers for taking the time to create this implementation. At the same time, my reporting part of DMARC kept throwing errors. When I started looking closer at it, I realised there’s no way OpenDMARC reporting functionality would ever work with the supplied database schema. I’m going to document changes to OpenDMARC 1.3.1 that fixed the issues on my server.
Creating OpenDMARC database
First things first, the OpenDMARC schema supplied in /usr/share/doc/opendmarc/schema.mysql could not be imported into MySQL on Ubuntu 16.04.1 LTS despite following the steps above. The reason was the default value for “lastsent” field in requests table. It was set to “0000-00-00 00:00:00”. Well, it seems MySQL on Ubuntu 16.04.1 LTS runs in “strict” mode, and a setting NO_ZERO_DATE prevents such default timestamps. The fix was cruel: I just set the default value to “1971-01-01 00:00:01” in the schema.
Importing with opendmarc-import
The next problem was with the opendmarc-import script. It kept throwing an error:
opendmarc-import: failed to create table ID: Field ‘repuri’ doesn’t have a default value
Looking at Perl code, I can see that the authors wanted to use a pattern “get_db_row_id_if_exists_or_create_if_does_not”. The problem with how this pattern was implemented is that most, if not all, database fields were marked as “NOT NULL”. Clearly, creating an empty row to get its ID in a table with NOT NULL fields isn’t going to work – one needs to provide all fields!
To cut a long story short, updating database schema was much easier than fixing the code. Here is the updated OpenDMARC SQL database schema for your download. Please note: the scripts provided have “CREATE” statements, not “ALTER.” Please modify to ALTER if you already have some data in your DB (unlikely, if, like in my case, OpenDMARC reporting didn’t work properly anyway).
Reporting with opendmarc-reports
Once the above problem was solved, there were still errors reported by opendmarc-reports:
Use of uninitialized value $answer in scalar chomp at /usr/sbin/opendmarc-reports line 938.
Use of uninitialized value $answer in concatenation (.) or string at /usr/sbin/opendmarc-reports line 939.
Examining the code, it seems using “Perl voodoo” isn’t always the best idea: Net::SMTP Perl module didn’t have ‘net_cmd_resp’ to return. Adding
$answer = $smtp->message() if (!defined($answer));
after line 938 of /usr/sbin/opendmarc-reports seems to have fixed this problem as well.
Expiring with opendmarc-expire
As a final step of reporting script, it runs opendmarc-expire. In my case, I got the following error:
opendmarc-expire: DELETE failed: Incorrect datetime value: ‘0000-00-00 00:00:00’ for column ‘lastsent’ at row 1
It is exactly the consequence of NO_ZERO_DATE setting as described above. All I did was update /usr/sbin/opendmarc-expire, line 343 to:
$dbi_s = $dbi_h->prepare(“DELETE FROM requests WHERE lastsent <= DATE_SUB(CURRENT_TIMESTAMP(), INTERVAL ? DAY) AND NOT lastsent = ‘1971-01-01 00:00:01′”);
That was enough to fix the issue.
“Hacking” OpenDMARC as above fixed all the problems I was experiencing. I didn’t bother to create a patch or submit bugs properly, but I may do so in the future. Let me know if this post helped anyone!