From da92e3b4e53441f5c960812a29c68126af733459 Mon Sep 17 00:00:00 2001
From: Neil Smith <neil.git@njae.me.uk>
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.43.0