From da92e3b4e53441f5c960812a29c68126af733459 Mon Sep 17 00:00:00 2001 From: Neil Smith Date: Mon, 9 Jan 2017 10:41:48 +0000 Subject: [PATCH] Added Schipka's changes --- README.md | 14 +++ usr/sbin/opendmarc-expire | 2 +- usr/sbin/opendmarc-reports | 1 + usr/share/doc/opendmarc/mkdb.mysql | 165 ++++++++++++++++----------- usr/share/doc/opendmarc/schema.mysql | 93 --------------- 5 files changed, 117 insertions(+), 158 deletions(-) create mode 100644 README.md delete mode 100644 usr/share/doc/opendmarc/schema.mysql diff --git a/README.md b/README.md new file mode 100644 index 0000000..070d89b --- /dev/null +++ b/README.md @@ -0,0 +1,14 @@ +# Updated opendmarc for Ubuntu + +The [opendmarc package](http://packages.ubuntu.com/xenial/opendmarc) for Ubuntu Xenial has a few problems. + +Maksym Schipka has described [some changes to make to the package](https://schipka.com/archives/154) to get it to work. I've also added SASL authentication for sending report messages. + +The changes are: + +* Updates to the SQL schema, to fix default timestamps and values +* Updates to the `opendmarc-expire` script in line with the updated schema +* Updates to the `opendmarc-report` script to fix SMTP request return values +* Updates to the `opendmarc-report` script to allow SASL authentication when sending reports + + diff --git a/usr/sbin/opendmarc-expire b/usr/sbin/opendmarc-expire index ccffc2d..a699512 100755 --- a/usr/sbin/opendmarc-expire +++ b/usr/sbin/opendmarc-expire @@ -340,7 +340,7 @@ if ($verbose) print STDERR "$progname: expiring request data older than $maxage days\n"; } -$dbi_s = $dbi_h->prepare("DELETE FROM requests WHERE lastsent <= DATE_SUB(CURRENT_TIMESTAMP(), INTERVAL ? DAY) AND NOT lastsent = '0000-00-00 00:00:00'"); +$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'"); $rows = $dbi_s->execute($maxage); if (!$rows) { diff --git a/usr/sbin/opendmarc-reports b/usr/sbin/opendmarc-reports index 7616d8d..b929933 100755 --- a/usr/sbin/opendmarc-reports +++ b/usr/sbin/opendmarc-reports @@ -935,6 +935,7 @@ foreach (@$domainset) { # now perl voodoo: $answer = ${${*$smtp}{'net_cmd_resp'}}[1]; + $answer = $smtp->message() if (!defined($answer)); chomp($answer); print STDERR "$progname: $smtpstatus report for $domain to $repdest ($answer)\n"; } diff --git a/usr/share/doc/opendmarc/mkdb.mysql b/usr/share/doc/opendmarc/mkdb.mysql index 83f5de5..05962b9 100644 --- a/usr/share/doc/opendmarc/mkdb.mysql +++ b/usr/share/doc/opendmarc/mkdb.mysql @@ -1,77 +1,114 @@ --- Copyright (c) 2013, The Trusted Domain Project. All rights reserved. +/* +SQLyog Ultimate v11.0 (64 bit) +MySQL - 5.7.15-0ubuntu0.16.04.1 : Database - opendmarc +********************************************************************* +*/ -- MySQL command sequence to create a database to accumulate OpenDMARC -- report data +/*!40101 SET NAMES utf8 */; + +/*!40101 SET SQL_MODE=''*/; + +/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; +/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; +/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; +/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; +DROP DATABASE IF EXISTS opendmarc; +CREATE DATABASE /*!32312 IF NOT EXISTS*/`opendmarc` /*!40100 DEFAULT CHARACTER SET latin1 */; + +USE `opendmarc`; + -- table mapping domain names to id numbers -CREATE TABLE domains ( - id INT(11) NOT NULL AUTO_INCREMENT, - name VARCHAR(255) NOT NULL, - firstseen TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, - PRIMARY KEY(id), - UNIQUE KEY(name) -) ENGINE=innodb DEFAULT CHARSET=latin1; +/*Table structure for table `domains` */ +CREATE TABLE `domains` ( + `id` int(11) NOT NULL AUTO_INCREMENT, + `name` varchar(255) NOT NULL, + `firstseen` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, + PRIMARY KEY (`id`), + UNIQUE KEY `name` (`name`) +) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=latin1; -- table mapping IP addresses to id numbers -CREATE TABLE ipaddr ( - id INT(11) NOT NULL AUTO_INCREMENT, - addr VARCHAR(64) DEFAULT NULL, - firstseen TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, - PRIMARY KEY(id), - UNIQUE KEY(addr) -) ENGINE=innodb DEFAULT CHARSET=latin1; +/*Table structure for table `ipaddr` */ + +CREATE TABLE `ipaddr` ( + `id` int(11) NOT NULL AUTO_INCREMENT, + `addr` varchar(64) NOT NULL, + `firstseen` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, + PRIMARY KEY (`id`), + UNIQUE KEY `addr` (`addr`) +) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=latin1; -- table tracking message-specific data -CREATE TABLE messages ( - id INT(11) NOT NULL AUTO_INCREMENT, - date TIMESTAMP NOT NULL default CURRENT_TIMESTAMP, - jobid VARCHAR(128) NOT NULL, - reporter INT(10) UNSIGNED NOT NULL, - ip INT(10) UNSIGNED NOT NULL, - policy TINYINT(3) UNSIGNED NOT NULL, - disp TINYINT(3) UNSIGNED NOT NULL, - from_domain INT(10) UNSIGNED NOT NULL, - env_domain INT(10) UNSIGNED NOT NULL, - policy_domain INT(10) UNSIGNED NOT NULL, - sigcount TINYINT(3) UNSIGNED NOT NULL, - spf TINYINT(3) NOT NULL, - align_spf TINYINT(3) UNSIGNED NOT NULL, - align_dkim TINYINT(3) UNSIGNED NOT NULL, - PRIMARY KEY(id), - UNIQUE KEY(reporter,date,jobid) -) ENGINE=innodb DEFAULT CHARSET=latin1; +/*Table structure for table `messages` */ + +CREATE TABLE `messages` ( + `id` int(11) NOT NULL AUTO_INCREMENT, + `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, + `jobid` varchar(128) NOT NULL, + `reporter` int(10) unsigned NOT NULL, + `policy` tinyint(3) unsigned NOT NULL, + `disp` tinyint(3) unsigned NOT NULL, + `ip` int(10) unsigned NOT NULL, + `env_domain` int(10) unsigned NOT NULL, + `from_domain` int(10) unsigned NOT NULL, + `policy_domain` int(10) unsigned DEFAULT NULL, + `spf` tinyint(3) unsigned NOT NULL, + `align_dkim` tinyint(3) unsigned NOT NULL, + `align_spf` tinyint(3) unsigned NOT NULL, + `sigcount` tinyint(3) unsigned NOT NULL, + PRIMARY KEY (`id`), + UNIQUE KEY `reporter` (`reporter`,`date`,`jobid`), + KEY `date` (`date`) +) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=latin1; -- table mapping reporters to ids -CREATE TABLE reporters ( - id INT(11) NOT NULL AUTO_INCREMENT, - name VARCHAR(255) NOT NULL, - firstseen TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, - PRIMARY KEY(id), - UNIQUE KEY(name) -) ENGINE=innodb DEFAULT CHARSET=latin1; +/*Table structure for table `reporters` */ + +CREATE TABLE `reporters` ( + `id` int(11) NOT NULL AUTO_INCREMENT, + `name` varchar(255) NOT NULL, + `firstseen` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, + PRIMARY KEY (`id`), + UNIQUE KEY `name` (`name`) +) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1; -- table tracking report requests -CREATE TABLE requests ( - id INT(11) NOT NULL AUTO_INCREMENT, - domain INT(11) NOT NULL, - repuri VARCHAR(255) NOT NULL, - pct TINYINT(4) NOT NULL, - policy TINYINT(4) NOT NULL, - spolicy TINYINT(4) NOT NULL, - aspf TINYINT(4) NOT NULL, - adkim TINYINT(4) NOT NULL, - locked TINYINT(4) NOT NULL DEFAULT '0', - firstseen TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, - lastsent TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00', - PRIMARY KEY(id) -) ENGINE=innodb DEFAULT CHARSET=latin1; - --- table for tracking DKIM signature evaluation results -CREATE TABLE signatures ( - id INT(11) NOT NULL AUTO_INCREMENT, - message INT(11) NOT NULL, - domain INT(11) NOT NULL, - pass TINYINT(4) NOT NULL, - error TINYINT(4) NOT NULL, - PRIMARY KEY(id) -) ENGINE=innodb DEFAULT CHARSET=latin1; +/*Table structure for table `requests` */ + +CREATE TABLE `requests` ( + `id` int(11) NOT NULL AUTO_INCREMENT, + `domain` int(11) NOT NULL, + `repuri` varchar(255) DEFAULT NULL, + `adkim` tinyint(4) DEFAULT NULL, + `aspf` tinyint(4) DEFAULT NULL, + `policy` tinyint(4) DEFAULT NULL, + `spolicy` tinyint(4) DEFAULT NULL, + `pct` tinyint(4) DEFAULT NULL, + `locked` tinyint(4) NOT NULL DEFAULT '0', + `firstseen` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, + `lastsent` timestamp NOT NULL DEFAULT '1971-01-01 00:00:01', + PRIMARY KEY (`id`), + UNIQUE KEY `domain` (`domain`), + KEY `lastsent` (`lastsent`) +) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=latin1; + +-- table for tracking DKIM signature +/*Table structure for table `signatures` */ + +CREATE TABLE `signatures` ( + `id` int(11) NOT NULL AUTO_INCREMENT, + `message` int(11) NOT NULL, + `domain` int(11) NOT NULL, + `pass` tinyint(4) NOT NULL, + `error` tinyint(4) NOT NULL, + PRIMARY KEY (`id`), + KEY `message` (`message`) +) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=latin1; + +/*!40101 SET SQL_MODE=@OLD_SQL_MODE */; +/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; +/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */; +/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; diff --git a/usr/share/doc/opendmarc/schema.mysql b/usr/share/doc/opendmarc/schema.mysql deleted file mode 100644 index 3f878cb..0000000 --- a/usr/share/doc/opendmarc/schema.mysql +++ /dev/null @@ -1,93 +0,0 @@ --- OpenDMARC database schema --- --- Copyright (c) 2012, The Trusted Domain Project. --- All rights reserved. - -CREATE DATABASE IF NOT EXISTS opendmarc; -USE opendmarc; - --- A table for mapping domain names and their DMARC policies to IDs -CREATE TABLE IF NOT EXISTS domains ( - id INT NOT NULL AUTO_INCREMENT, - name VARCHAR(255) NOT NULL, - firstseen TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, - - PRIMARY KEY(id), - UNIQUE KEY(name) -); - --- A table for logging reporting requests -CREATE TABLE IF NOT EXISTS requests ( - id INT NOT NULL AUTO_INCREMENT, - domain INT NOT NULL, - repuri VARCHAR(255) NOT NULL, - adkim TINYINT NOT NULL, - aspf TINYINT NOT NULL, - policy TINYINT NOT NULL, - spolicy TINYINT NOT NULL, - pct TINYINT NOT NULL, - locked TINYINT NOT NULL, - firstseen TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, - lastsent TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00', - - PRIMARY KEY(id), - KEY(lastsent), - UNIQUE KEY(domain) -); - --- A table for reporting hosts -CREATE TABLE IF NOT EXISTS reporters ( - id INT NOT NULL AUTO_INCREMENT, - name VARCHAR(255) NOT NULL, - firstseen TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, - - PRIMARY KEY(id), - UNIQUE KEY(name) -); - --- A table for IP addresses -CREATE TABLE IF NOT EXISTS ipaddr ( - id INT NOT NULL AUTO_INCREMENT, - addr VARCHAR(64) NOT NULL, - firstseen TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, - - PRIMARY KEY(id), - UNIQUE KEY(addr) -); - --- A table for messages -CREATE TABLE IF NOT EXISTS messages ( - id INT NOT NULL AUTO_INCREMENT, - date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, - jobid VARCHAR(128) NOT NULL, - reporter INT UNSIGNED NOT NULL, - policy TINYINT UNSIGNED NOT NULL, - disp TINYINT UNSIGNED NOT NULL, - ip INT UNSIGNED NOT NULL, - env_domain INT UNSIGNED NOT NULL, - from_domain INT UNSIGNED NOT NULL, - policy_domain INT UNSIGNED NOT NULL, - spf TINYINT UNSIGNED NOT NULL, - align_dkim TINYINT UNSIGNED NOT NULL, - align_spf TINYINT UNSIGNED NOT NULL, - sigcount TINYINT UNSIGNED NOT NULL, - - PRIMARY KEY(id), - KEY(date), - UNIQUE KEY(reporter, date, jobid) -); - --- A table for signatures -CREATE TABLE IF NOT EXISTS signatures ( - id INT NOT NULL AUTO_INCREMENT, - message INT NOT NULL, - domain INT NOT NULL, - pass TINYINT NOT NULL, - error TINYINT NOT NULL, - - PRIMARY KEY(id), - KEY(message) -); - --- CREATE USER 'opendmarc'@'localhost' IDENTIFIED BY 'changeme'; --- GRANT ALL ON opendmarc.* to 'opendmarc'@'localhost'; -- 2.34.1