From: Neil Smith <neil.git@njae.me.uk>
Date: Mon, 9 Jan 2017 09:52:06 +0000 (+0000)
Subject: Initial commit
X-Git-Url: https://git.njae.me.uk/?p=opendmarc.git;a=commitdiff_plain;h=256870664833badf02ba69cb474a5efecb5c5fbb

Initial commit
---

256870664833badf02ba69cb474a5efecb5c5fbb
diff --git a/.gitignore b/.gitignore
new file mode 100644
index 0000000..df7cdd5
--- /dev/null
+++ b/.gitignore
@@ -0,0 +1,5 @@
+# Sublime text
+*.sublime-workspace
+
+# Logs
+*.log
diff --git a/etc/default/opendmarc b/etc/default/opendmarc
new file mode 100644
index 0000000..3a45664
--- /dev/null
+++ b/etc/default/opendmarc
@@ -0,0 +1,10 @@
+# Command-line options specified here will override the contents of
+# /etc/opendmarc.conf. See opendmarc(8) for a complete list of options.
+#DAEMON_OPTS=""
+#
+# Uncomment to specify an alternate socket
+# Note that setting this will override any Socket value in opendkim.conf
+#SOCKET="local:/var/run/opendmarc/opendmarc.sock" # default
+#SOCKET="inet:54321" # listen on all interfaces on port 54321
+#SOCKET="inet:12345@localhost" # listen on loopback on port 12345
+#SOCKET="inet:12345@192.0.2.1" # listen on 192.0.2.1 on port 12345
diff --git a/etc/init.d/opendmarc b/etc/init.d/opendmarc
new file mode 100755
index 0000000..186a394
--- /dev/null
+++ b/etc/init.d/opendmarc
@@ -0,0 +1,151 @@
+#! /bin/sh
+#
+### BEGIN INIT INFO
+# Provides:		opendmarc
+# Required-Start:	$syslog $time $local_fs $remote_fs $named $network
+# Required-Stop:	$syslog $time $local_fs $remote_fs
+# Default-Start:	2 3 4 5
+# Default-Stop:		0 1 6
+# Short-Description:	Start the OpenDMARC service
+# Description:		Enable DMAR verification and reporting provided by OpenDMARC
+### END INIT INFO
+
+PATH=/sbin:/bin:/usr/sbin:/usr/bin
+DAEMON=/usr/sbin/opendmarc
+NAME=opendmarc
+DESC="OpenDMARC"
+RUNDIR=/var/run/$NAME
+USER=opendmarc
+GROUP=opendmarc
+SOCKET=local:$RUNDIR/$NAME.sock
+PIDFILE=$RUNDIR/$NAME.pid
+
+# How long to wait for the process to die on stop/restart
+stoptimeout=5
+
+test -x $DAEMON || exit 0
+
+# Include LSB provided init functions
+. /lib/lsb/init-functions
+
+# Include opendkim defaults if available
+if [ -f /etc/default/opendmarc ] ; then
+	. /etc/default/opendmarc
+fi
+
+if [ -f /etc/opendmarc.conf ]; then
+	CONFIG_SOCKET=`awk '$1 == "Socket" { print $2 }' /etc/opendmarc.conf`
+fi
+
+# This can be set via Socket option in config file, so it's not required
+if [ -n "$SOCKET" -a -z "$CONFIG_SOCKET" ]; then
+	DAEMON_OPTS="-p $SOCKET $DAEMON_OPTS"
+fi
+
+DAEMON_OPTS="-c /etc/opendmarc.conf -u $USER -P $PIDFILE $DAEMON_OPTS"
+
+start() {
+	# Create the run directory if it doesn't exist
+	if [ ! -d "$RUNDIR" ]; then
+		install -o "$USER" -g "$GROUP" -m 755 -d "$RUNDIR" || return 2
+		[ -x /sbin/restorecon ] && /sbin/restorecon "$RUNDIR"
+	fi
+	# Clean up stale sockets
+	if [ -f "$PIDFILE" ]; then
+		pid=`cat $PIDFILE`
+		if ! ps -C "$DAEMON" -s "$pid" >/dev/null; then
+			rm "$PIDFILE"
+			TMPSOCKET=""
+			if [ -n "$SOCKET" ]; then
+				TMPSOCKET="$SOCKET"
+			elif [ -n "$CONFIG_SOCKET" ]; then
+				TMPSOCKET="$CONFIG_SOCKET"
+			fi
+			if [ -n "$TMPSOCKET" ]; then
+				# UNIX sockets may be specified with or without the
+				# local: prefix; handle both
+				t=`echo $SOCKET | cut -d: -f1`
+				s=`echo $SOCKET | cut -d: -f2`
+				if [ -e "$s" -a -S "$s" ]; then
+					if [ "$t" = "$s" -o "$t" = "local" ]; then
+						rm "$s"
+					fi
+				fi
+			fi
+		fi
+	fi
+	start-stop-daemon --start --quiet --pidfile "$PIDFILE" --exec "$DAEMON" --test -- $DAEMON_OPTS || return 1
+	start-stop-daemon --start --quiet --pidfile "$PIDFILE" --exec "$DAEMON" -- $DAEMON_OPTS || return 2
+}
+
+stop() {
+	start-stop-daemon --stop --retry "$stoptimeout" --exec "$DAEMON"
+	[ "$?" = 2 ] && return 2
+}
+
+reload() {
+	start-stop-daemon --stop --signal USR1 --exec "$DAEMON"
+}
+
+status() {
+    local pidfile daemon name status
+
+    pidfile=
+    OPTIND=1
+    while getopts p: opt ; do
+        case "$opt" in
+            p)  pidfile="$OPTARG";;
+        esac
+    done
+    shift $(($OPTIND - 1))
+
+    if [ -n "$pidfile" ]; then
+        pidfile="-p $pidfile"
+    fi
+    daemon="$1"
+    name="$2"
+
+    status="0"
+    pidofproc $pidfile $daemon >/dev/null || status="$?"
+    if [ "$status" = 0 ]; then
+        log_success_msg "$name is running"
+        return 0
+    else
+        log_failure_msg "$name is not running"
+        return $status
+    fi
+}
+
+case "$1" in
+  start)
+	echo -n "Starting $DESC: "
+	start
+	echo "$NAME."
+	;;
+  stop)
+	echo -n "Stopping $DESC: "
+	stop
+	echo "$NAME."
+	;;
+  restart)
+	echo -n "Restarting $DESC: "
+	stop
+	start
+	echo "$NAME."
+	;;
+  reload|force-reload)
+	echo -n "Restarting $DESC: "
+	reload
+	echo "$NAME."
+	;;
+  status)
+	status $DAEMON $NAME
+	;;
+  *)
+	N=/etc/init.d/$NAME
+	echo "Usage: $N {start|stop|restart|reload|force-reload|status}" >&2
+	exit 1
+	;;
+esac
+
+exit 0
diff --git a/etc/opendmarc.conf b/etc/opendmarc.conf
new file mode 100644
index 0000000..e3067bf
--- /dev/null
+++ b/etc/opendmarc.conf
@@ -0,0 +1,72 @@
+# This is a basic configuration that can easily be adapted to suit a standard
+# installation. For more advanced options, see opendkim.conf(5) and/or
+# /usr/share/doc/opendmarc/examples/opendmarc.conf.sample.
+
+##  AuthservID (string)
+##  	defaults to MTA name
+#
+# AuthservID name
+
+##  FailureReports { true | false }
+##  	default "false"
+##
+# FailureReports false
+
+PidFile /var/run/opendmarc.pid
+
+##  RejectFailures { true | false }
+##  	default "false"
+##
+RejectFailures false
+
+##  Syslog { true | false }
+##  	default "false"
+##
+##  Log via calls to syslog(3) any interesting activity.
+#
+Syslog true
+
+##  SyslogFacility facility-name
+##  	default "mail"
+##
+##  Log via calls to syslog(3) using the named facility.  The facility names
+##  are the same as the ones allowed in syslog.conf(5).
+#
+# SyslogFacility mail
+
+##  TrustedAuthservIDs string
+##  	default HOSTNAME
+##
+##  Specifies one or more "authserv-id" values to trust as relaying true
+##  upstream DKIM and SPF results.  The default is to use the name of
+##  the MTA processing the message.  To specify a list, separate each entry
+##  with a comma.  The key word "HOSTNAME" will be replaced by the name of
+##  the host running the filter as reported by the gethostname(3) function.
+#
+# TrustedAuthservIDs HOSTNAME
+
+
+##  UMask mask
+##  	default (none)
+##
+##  Requests a specific permissions mask to be used for file creation.  This
+##  only really applies to creation of the socket when Socket specifies a
+##  UNIX domain socket, and to the HistoryFile and PidFile (if any); temporary
+##  files are normally created by the mkstemp(3) function that enforces a
+##  specific file mode on creation regardless of the process umask.  See
+##  umask(2) for more information.
+#
+UMask 0002
+
+##  UserID user[:group]
+##  	default (none)
+##
+##  Attempts to become the specified userid before starting operations.
+##  The process will be assigned all of the groups and primary group ID of
+##  the named userid unless an alternate group is specified.
+#
+UserID opendmarc:opendmarc
+
+## Path to system copy of PSL (needed to determine organizational domain)
+#
+PublicSuffixList /usr/share/publicsuffix/
diff --git a/opendmarc.sublime-project b/opendmarc.sublime-project
new file mode 100644
index 0000000..24db303
--- /dev/null
+++ b/opendmarc.sublime-project
@@ -0,0 +1,8 @@
+{
+	"folders":
+	[
+		{
+			"path": "."
+		}
+	]
+}
diff --git a/usr/sbin/opendmarc b/usr/sbin/opendmarc
new file mode 100755
index 0000000..96cd1be
Binary files /dev/null and b/usr/sbin/opendmarc differ
diff --git a/usr/sbin/opendmarc-check b/usr/sbin/opendmarc-check
new file mode 100755
index 0000000..cdd4849
Binary files /dev/null and b/usr/sbin/opendmarc-check differ
diff --git a/usr/sbin/opendmarc-expire b/usr/sbin/opendmarc-expire
new file mode 100755
index 0000000..ccffc2d
--- /dev/null
+++ b/usr/sbin/opendmarc-expire
@@ -0,0 +1,465 @@
+#!/usr/bin/perl
+#
+# Copyright (c) 2010-2012, 2014, 2015, The Trusted Domain Project.
+# All rights reserved.
+#
+# Script to age out OpenDMARC aggregate report data
+
+###
+### Setup
+###
+
+use strict;
+use warnings;
+
+use DBI;
+use File::Basename;
+use Getopt::Long;
+use IO::Handle;
+use POSIX;
+
+require DBD::mysql;
+
+# general
+my $progname      = basename($0);
+my $version       = "1.3.1";
+my $verbose       = 0;
+my $helponly      = 0;
+my $showversion   = 0;
+my $alltables     = 0;
+
+my $minmsg;
+my $rowcount;
+
+my $dbi_s;
+my $dbi_h;
+my $dbi_a;
+
+# DB parameters
+my $def_dbhost    = "localhost";
+my $def_dbname    = "opendmarc";
+my $def_dbuser    = "opendmarc";
+my $def_dbpasswd  = "opendmarc";
+my $def_dbport    = "3306";
+my $dbhost;
+my $dbname;
+my $dbuser;
+my $dbpasswd;
+my $dbport;
+
+my $dbscheme      = "mysql";
+
+my $def_maxage    = 180;
+
+my $rows;
+my $maxage;
+
+###
+### NO user-serviceable parts beyond this point
+###
+
+sub usage
+{
+	print STDERR "$progname: usage: $progname [options]\n";
+	print STDERR "\t--alltables        expire rows from all tables\n";
+	print STDERR "\t--dbhost=host      database host [$def_dbhost]\n";
+	print STDERR "\t--dbname=name      database name [$def_dbname]\n";
+	print STDERR "\t--dbpasswd=passwd  database password [$def_dbpasswd]\n";
+	print STDERR "\t--dbport=port      database port [$def_dbport]\n";
+	print STDERR "\t--dbuser=user      database user [$def_dbuser]\n";
+	print STDERR "\t--expire=days      expiration time, in days [$def_maxage]\n";
+	print STDERR "\t--help             print help and exit\n";
+	print STDERR "\t--verbose          verbose output\n";
+	print STDERR "\t--version          print version and exit\n";
+}
+
+# parse command line arguments
+my $opt_retval = &Getopt::Long::GetOptions ('alltables!' => \$alltables,
+                                            'dbhost=s' => \$dbhost,
+                                            'dbname=s' => \$dbname,
+                                            'dbpasswd=s' => \$dbpasswd,
+                                            'dbport=s' => \$dbport,
+                                            'dbuser=s' => \$dbuser,
+                                            'expire=i' => \$maxage,
+                                            'help!' => \$helponly,
+                                            'verbose!' => \$verbose,
+                                            'version!' => \$showversion,
+                                           );
+
+if (!$opt_retval || $helponly)
+{
+	usage();
+
+	if ($helponly)
+	{
+		exit(0);
+	}
+	else
+	{
+		exit(1);
+	}
+}
+
+if ($showversion)
+{
+	print STDOUT "$progname v$version\n";
+	exit(0);
+}
+
+# apply defaults
+if (!defined($dbhost))
+{
+	if (defined($ENV{'OPENDMARC_DBHOST'}))
+	{
+		$dbhost = $ENV{'OPENDMARC_DBHOST'};
+	}
+	else
+	{
+		$dbhost = $def_dbhost;
+	}
+}
+
+if (!defined($dbname))
+{
+	if (defined($ENV{'OPENDMARC_DB'}))
+	{
+		$dbname = $ENV{'OPENDMARC_DB'};
+	}
+	else
+	{
+		$dbname = $def_dbname;
+	}
+}
+
+if (!defined($dbpasswd))
+{
+	if (defined($ENV{'OPENDMARC_PASSWORD'}))
+	{
+		$dbpasswd = $ENV{'OPENDMARC_PASSWORD'};
+	}
+	else
+	{
+		$dbpasswd = $def_dbpasswd;
+	}
+}
+
+if (!defined($dbport))
+{
+	if (defined($ENV{'OPENDMARC_PORT'}))
+	{
+		$dbport = $ENV{'OPENDMARC_PORT'};
+	}
+	else
+	{
+		$dbport = $def_dbport;
+	}
+}
+
+if (!defined($dbuser))
+{
+	if (defined($ENV{'OPENDMARC_USER'}))
+	{
+		$dbuser = $ENV{'OPENDMARC_USER'};
+	}
+	else
+	{
+		$dbuser = $def_dbuser;
+	}
+}
+
+if (!defined($maxage))
+{
+	if (defined($ENV{'OPENDMARC_MAXAGE'}))
+	{
+		$maxage = $ENV{'OPENDMARC_MAXAGE'};
+	}
+	else
+	{
+		$maxage = $def_maxage;
+	}
+}
+
+# sanity check
+if ($maxage <= 0)
+{
+	print STDERR "$progname: invalid expiration time\n";
+	exit(1);
+}
+
+#
+# Let's go!
+#
+
+if ($verbose)
+{
+	print STDERR "$progname: started at " . localtime() . "\n";
+}
+
+my $dbi_dsn = "DBI:" . $dbscheme . ":database=" . $dbname .
+              ";host=" . $dbhost . ";port=" . $dbport;
+
+$dbi_h = DBI->connect($dbi_dsn, $dbuser, $dbpasswd, { PrintError => 0 });
+if (!defined($dbi_h))
+{
+	print STDERR "$progname: unable to connect to database: $DBI::errstr\n";
+	exit(1);
+}
+
+if ($verbose)
+{
+	print STDERR "$progname: connected to database\n";
+}
+
+#
+# Expire messages
+#
+
+if ($verbose)
+{
+	print STDERR "$progname: expiring messages older than $maxage day(s)\n";
+}
+
+$dbi_s = $dbi_h->prepare("DELETE FROM messages WHERE date <= DATE_SUB(CURRENT_TIMESTAMP(), INTERVAL ? DAY)");
+$rows = $dbi_s->execute($maxage);
+if (!$rows)
+{
+	print STDERR "$progname: DELETE failed: " . $dbi_h->errstr;
+	$dbi_s->finish;
+	$dbi_h->disconnect;
+	exit(1);
+}
+elsif ($verbose)
+{
+	if ($rows eq "0E0")
+	{
+		print STDOUT "$progname: no rows deleted\n";
+	}
+	else
+	{
+		print STDOUT "$progname: $rows row(s) deleted\n";
+	}
+}
+
+$dbi_s->finish;
+
+#
+# Expire signatures
+#
+
+$dbi_s = $dbi_h->prepare("SELECT MIN(id) FROM messages");
+if (!$dbi_s->execute)
+{
+	print STDERR "$progname: SELECT failed: " . $dbi_h->errstr;
+	$dbi_s->finish;
+	$dbi_h->disconnect;
+	exit(1);
+}
+
+while ($dbi_a = $dbi_s->fetchrow_arrayref())
+{
+	$minmsg = $dbi_a->[0];
+}
+
+# 
+# We might have emptied the messages table
+# 
+$dbi_s->finish;
+
+if (!defined($minmsg))
+{
+	$dbi_s = $dbi_h->prepare("SELECT COUNT(id) FROM messages");
+	if (!$dbi_s->execute)
+	{
+		print STDERR "$progname: SELECT failed: " . $dbi_h->errstr;
+		$dbi_s->finish;
+		$dbi_h->disconnect;
+		exit(1);
+	}
+
+	while ($dbi_a = $dbi_s->fetchrow_arrayref())
+	{
+		$rowcount = $dbi_a->[0];
+	}
+
+	$dbi_s->finish;
+
+	if (defined($rowcount) && $rowcount == 0)
+	{
+		$dbi_s = $dbi_h->prepare("TRUNCATE TABLE signatures");
+		if ($dbi_s->execute)
+		{
+			print STDERR "$progname: TRUNCATE failed: " . $dbi_h->errstr;
+			$dbi_s->finish;
+			$dbi_h->disconnect;
+			exit(1);
+		}
+
+		$dbi_s->finish;
+	}
+
+	$dbi_h->disconnect;
+	exit(1);
+}
+else
+{
+	if ($verbose)
+	{
+		print STDERR "$progname: expiring signatures on expired messages (id < $minmsg)\n";
+	}
+
+	$dbi_s = $dbi_h->prepare("DELETE FROM signatures WHERE message < ?");
+	$rows = $dbi_s->execute($minmsg);
+	if (!$rows)
+	{
+		print STDERR "$progname: DELETE failed: " . $dbi_h->errstr;
+		$dbi_s->finish;
+		$dbi_h->disconnect;
+		exit(1);
+	}
+	elsif ($verbose)
+	{
+		if ($rows eq "0E0")
+		{
+			print STDOUT "$progname: no rows deleted\n";
+		}
+		else
+		{
+			print STDOUT "$progname: $rows row(s) deleted\n";
+		}
+	}
+
+	$dbi_s->finish;
+}
+
+#
+# Expire request data
+#
+
+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'");
+$rows = $dbi_s->execute($maxage);
+if (!$rows)
+{
+	print STDERR "$progname: DELETE failed: " . $dbi_h->errstr . "\n";
+	$dbi_s->finish;
+	$dbi_h->disconnect;
+	exit(1);
+}
+elsif ($verbose)
+{
+	if ($rows eq "0E0")
+	{
+		print STDOUT "$progname: no rows deleted\n";
+	}
+	else
+	{
+		print STDOUT "$progname: $rows row(s) deleted\n";
+	}
+}
+
+$dbi_s->finish;
+
+if ($alltables)
+{
+	if ($verbose)
+	{
+		print STDERR "$progname: expiring unneeded domain data\n";
+	}
+
+	$dbi_s = $dbi_h->prepare("DELETE FROM domains WHERE id NOT IN
+		(SELECT DISTINCT domain FROM requests) AND id NOT IN
+		(SELECT DISTINCT from_domain FROM messages) AND id NOT IN
+		(SELECT DISTINCT env_domain FROM messages) AND id NOT IN
+		(SELECT DISTINCT policy_domain FROM messages) AND id NOT IN
+		(SELECT DISTINCT domain FROM signatures)");
+	$rows = $dbi_s->execute();
+	if (!$rows)
+	{
+		print STDERR "$progname: DELETE failed: " . $dbi_h->errstr . "\n";
+		$dbi_s->finish;
+		$dbi_h->disconnect;
+		exit(1);
+	}
+	elsif ($verbose)
+	{
+		if ($rows eq "0E0")
+		{
+			print STDOUT "$progname: no rows deleted\n";
+		}
+		else
+		{
+			print STDOUT "$progname: $rows row(s) deleted\n";
+		}
+	}
+
+	if ($verbose)
+	{
+		print STDERR "$progname: expiring unneeded IP data\n";
+	}
+
+	$dbi_s = $dbi_h->prepare("DELETE FROM ipaddr WHERE id NOT IN
+		(SELECT DISTINCT ip FROM messages)");
+	$rows = $dbi_s->execute();
+	if (!$rows)
+	{
+		print STDERR "$progname: DELETE failed: " . $dbi_h->errstr . "\n";
+		$dbi_s->finish;
+		$dbi_h->disconnect;
+		exit(1);
+	}
+	elsif ($verbose)
+	{
+		if ($rows eq "0E0")
+		{
+			print STDOUT "$progname: no rows deleted\n";
+		}
+		else
+		{
+			print STDOUT "$progname: $rows row(s) deleted\n";
+		}
+	}
+
+	if ($verbose)
+	{
+		print STDERR "$progname: expiring unneeded reporter data\n";
+	}
+
+	$dbi_s = $dbi_h->prepare("DELETE FROM reporters WHERE id NOT IN
+		(SELECT DISTINCT reporter FROM messages)");
+	$rows = $dbi_s->execute();
+	if (!$rows)
+	{
+		print STDERR "$progname: DELETE failed: " . $dbi_h->errstr . "\n";
+		$dbi_s->finish;
+		$dbi_h->disconnect;
+		exit(1);
+	}
+	elsif ($verbose)
+	{
+		if ($rows eq "0E0")
+		{
+			print STDOUT "$progname: no rows deleted\n";
+		}
+		else
+		{
+			print STDOUT "$progname: $rows row(s) deleted\n";
+		}
+	}
+}
+	
+#
+# All done!
+#
+
+if ($verbose)
+{
+	print STDERR "$progname: terminating at " . localtime() . "\n";
+}
+
+$dbi_h->disconnect;
+
+exit(0);
diff --git a/usr/sbin/opendmarc-import b/usr/sbin/opendmarc-import
new file mode 100755
index 0000000..d1a241e
--- /dev/null
+++ b/usr/sbin/opendmarc-import
@@ -0,0 +1,606 @@
+#!/usr/bin/perl
+#
+# Copyright (c) 2012, 2014, The Trusted Domain Project.  All rights reserved.
+#
+# Script to import per-message DMARC data.
+
+###
+### Setup
+###
+
+use strict;
+use warnings;
+
+use Switch;
+
+use DBI;
+use File::Basename;
+use Fcntl qw(:flock);
+use Getopt::Long;
+use POSIX;
+
+require DBD::mysql;
+
+# general
+my $progname      = basename($0);
+my $version       = "1.3.1";
+my $verbose       = 0;
+my $helponly      = 0;
+my $showversion   = 0;
+
+# DB parameters
+my $def_dbhost    = "localhost";
+my $def_dbname    = "opendmarc";
+my $def_dbuser    = "opendmarc";
+my $def_dbpasswd  = "opendmarc";
+my $def_dbport    = "3306";
+my $def_interval  = "86400";
+my $dbhost;
+my $dbname;
+my $dbuser;
+my $dbpasswd;
+my $dbport;
+
+my $dbscheme     = "mysql";
+
+my $dbi_a;
+my $dbi_h;
+my $dbi_s;
+my $dbi_t;
+
+my $lineno;
+my $key;
+my $value;
+
+my $action;
+my $adkim;
+my $align_dkim;
+my $align_spf;
+my $aspf;
+my $dd;
+my $dkim_align;
+my @dkim_data;
+my $dkim_domain;
+my @dkim_entry;
+my $dkim_result;
+my $envdomain;
+my $fdomain;
+my $ipaddr;
+my $jobid;
+my $p;
+my $pct;
+my $pdomain;
+my $policy;
+my $received;
+my $reporter;
+my $repuri;
+my $sigcount = 0;
+my $sp;
+my $spf;
+my @rua;
+
+###
+### NO user-serviceable parts beyond this point
+###
+
+sub get_value
+{
+	my $table;
+	my $column;
+	my $id;
+	my $out;
+
+	($table, $column, $id) = @_;
+
+	$dbi_t = $dbi_h->prepare("SELECT $column FROM $table WHERE id = ?");
+	if (!$dbi_t->execute($id))
+	{
+		print STDERR "$progname: failed to $column value for ID $id: " . $dbi_h->errstr . "\n";
+		return undef;
+	}
+
+	while ($dbi_a = $dbi_t->fetchrow_arrayref())
+	{
+		if (defined($dbi_a->[0]))
+		{
+			$out = $dbi_a->[0];
+		}
+	}
+
+	return $out;
+}
+	
+sub get_table_id
+{
+	my $name;
+	my $table;
+	my $column;
+	my $out;
+
+	($name, $table, $column) = @_;
+
+	if (!defined($name) || !defined($table))
+	{
+		return undef;
+	}
+
+	if (!defined($column))
+	{
+		$column = "name";
+	}
+
+	$dbi_t = $dbi_h->prepare("SELECT id FROM $table WHERE $column = ?");
+	if (!$dbi_t->execute($name))
+	{
+		print STDERR "$progname: failed to retrieve table ID: " . $dbi_h->errstr . "\n";
+		return undef;
+	}
+
+	undef $out;
+	while ($dbi_a = $dbi_t->fetchrow_arrayref())
+	{
+		if (defined($dbi_a->[0]))
+		{
+			$out = $dbi_a->[0];
+		}
+	}
+
+	$dbi_t->finish;
+
+	if (!defined($out))
+	{
+		$dbi_t = $dbi_h->prepare("INSERT INTO $table ($column) VALUES(?)");
+		if (!$dbi_t->execute($name))
+		{
+			print STDERR "$progname: failed to create table ID: " . $dbi_h->errstr . "\n";
+			return undef;
+		}
+
+		$dbi_t = $dbi_h->prepare("SELECT LAST_INSERT_ID()");
+		if (!$dbi_t->execute())
+		{
+			print STDERR "$progname: failed to retrieve created table ID: " . $dbi_h->errstr . "\n";
+			return undef;
+		}
+
+		while ($dbi_a = $dbi_t->fetchrow_arrayref())
+		{
+			if (defined($dbi_a->[0]))
+			{
+				$out = $dbi_a->[0];
+			}
+		}
+
+		$dbi_t->finish;
+
+		if (!defined($out))
+		{
+			print STDERR "$progname: failed to retrieve created table ID: " . $dbi_h->errstr . "\n";
+			return undef;
+		}
+	}
+
+	return $out;
+}
+
+sub update_db
+{
+	my $rep_id;
+	my $from_id;
+	my $envfrom_id;
+	my $pdomain_id;
+	my $ipaddr_id;
+	my $msg_id;
+	my $sdomain_id;
+	my $request_id;
+
+	if ($verbose)
+	{
+		print STDERR "$progname: updating at line $lineno\n";
+	}
+
+	$rep_id = get_table_id($reporter, "reporters");
+	$from_id = get_table_id($fdomain, "domains");
+	$envfrom_id = get_table_id($envdomain, "domains");
+	$pdomain_id = get_table_id($pdomain, "domains");
+	$ipaddr_id = get_table_id($ipaddr, "ipaddr", "addr");
+	$request_id = get_table_id($from_id, "requests", "domain");
+
+	if (!defined($rep_id) ||
+	    !defined($from_id) ||
+	    !defined($envfrom_id) ||
+	    !defined($pdomain_id) ||
+	    !defined($ipaddr_id) ||
+	    !defined($request_id))
+	{
+		return;
+	}
+
+	$dbi_s = $dbi_h->prepare("INSERT INTO messages (date, jobid, reporter, policy, disp, ip, env_domain, from_domain, spf, align_spf, align_dkim, sigcount) VALUES(FROM_UNIXTIME(?), ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
+	if (!$dbi_s->execute($received, $jobid, $rep_id, $policy, $action, $ipaddr_id, $envfrom_id, $from_id, $spf, $align_spf, $align_dkim, $sigcount))
+	{
+		print STDERR "$progname: failed to insert message: " . $dbi_h->errstr . "\n";
+		return;
+	}
+
+	$dbi_s->finish;
+
+	undef $msg_id;
+	$dbi_s = $dbi_h->prepare("SELECT LAST_INSERT_ID()");
+	if (!$dbi_s->execute())
+	{
+		print STDERR "$progname: failed to retrieve message ID: " . $dbi_h->errstr . "\n";
+		return;
+	}
+
+	while ($dbi_a = $dbi_s->fetchrow_arrayref())
+	{
+		if (defined($dbi_a->[0]))
+		{
+			$msg_id = $dbi_a->[0];
+		}
+	}
+
+	$dbi_s->finish;
+
+	if (!defined($msg_id))
+	{
+		print STDERR "$progname: failed to retrieve message ID: " . $dbi_h->errstr . "\n";
+		return;
+	}
+
+	$dbi_s = $dbi_h->prepare("INSERT INTO signatures (message, domain, pass, error) VALUES(?, ?, ?, ?)");
+	foreach $dd (0 .. $#dkim_data)
+	{
+		my $sdomain;
+		my $pass;
+		my $error;
+
+		$sdomain = $dkim_data[$dd][0];
+		$pass = $dkim_data[$dd][1];
+		$error = $dkim_data[$dd][2];
+
+		$sdomain_id = get_table_id($sdomain, "domains");
+		if (!defined($sdomain_id))
+		{
+			next;
+		}
+
+		if (!$dbi_s->execute($msg_id, $sdomain_id, $pass, $error))
+		{
+			print STDERR "$progname: failed to insert DKIM data: " . $dbi_h->errstr . "\n";
+			$dbi_s->finish;
+			return;
+		}
+	}
+	$dbi_s->finish;
+
+	if (get_value("requests", "locked", $request_id) != 1)
+	{
+		if (scalar @rua > 0)
+		{
+			$repuri = join(",", @rua);
+			$dbi_s = $dbi_h->prepare("UPDATE requests SET repuri = ? WHERE id = ?");
+
+			if (!$dbi_s->execute($repuri, $request_id))
+			{
+				print STDERR "$progname: failed to update reporting URI for $fdomain: " . $dbi_h->errstr . "\n";
+				$dbi_s->finish;
+				return;
+			}
+
+			$dbi_s->finish;
+		}
+		else
+		{
+			$dbi_s = $dbi_h->prepare("UPDATE requests SET repuri = NULL WHERE id = ?");
+
+			if (!$dbi_s->execute($request_id))
+			{
+				print STDERR "$progname: failed to update reporting URI for $fdomain: " . $dbi_h->errstr . "\n";
+				$dbi_s->finish;
+				return;
+			}
+
+			$dbi_s->finish;
+		}
+
+		$dbi_s = $dbi_h->prepare("UPDATE requests SET adkim = ?, aspf = ?, policy = ?, spolicy = ?, pct = ? WHERE id = ?");
+
+		if (!$dbi_s->execute($adkim, $aspf, $p, $sp, $pct, $request_id))
+		{
+			print STDERR "$progname: failed to update policy data for $fdomain: " . $dbi_h->errstr . "\n";
+			$dbi_s->finish;
+			return;
+		}
+	}
+
+	$dbi_s->finish;
+}
+
+sub usage
+{
+	print STDERR "$progname: usage: $progname [options]\n";
+	print STDERR "\t--dbhost=host      database host [$def_dbhost]\n";
+	print STDERR "\t--dbname=name      database name [$def_dbname]\n";
+	print STDERR "\t--dbpasswd=passwd  database password [$def_dbpasswd]\n";
+	print STDERR "\t--dbport=port      database port [$def_dbport]\n";
+	print STDERR "\t--dbuser=user      database user [$def_dbuser]\n";
+	print STDERR "\t--help             print help and exit\n";
+	print STDERR "\t--verbose          verbose output\n";
+	print STDERR "\t--version          print version and exit\n";
+}
+
+# parse command line arguments
+my $opt_retval = &Getopt::Long::GetOptions ('dbhost=s' => \$dbhost,
+                                            'dbname=s' => \$dbname,
+                                            'dbpasswd=s' => \$dbpasswd,
+                                            'dbport=s' => \$dbport,
+                                            'dbuser=s' => \$dbuser,
+                                            'help!' => \$helponly,
+                                            'verbose!' => \$verbose,
+                                            'version!' => \$showversion,
+                                           );
+
+if (!$opt_retval || $helponly)
+{
+	usage();
+
+	if ($helponly)
+	{
+		exit(0);
+	}
+	else
+	{
+		exit(1);
+	}
+}
+
+if ($showversion)
+{
+	print STDOUT "$progname v$version\n";
+	exit(0);
+}
+
+# apply defaults
+if (!defined($dbhost))
+{
+	if (defined($ENV{'OPENDMARC_DBHOST'}))
+	{
+		$dbhost = $ENV{'OPENDMARC_DBHOST'};
+	}
+	else
+	{
+		$dbhost = $def_dbhost;
+	}
+}
+
+if (!defined($dbname))
+{
+	if (defined($ENV{'OPENDMARC_DB'}))
+	{
+		$dbname = $ENV{'OPENDMARC_DB'};
+	}
+	else
+	{
+		$dbname = $def_dbname;
+	}
+}
+
+if (!defined($dbpasswd))
+{
+	if (defined($ENV{'OPENDMARC_PASSWORD'}))
+	{
+		$dbpasswd = $ENV{'OPENDMARC_PASSWORD'};
+	}
+	else
+	{
+		$dbpasswd = $def_dbpasswd;
+	}
+}
+
+if (!defined($dbport))
+{
+	if (defined($ENV{'OPENDMARC_PORT'}))
+	{
+		$dbport = $ENV{'OPENDMARC_PORT'};
+	}
+	else
+	{
+		$dbport = $def_dbport;
+	}
+}
+
+if (!defined($dbuser))
+{
+	if (defined($ENV{'OPENDMARC_USER'}))
+	{
+		$dbuser = $ENV{'OPENDMARC_USER'};
+	}
+	else
+	{
+		$dbuser = $def_dbuser;
+	}
+}
+
+if ($verbose)
+{
+	print STDERR "$progname: started at " . localtime() . "\n";
+}
+
+my $dbi_dsn = "DBI:" . $dbscheme . ":database=" . $dbname .
+              ";host=" . $dbhost . ";port=" . $dbport;
+
+$dbi_h = DBI->connect($dbi_dsn, $dbuser, $dbpasswd, { PrintError => 0 });
+if (!defined($dbi_h))
+{
+	print STDERR "$progname: unable to connect to database: $DBI::errstr\n";
+	exit(1);
+}
+
+if ($verbose)
+{
+	print STDERR "$progname: connected to database\n";
+}
+
+#
+# Read history file from stdin.
+#
+
+$lineno = 0;
+if (!flock(STDIN, LOCK_SH))
+{
+	print STDERR "$progname: warning: unable to establish read lock\n";
+}
+
+while (<STDIN>)
+{
+	$lineno++;
+
+	chomp;
+	($key, $value, $dkim_result) = split;
+
+	switch ($key)
+	{
+	  case "action"		{
+					$action = $value;
+				}
+
+	  case "adkim"		{
+					$adkim = $value;
+				}
+
+	  case "align_dkim"	{
+					$align_dkim = $value;
+				}
+
+	  case "align_spf"	{
+					$align_spf = $value;
+				}
+
+	  case "aspf"		{
+					$aspf = $value;
+				}
+
+	  case "dkim"		{
+					undef @dkim_entry;
+					push(@dkim_entry, $value);
+					push(@dkim_entry, $dkim_result);
+					if ($dkim_result eq "4" ||
+					    $dkim_result eq "5")
+					{
+						push(@dkim_entry, 1);
+					}
+					else
+					{
+						push(@dkim_entry, 0);
+					}
+					push(@dkim_data, [ @dkim_entry ]);
+
+					$sigcount++;
+				}
+
+	  case "from"		{
+					$fdomain = $value;
+				}
+
+	  case "job"		{
+					if (defined($jobid))
+					{
+						update_db();
+
+						undef $action;
+						undef $adkim;
+						undef $align_dkim;
+						undef $align_spf;
+						undef $aspf;
+						undef @dkim_data;
+						undef $envdomain;
+						undef $fdomain;
+						undef $ipaddr;
+						undef $jobid;
+						undef $p;
+						undef $pct;
+						undef $pdomain;
+						undef $policy;
+						undef $received;
+						undef $reporter;
+						undef @rua;
+						$sigcount = 0;
+						undef $sp;
+						undef $spf;
+					}
+
+					$jobid = $value;
+				}
+
+	  case "ipaddr"		{
+					$ipaddr = $value;
+				}
+
+	  case "mfrom"		{
+					$envdomain = $value;
+				}
+
+	  case "p"		{
+					$p = $value;
+				}
+
+	  case "pct"		{
+					$pct = $value;
+				}
+
+	  case "pdomain"	{
+					$pdomain = $value;
+				}
+
+	  case "policy"		{
+					$policy = $value;
+				}
+
+	  case "received"	{
+					$received = $value;
+				}
+
+	  case "reporter"	{
+					$reporter = $value;
+				}
+
+	  case "rua"		{
+					if ($value ne "-")
+					{
+						push(@rua, $value);
+					}
+				}
+
+	  case "sp"		{
+					$sp = $value;
+				}
+
+	  case "spf"		{
+					$spf = $value;
+				}
+
+	  else			{
+					print STDERR "$progname: unknown key '$key' at line $lineno\n";
+				}
+	}
+}
+
+if (defined($jobid))
+{
+	update_db();
+}
+
+#
+# all done!
+#
+
+if ($verbose)
+{
+	print STDERR "$progname: terminating at " . localtime() . "\n";
+}
+
+$dbi_h->disconnect;
+
+exit(0);
diff --git a/usr/sbin/opendmarc-importstats b/usr/sbin/opendmarc-importstats
new file mode 100755
index 0000000..839a871
--- /dev/null
+++ b/usr/sbin/opendmarc-importstats
@@ -0,0 +1,26 @@
+#!/bin/sh
+##
+## Copyright (c) 2012, The Trusted Domain Project.  All rights reserved.
+##
+## opendmarc-importstats -- import opendmarc output to MySQL
+##
+## This is intended to be used via a crontab.  If import is successful,
+## this code exits quietly so there's no output.  If it fails, it does
+## "ls -l" on the temporary file, so that cron generates mail to whever
+## ran the job.
+
+## setup
+statsdb="/var/tmp/dmarc.dat"
+# OPENDMARC_PASSWORD="password"; export OPENDMARC_PASSWORD
+
+if [ -s $statsdb ]
+then
+	mv $statsdb ${statsdb}.OLD.$$
+
+	if opendmarc-import < ${statsdb}.OLD.$$
+	then
+		rm ${statsdb}.OLD.$$
+	else
+		ls -l ${statsdb}.OLD.$$
+	fi
+fi
diff --git a/usr/sbin/opendmarc-params b/usr/sbin/opendmarc-params
new file mode 100755
index 0000000..726cd1f
--- /dev/null
+++ b/usr/sbin/opendmarc-params
@@ -0,0 +1,284 @@
+#!/usr/bin/perl
+#
+# Copyright (c) 2012, 2013, The Trusted Domain Project.  All rights reserved.
+#
+# Script to apply manual changes to DMARC reporting parameters.
+
+###
+### Setup
+###
+
+use strict;
+use warnings;
+
+use Switch;
+
+use DBI;
+use File::Basename;
+use Getopt::Long;
+use POSIX;
+
+require DBD::mysql;
+
+# general
+my $progname      = basename($0);
+my $version       = "1.3.1";
+my $verbose       = 0;
+my $helponly      = 0;
+my $showversion   = 0;
+
+# DB parameters
+my $def_dbhost    = "localhost";
+my $def_dbname    = "opendmarc";
+my $def_dbuser    = "opendmarc";
+my $def_dbpasswd  = "opendmarc";
+my $def_dbport    = "3306";
+my $dbhost;
+my $dbname;
+my $dbuser;
+my $dbpasswd;
+my $dbport;
+
+my $dbscheme     = "mysql";
+
+my $dbi_a;
+my $dbi_h;
+my $dbi_t;
+
+my $domain;
+my $domainid;
+my $requestid;
+my $rua;
+my $unlock;
+
+sub get_table_id
+{
+	my $name;
+	my $table;
+	my $column;
+	my $out;
+
+	($name, $table, $column) = @_;
+
+	if (!defined($name) || !defined($table))
+	{
+		return undef;
+	}
+
+	if (!defined($column))
+	{
+		$column = "name";
+	}
+
+	$dbi_t = $dbi_h->prepare("SELECT id FROM $table WHERE $column = ?");
+	if (!$dbi_t->execute($name))
+	{
+		print STDERR "$progname: failed to retrieve table ID: " . $dbi_h->errstr . "\n";
+		return undef;
+	}
+
+	undef $out;
+	while ($dbi_a = $dbi_t->fetchrow_arrayref())
+	{
+		if (defined($dbi_a->[0]))
+		{
+			$out = $dbi_a->[0];
+		}
+	}
+
+	$dbi_t->finish;
+
+	if (!defined($out))
+	{
+		$dbi_t = $dbi_h->prepare("INSERT INTO $table ($column) VALUES(?)");
+		if (!$dbi_t->execute($name))
+		{
+			print STDERR "$progname: failed to create table ID: " . $dbi_h->errstr . "\n";
+			return undef;
+		}
+
+		$dbi_t = $dbi_h->prepare("SELECT LAST_INSERT_ID()");
+		if (!$dbi_t->execute())
+		{
+			print STDERR "$progname: failed to retrieve created table ID: " . $dbi_h->errstr . "\n";
+			return undef;
+		}
+
+		while ($dbi_a = $dbi_t->fetchrow_arrayref())
+		{
+			if (defined($dbi_a->[0]))
+			{
+				$out = $dbi_a->[0];
+			}
+		}
+
+		$dbi_t->finish;
+
+		if (!defined($out))
+		{
+			print STDERR "$progname: failed to retrieve created table ID: " . $dbi_h->errstr . "\n";
+			return undef;
+		}
+	}
+
+	return $out;
+}
+
+sub usage
+{
+	print STDERR "$progname: usage: $progname [options] domain\n";
+	print STDERR "\t--dbhost=host      database host [$def_dbhost]\n";
+	print STDERR "\t--dbname=name      database name [$def_dbname]\n";
+	print STDERR "\t--dbpasswd=passwd  database password [$def_dbpasswd]\n";
+	print STDERR "\t--dbport=port      database port [$def_dbport]\n";
+	print STDERR "\t--dbuser=user      database user [$def_dbuser]\n";
+	print STDERR "\t--rua=string       aggregate report URI(s)\n";
+	print STDERR "\t--help             print help and exit\n";
+	print STDERR "\t--unlock           unlocks named record\n";
+	print STDERR "\t--verbose          verbose output\n";
+	print STDERR "\t--version          print version and exit\n";
+}
+
+# parse command line arguments
+my $opt_retval = &Getopt::Long::GetOptions ('dbhost=s' => \$dbhost,
+                                            'dbname=s' => \$dbname,
+                                            'dbpasswd=s' => \$dbpasswd,
+                                            'dbport=s' => \$dbport,
+                                            'dbuser=s' => \$dbuser,
+                                            'help!' => \$helponly,
+                                            'rua=s' => \$rua,
+                                            'unlock!' => \$unlock,
+                                            'verbose!' => \$verbose,
+                                            'version!' => \$showversion,
+                                           );
+
+$domain = $ARGV[0];
+
+if (!$opt_retval || $helponly || !defined($domain))
+{
+	usage();
+
+	if ($helponly)
+	{
+		exit(0);
+	}
+	else
+	{
+		exit(1);
+	}
+}
+
+if ($showversion)
+{
+	print STDOUT "$progname v$version\n";
+	exit(0);
+}
+
+# apply defaults
+if (!defined($dbhost))
+{
+	if (defined($ENV{'OPENDMARC_DBHOST'}))
+	{
+		$dbhost = $ENV{'OPENDMARC_DBHOST'};
+	}
+	else
+	{
+		$dbhost = $def_dbhost;
+	}
+}
+
+if (!defined($dbname))
+{
+	if (defined($ENV{'OPENDMARC_DB'}))
+	{
+		$dbname = $ENV{'OPENDMARC_DB'};
+	}
+	else
+	{
+		$dbname = $def_dbname;
+	}
+}
+
+if (!defined($dbpasswd))
+{
+	if (defined($ENV{'OPENDMARC_PASSWORD'}))
+	{
+		$dbpasswd = $ENV{'OPENDMARC_PASSWORD'};
+	}
+	else
+	{
+		$dbpasswd = $def_dbpasswd;
+	}
+}
+
+if (!defined($dbport))
+{
+	if (defined($ENV{'OPENDMARC_PORT'}))
+	{
+		$dbport = $ENV{'OPENDMARC_PORT'};
+	}
+	else
+	{
+		$dbport = $def_dbport;
+	}
+}
+
+if (!defined($dbuser))
+{
+	if (defined($ENV{'OPENDMARC_USER'}))
+	{
+		$dbuser = $ENV{'OPENDMARC_USER'};
+	}
+	else
+	{
+		$dbuser = $def_dbuser;
+	}
+}
+
+if ($verbose)
+{
+	print STDERR "$progname: started at " . localtime() . "\n";
+}
+
+my $dbi_dsn = "DBI:" . $dbscheme . ":database=" . $dbname .
+              ";host=" . $dbhost . ";port=" . $dbport;
+
+$dbi_h = DBI->connect($dbi_dsn, $dbuser, $dbpasswd, { PrintError => 0 });
+if (!defined($dbi_h))
+{
+	print STDERR "$progname: unable to connect to database: $DBI::errstr\n";
+	exit(1);
+}
+
+if ($verbose)
+{
+	print STDERR "$progname: connected to database\n";
+}
+
+$domainid = get_table_id($domain, "domains", "name");
+$requestid = get_table_id($domainid, "requests", "domain");
+
+if ($unlock)
+{
+	$dbi_t = $dbi_h->prepare("UPDATE requests SET locked = 0 WHERE id = ?");
+	if (!$dbi_t->execute($requestid))
+	{
+		print STDERR "$progname: failed to update requests table for $domain: " . $dbi_h->errstr . "\n";
+	}
+}
+else
+{
+	$dbi_t = $dbi_h->prepare("UPDATE requests SET locked = 1, repuri = ? WHERE id = ?");
+	if (!$dbi_t->execute($rua, $requestid))
+	{
+		print STDERR "$progname: failed to update requests table for $domain: " . $dbi_h->errstr . "\n";
+	}
+}
+
+#
+# all done!
+#
+
+$dbi_h->disconnect;
+
+exit(0);
diff --git a/usr/sbin/opendmarc-reports b/usr/sbin/opendmarc-reports
new file mode 100755
index 0000000..7616d8d
--- /dev/null
+++ b/usr/sbin/opendmarc-reports
@@ -0,0 +1,991 @@
+#!/usr/bin/perl
+#
+# Copyright (c) 2012-2015, The Trusted Domain Project.  All rights reserved.
+#
+# Script to generate regular DMARC reports.
+
+###
+### Setup
+###
+
+use strict;
+use warnings;
+
+use Switch;
+
+use DBI;
+use File::Basename;
+use File::Temp;
+use Net::Domain qw(hostfqdn hostdomain);
+use Getopt::Long;
+use IO::Handle;
+use IO::Compress::Zip qw(zip);
+use POSIX;
+use MIME::Base64;
+use Net::SMTP;
+
+require DBD::mysql;
+
+require HTTP::Request;
+
+# general
+my $progname      = basename($0);
+my $version       = "1.3.1";
+my $verbose       = 0;
+my $helponly      = 0;
+my $showversion   = 0;
+
+my $interval;
+
+my $gen;
+my $uri;
+
+my $buf;
+
+my $mailout;
+my $boundary;
+
+my $tmpout;
+
+my $repfile;
+my $zipfile;
+
+my $zipin;
+
+my $now;
+
+my $repstart;
+my $repend;
+
+my $domain;
+my $domainid;
+my $domainset;
+my $forcedomain;
+my @skipdomains;
+
+my $policy;
+my $spolicy;
+my $policystr;
+my $spolicystr;
+my $pct;
+
+my $repuri;
+my @repuris;
+my $lastsent;
+
+my $aspf;
+my $aspfstr;
+my $adkim;
+my $adkimstr;
+my $align_dkim;
+my $align_dkimstr;
+my $align_spf;
+my $align_spfstr;
+my $spfresult;
+my $dkimresult;
+my $disp;
+my $spfresultstr;
+my $dkimresultstr;
+my $dispstr;
+my $ipaddr;
+my $fromdomain;
+my $envdomain;
+my $dkimdomain;
+
+my $repdest;
+
+my $smtpstatus;
+my $smtpfail;
+
+my $doupdate = 1;
+my $testmode = 0;
+my $keepfiles = 0;
+my $use_utc = 0;
+my $daybound = 0;
+my $report_maxbytes_global = 15728640; # default: 15M, per spec
+
+my $msgid;
+
+my $rowcount;
+
+my $dbi_s;
+my $dbi_h;
+my $dbi_a;
+my $dbi_d;
+
+# DB parameters
+my $def_dbhost    = "localhost";
+my $def_dbname    = "opendmarc";
+my $def_dbuser    = "opendmarc";
+my $def_dbpasswd  = "opendmarc";
+my $def_dbport    = "3306";
+my $def_interval  = "86400";
+my $dbhost;
+my $dbname;
+my $dbuser;
+my $dbpasswd;
+my $dbport;
+
+my $dbscheme     = "mysql";
+
+my $repdom       = hostdomain();
+my $repemail     = "postmaster@" . $repdom;
+
+my $smtp_server  = '127.0.0.1';
+my $smtp_port    = 25;
+my $smtp;
+
+my $answer;
+
+###
+### NO user-serviceable parts beyond this point
+###
+
+sub usage
+{
+	print STDERR "$progname: usage: $progname [options]\n";
+	print STDERR "\t--day              send yesterday's data\n";
+	print STDERR "\t--dbhost=host      database host [$def_dbhost]\n";
+	print STDERR "\t--dbname=name      database name [$def_dbname]\n";
+	print STDERR "\t--dbpasswd=passwd  database password [$def_dbpasswd]\n";
+	print STDERR "\t--dbport=port      database port [$def_dbport]\n";
+	print STDERR "\t--dbuser=user      database user [$def_dbuser]\n";
+	print STDERR "\t--domain=name      force a report for named domain\n";
+	print STDERR "\t--help             print help and exit\n";
+	print STDERR "\t--interval=secs    report interval [$def_interval]\n";
+	print STDERR "\t--keepfiles        keep xml files (in local directory)\n";
+	print STDERR "\t -n                synonym for --test\n";
+	print STDERR "\t--nodomain=name    omit a report for named domain\n";
+	print STDERR "\t--noupdate         don't record report transmission\n";
+	print STDERR "\t--report-email     reporting contact [$repemail]\n";
+	print STDERR "\t--report-org       reporting organization [$repdom]\n";
+	print STDERR "\t--smtp-port        smtp server port [$smtp_port]\n";
+	print STDERR "\t--smtp-server      smtp server [$smtp_server]\n";
+	print STDERR "\t--test             don't send reports\n";
+	print STDERR "\t--utc              operate in UTC\n";
+	print STDERR "\t                   (implies --keepfiles --noupdate)\n";
+	print STDERR "\t--verbose          verbose output\n";
+	print STDERR "\t                   (repeat for increased output)\n";
+	print STDERR "\t--version          print version and exit\n";
+}
+
+# set locale
+setlocale(LC_ALL, 'C');
+
+# parse command line arguments
+my $opt_retval = &Getopt::Long::GetOptions ('day!' => \$daybound,
+                                            'dbhost=s' => \$dbhost,
+                                            'dbname=s' => \$dbname,
+                                            'dbpasswd=s' => \$dbpasswd,
+                                            'dbport=s' => \$dbport,
+                                            'dbuser=s' => \$dbuser,
+                                            'domain=s' => \$forcedomain,
+                                            'help!' => \$helponly,
+                                            'interval=i' => \$interval,
+                                            'keepfiles' => \$keepfiles,
+                                            'n|test' => \$testmode,
+                                            'nodomain=s' => \@skipdomains,
+                                            'report-email=s' => \$repemail,
+                                            'report-org=s' => \$repdom,
+                                            'smtp-server=s' => \$smtp_server,
+                                            'smtp-port=i' => \$smtp_port,
+                                            'update!' => \$doupdate,
+                                            'utc!' => \$use_utc,
+                                            'verbose+' => \$verbose,
+                                            'version!' => \$showversion,
+                                           );
+
+if (!$opt_retval || $helponly)
+{
+	usage();
+
+	if ($helponly)
+	{
+		exit(0);
+	}
+	else
+	{
+		exit(1);
+	}
+}
+
+if ($showversion)
+{
+	print STDOUT "$progname v$version\n";
+	exit(0);
+}
+
+# apply defaults
+if (!defined($dbhost))
+{
+	if (defined($ENV{'OPENDMARC_DBHOST'}))
+	{
+		$dbhost = $ENV{'OPENDMARC_DBHOST'};
+	}
+	else
+	{
+		$dbhost = $def_dbhost;
+	}
+}
+
+if (!defined($dbname))
+{
+	if (defined($ENV{'OPENDMARC_DB'}))
+	{
+		$dbname = $ENV{'OPENDMARC_DB'};
+	}
+	else
+	{
+		$dbname = $def_dbname;
+	}
+}
+
+if (!defined($dbpasswd))
+{
+	if (defined($ENV{'OPENDMARC_PASSWORD'}))
+	{
+		$dbpasswd = $ENV{'OPENDMARC_PASSWORD'};
+	}
+	else
+	{
+		$dbpasswd = $def_dbpasswd;
+	}
+}
+
+if (!defined($dbport))
+{
+	if (defined($ENV{'OPENDMARC_PORT'}))
+	{
+		$dbport = $ENV{'OPENDMARC_PORT'};
+	}
+	else
+	{
+		$dbport = $def_dbport;
+	}
+}
+
+if (!defined($dbuser))
+{
+	if (defined($ENV{'OPENDMARC_USER'}))
+	{
+		$dbuser = $ENV{'OPENDMARC_USER'};
+	}
+	else
+	{
+		$dbuser = $def_dbuser;
+	}
+}
+
+if (!defined($interval))
+{
+	$interval = $def_interval;
+}
+
+# Test mode requested, don't update last sent and keep xml files
+$doupdate  = ($testmode == 1) ? 0 : $doupdate;
+$keepfiles = ($testmode == 1) ? 1 : $keepfiles;
+
+if ($verbose)
+{
+	print STDERR "$progname: started at " . localtime() . "\n";
+}
+
+my $dbi_dsn = "DBI:" . $dbscheme . ":database=" . $dbname .
+              ";host=" . $dbhost . ";port=" . $dbport;
+
+$dbi_h = DBI->connect($dbi_dsn, $dbuser, $dbpasswd, { PrintError => 0 });
+if (!defined($dbi_h))
+{
+	print STDERR "$progname: unable to connect to database: $DBI::errstr\n";
+	exit(1);
+}
+
+if ($verbose >= 2)
+{
+	print STDERR "$progname: connected to database\n";
+}
+
+if ($use_utc)
+{
+	$dbi_s = $dbi_h->prepare("SET TIME_ZONE='+00:00'");
+
+	if (!$dbi_s->execute())
+	{
+		print STDERR "$progname: failed to change to UTC: " . $dbi_h->errstr . "\n";
+		$dbi_s->finish;
+		$dbi_h->disconnect;
+		exit(1);
+	}
+}
+
+#
+# Select domains on which to report
+#
+
+$now = time();
+
+if ($verbose >= 2)
+{
+	print STDERR "$progname: selecting target domains\n";
+}
+
+if (defined($forcedomain))
+{
+	$dbi_s = $dbi_h->prepare("SELECT name FROM domains WHERE name = ?");
+
+	if (!$dbi_s->execute($forcedomain))
+	{
+		print STDERR "$progname: failed to test for database entry: " . $dbi_h->errstr . "\n";
+		$dbi_s->finish;
+		$dbi_h->disconnect;
+		exit(1);
+	}
+}
+elsif ($daybound)
+{
+	$dbi_s = $dbi_h->prepare("SELECT domains.name FROM requests JOIN domains ON requests.domain = domains.id WHERE DATE(lastsent) < DATE(FROM_UNIXTIME(?))");
+
+	if (!$dbi_s->execute($now))
+	{
+		print STDERR "$progname: failed to collect domain names: " . $dbi_h->errstr . "\n";
+		$dbi_s->finish;
+		$dbi_h->disconnect;
+		exit(1);
+	}
+}
+else
+{
+	$dbi_s = $dbi_h->prepare("SELECT domains.name FROM requests JOIN domains ON requests.domain = domains.id WHERE lastsent <= DATE_SUB(FROM_UNIXTIME(?), INTERVAL ? SECOND)");
+
+	if (!$dbi_s->execute($now, $interval))
+	{
+		print STDERR "$progname: failed to collect domain names: " . $dbi_h->errstr . "\n";
+		$dbi_s->finish;
+		$dbi_h->disconnect;
+		exit(1);
+	}
+}
+
+$domainset = $dbi_s->fetchall_arrayref([0]);
+$dbi_s->finish;
+
+if ($verbose)
+{
+	print STDERR "$progname: selected " . scalar(@$domainset) . " domain(s)\n";
+}
+
+#
+# For each domain:
+# 	-- extract reporting address
+# 	-- extract messages/signatures to report
+# 	-- generate and send report
+# 	-- update "last sent" timestamp
+#
+
+$smtp = Net::SMTP->new($smtp_server,
+                       'Port' => $smtp_port,
+                       'Helo' => hostfqdn());
+if (!defined($smtp))
+{
+	print STDERR "$progname: open SMTP server $smtp_server:$smtp_port failed\n";
+	exit(1);
+}
+
+foreach (@$domainset)
+{
+	$domain = $_->[0];
+
+	if (!defined($domain))
+	{
+		next;
+	}
+
+	if (@skipdomains && grep({$_ eq $domain} @skipdomains) != 0)
+	{
+		next;
+	}
+
+	if ($verbose >= 2)
+	{
+		print STDERR "$progname: processing $domain\n";
+	}
+
+	# extract this domain's reporting parameters
+	$dbi_s = $dbi_h->prepare("SELECT id FROM domains WHERE name = ?");
+	if (!$dbi_s->execute($domain))
+	{
+		print STDERR "$progname: can't get ID for domain $domain: " . $dbi_h->errstr . "\n";
+		$dbi_s->finish;
+		$dbi_h->disconnect;
+		exit(1);
+	}
+
+	undef $domainid;
+	while ($dbi_a = $dbi_s->fetchrow_arrayref())
+	{
+		if (defined($dbi_a->[0]))
+		{
+			$domainid = $dbi_a->[0];
+		}
+	}
+	$dbi_s->finish;
+
+	if (!defined($domainid))
+	{
+		print STDERR "$progname: ID for domain $domain not found\n";
+		next;
+	}
+
+	$dbi_s = $dbi_h->prepare("SELECT repuri, adkim, aspf, policy, spolicy, pct, UNIX_TIMESTAMP(lastsent) FROM requests WHERE domain = ?");
+	if (!$dbi_s->execute($domainid))
+	{
+		print STDERR "$progname: can't get reporting URI for domain $domain: " . $dbi_h->errstr . "\n";
+		$dbi_s->finish;
+		$dbi_h->disconnect;
+		exit(1);
+	}
+
+	undef $repuri;
+
+	while ($dbi_a = $dbi_s->fetchrow_arrayref())
+	{
+		if (defined($dbi_a->[0]))
+		{
+			$repuri = $dbi_a->[0];
+		}
+		if (defined($dbi_a->[1]))
+		{
+			$adkim = $dbi_a->[1];
+		}
+		if (defined($dbi_a->[2]))
+		{
+			$aspf = $dbi_a->[2];
+		}
+		if (defined($dbi_a->[3]))
+		{
+			$policy = $dbi_a->[3];
+		}
+		if (defined($dbi_a->[4]))
+		{
+			$spolicy = $dbi_a->[4];
+		}
+		if (defined($dbi_a->[5]))
+		{
+			$pct = $dbi_a->[5];
+		}
+		if (defined($dbi_a->[6]))
+		{
+			$lastsent = $dbi_a->[6];
+		}
+	}
+
+	$dbi_s->finish;
+
+	if (!defined($repuri) || ("" eq $repuri))
+	{
+		if ($verbose >= 2)
+		{
+			print STDERR "$progname: no reporting URI for domain $domain; skipping\n";
+		}
+
+		next;
+	}
+
+	# construct the temporary file
+	$repfile = $repdom . "!" . $domain . "!" . $lastsent . "!" . time() . ".xml";
+	$zipfile = $repdom . "!" . $domain . "!" . $lastsent . "!" . time() . ".zip";
+	if (!open($tmpout, ">", $repfile))
+	{
+		print STDERR "$progname: can't create report file for domain $domain\n";
+		next;
+	}
+
+	switch ($adkim)
+	{
+		case ord("r")	{ $adkimstr = "r"; }
+		case ord("s")	{ $adkimstr = "s"; }
+		else		{ $adkimstr = "unknown"; }
+	}
+
+	switch ($aspf)
+	{
+		case ord("r")	{ $aspfstr = "r"; }
+		case ord("s")	{ $aspfstr = "s"; }
+		else		{ $aspfstr = "unknown"; }
+	}
+
+	switch ($policy)
+	{
+		case ord("n")	{ $policystr = "none"; }
+		case ord("q")	{ $policystr = "quarantine"; }
+		case ord("r")	{ $policystr = "reject"; }
+		else		{ $policystr = "unknown"; }
+	}
+
+ 	switch ($spolicy)
+ 	{
+ 		case ord("n")	{ $spolicystr = "none"; }
+ 		case ord("q")	{ $spolicystr = "quarantine"; }
+ 		case ord("r")	{ $spolicystr = "reject"; }
+ 	}
+
+	if ($daybound)
+	{
+		$dbi_s = $dbi_h->prepare("SELECT UNIX_TIMESTAMP(MIN(date)), UNIX_TIMESTAMP(MAX(date)) FROM messages WHERE from_domain = ? AND DATE(date) >= DATE(FROM_UNIXTIME(?)) AND DATE(date) < DATE(FROM_UNIXTIME(?))");
+	}
+	else
+	{
+		$dbi_s = $dbi_h->prepare("SELECT UNIX_TIMESTAMP(MIN(date)), UNIX_TIMESTAMP(MAX(date)) FROM messages WHERE from_domain = ? AND UNIX_TIMESTAMP(date) > ? AND UNIX_TIMESTAMP(date) <= ?");
+	}
+
+	if (!$dbi_s->execute($domainid, $lastsent, $now))
+	{
+		print STDERR "$progname: can't extract begin/end times for domain $domain: " . $dbi_h->errstr . "\n";
+		$dbi_s->finish;
+		$dbi_h->disconnect;
+		exit(1);
+	}
+
+	$repstart = 0;
+	$repend = $now;
+
+	while ($dbi_a = $dbi_s->fetchrow_arrayref())
+	{
+		if (defined($dbi_a->[0]))
+		{
+			$repstart = $dbi_a->[0];
+		}
+		if (defined($dbi_a->[1]))
+		{
+			$repend = $dbi_a->[1];
+		}
+	}
+
+	$dbi_s->finish;
+
+	print $tmpout "<?xml version=\"1.0\" encoding=\"UTF-8\" ?>\n";
+	print $tmpout "<feedback>\n";
+
+	print $tmpout " <report_metadata>\n";
+	print $tmpout "  <org_name>$repdom</org_name>\n";
+	print $tmpout "  <email>$repemail</email>\n";
+	print $tmpout "  <report_id>$domain:$now</report_id>\n";
+	print $tmpout "  <date_range>\n";
+	print $tmpout "   <begin>$repstart</begin>\n";
+	print $tmpout "   <end>$repend</end>\n";
+	print $tmpout "  </date_range>\n";
+	print $tmpout " </report_metadata>\n";
+
+	print $tmpout " <policy_published>\n";
+	print $tmpout "  <domain>$domain</domain>\n";
+	print $tmpout "  <adkim>$adkimstr</adkim>\n";
+	print $tmpout "  <aspf>$aspfstr</aspf>\n";
+	print $tmpout "  <p>$policystr</p>\n";
+	if (defined($spolicystr))
+	{
+		print $tmpout "  <sp>$spolicystr</sp>\n";
+	}
+	print $tmpout "  <pct>$pct</pct>\n";
+	print $tmpout " </policy_published>\n";
+
+	if ($daybound)
+	{
+		$dbi_s = $dbi_h->prepare("SELECT messages.id, ipaddr.addr, messages.disp, d1.name, d2.name, messages.spf, messages.align_spf, messages.align_dkim FROM messages JOIN ipaddr ON messages.ip = ipaddr.id JOIN domains d1 ON messages.from_domain = d1.id JOIN domains d2 ON messages.env_domain = d2.id WHERE messages.from_domain = ? AND DATE(messages.date) >= DATE(FROM_UNIXTIME(?)) AND DATE(messages.date) < DATE(FROM_UNIXTIME(?))");
+	}
+	else
+	{
+		$dbi_s = $dbi_h->prepare("SELECT messages.id, ipaddr.addr, messages.disp, d1.name, d2.name, messages.spf, messages.align_spf, messages.align_dkim FROM messages JOIN ipaddr ON messages.ip = ipaddr.id JOIN domains d1 ON messages.from_domain = d1.id JOIN domains d2 ON messages.env_domain = d2.id WHERE messages.from_domain = ? AND messages.date > FROM_UNIXTIME(?) AND messages.date <= FROM_UNIXTIME(?)");
+	}
+
+	if (!$dbi_s->execute($domainid, $lastsent, $now))
+	{
+		print STDERR "$progname: can't extract report for domain $domain: " . $dbi_h->errstr . "\n";
+		$dbi_s->finish;
+		$dbi_h->disconnect;
+		exit(1);
+	}
+
+	$rowcount = 0;
+
+	while ($dbi_a = $dbi_s->fetchrow_arrayref())
+	{
+		undef $msgid;
+
+		if (defined($dbi_a->[0]))
+		{
+			$msgid = $dbi_a->[0];
+		}
+		if (defined($dbi_a->[1]))
+		{
+			$ipaddr = $dbi_a->[1];
+		}
+		if (defined($dbi_a->[2]))
+		{
+			$disp = $dbi_a->[2];
+		}
+		if (defined($dbi_a->[3]))
+		{
+			$fromdomain = $dbi_a->[3];
+		}
+		if (defined($dbi_a->[4]))
+		{
+			$envdomain = $dbi_a->[4];
+		}
+		if (defined($dbi_a->[5]))
+		{
+			$spfresult = $dbi_a->[5];
+		}
+		if (defined($dbi_a->[6]))
+		{
+			$align_spf = $dbi_a->[6];
+		}
+		if (defined($dbi_a->[7]))
+		{
+			$align_dkim = $dbi_a->[7];
+		}
+
+		if (!defined($msgid))
+		{
+			next;
+		}
+
+		$rowcount++;
+
+		switch ($disp)
+		{
+			case 0	{ $dispstr = "reject"; }
+			case 1	{ $dispstr = "reject"; }
+			case 2	{ $dispstr = "none"; }
+			case 4	{ $dispstr = "quarantine"; }
+			else	{ $dispstr = "unknown"; }
+		}
+
+		switch ($spfresult)
+		{
+			case 0	{ $spfresultstr = "pass"; }
+			case 2	{ $spfresultstr = "softfail"; }
+			case 3	{ $spfresultstr = "neutral"; }
+			case 4	{ $spfresultstr = "temperror"; }
+			case 5	{ $spfresultstr = "permerror"; }
+			case 6	{ $spfresultstr = "none"; }
+			case 7	{ $spfresultstr = "fail"; }
+			case 8	{ $spfresultstr = "policy"; }
+			case 9	{ $spfresultstr = "nxdomain"; }
+			case 10	{ $spfresultstr = "signed"; }
+			case 12	{ $spfresultstr = "discard"; }
+			else	{ $spfresultstr = "unknown"; }
+		}
+
+		switch ($align_dkim)
+		{
+			case 4	{ $align_dkimstr = "pass"; }
+			case 5	{ $align_dkimstr = "fail"; }
+			else	{ $align_dkimstr = "unknown"; }
+		}
+
+		switch ($align_spf)
+		{
+			case 4	{ $align_spfstr = "pass"; }
+			case 5	{ $align_spfstr = "fail"; }
+			else	{ $align_spfstr = "unknown"; }
+		}
+
+		print $tmpout " <record>\n";
+		print $tmpout "  <row>\n";
+		print $tmpout "   <source_ip>$ipaddr</source_ip>\n";
+		print $tmpout "   <count>1</count>\n";
+		print $tmpout "   <policy_evaluated>\n";
+		print $tmpout "    <disposition>$dispstr</disposition>\n";
+		print $tmpout "    <dkim>$align_dkimstr</dkim>\n";
+		print $tmpout "    <spf>$align_spfstr</spf>\n";
+		print $tmpout "   </policy_evaluated>\n";
+		print $tmpout "  </row>\n";
+		print $tmpout "  <identifiers>\n";
+		print $tmpout "   <header_from>$fromdomain</header_from>\n";
+		print $tmpout "  </identifiers>\n";
+		print $tmpout "  <auth_results>\n";
+		print $tmpout "   <spf>\n";
+		print $tmpout "    <domain>$envdomain</domain>\n";
+		print $tmpout "    <result>$spfresultstr</result>\n";
+		print $tmpout "   </spf>\n";
+
+		$dbi_d = $dbi_h->prepare("SELECT domains.name, pass FROM signatures JOIN domains ON signatures.domain = domains.id WHERE signatures.message = ?");
+		if (!$dbi_d->execute($msgid))
+		{
+			print STDERR "$progname: can't extract report for message $msgid: " . $dbi_h->errstr . "\n";
+			$dbi_s->finish;
+			$dbi_d->finish;
+			$dbi_h->disconnect;
+			exit(1);
+		}
+
+		while ($dbi_a = $dbi_d->fetchrow_arrayref())
+		{
+			undef $dkimdomain;
+
+			if (defined($dbi_a->[0]))
+			{
+				$dkimdomain = $dbi_a->[0];
+			}
+			if (defined($dbi_a->[1]))
+			{
+				$dkimresult = $dbi_a->[1];
+			}
+
+
+			if (!defined($dkimdomain))
+			{
+				next;
+			}
+
+			switch ($dkimresult)
+			{
+				case 0	{ $dkimresultstr = "pass"; }
+				case 2	{ $dkimresultstr = "softfail"; }
+				case 3	{ $dkimresultstr = "neutral"; }
+				case 4	{ $dkimresultstr = "temperror"; }
+				case 5	{ $dkimresultstr = "permerror"; }
+				case 6	{ $dkimresultstr = "none"; }
+				case 7	{ $dkimresultstr = "fail"; }
+				case 8	{ $dkimresultstr = "policy"; }
+				case 9	{ $dkimresultstr = "nxdomain"; }
+				case 10	{ $dkimresultstr = "signed"; }
+				case 12	{ $dkimresultstr = "discard"; }
+				else	{ $dkimresultstr = "unknown"; }
+			}
+
+			print $tmpout "   <dkim>\n";
+			print $tmpout "    <domain>$dkimdomain</domain>\n";
+			print $tmpout "    <result>$dkimresultstr</result>\n";
+			print $tmpout "   </dkim>\n";
+		}
+
+		$dbi_d->finish;
+
+		print $tmpout "  </auth_results>\n";
+		print $tmpout " </record>\n";
+	}
+
+	$dbi_s->finish;
+
+	print $tmpout "</feedback>\n";
+
+	close($tmpout);
+
+	if ($rowcount == 0)
+	{
+		if ($verbose >= 2)
+		{
+			print STDERR "$progname: no activity selected for $domain; skipping\n";
+		}
+
+		unlink($repfile);
+		next;
+	}
+
+	# zip the report
+	if (!zip [ $repfile ] => $zipfile)
+	{
+		print STDERR "$progname: can't zip report for domain $domain: $!\n";
+		next;
+	}
+
+	if ($keepfiles)
+	{
+		print STDERR "$progname: keeping report file \"$repfile\"\n";
+	}
+
+	# decode the URI
+	@repuris = split(',', $repuri);
+
+	for $repuri (@repuris)
+	{
+		$uri = URI->new($repuri);
+		if (!defined($uri) ||
+		    !defined($uri->scheme) ||
+		    $uri->opaque eq "")
+		{
+			print STDERR "$progname: can't parse reporting URI for domain $domain\n";
+			unlink($zipfile);
+			unlink($repfile);
+			next;
+		}
+
+		$repdest = $uri->opaque;
+		my $report_maxbytes = $report_maxbytes_global;
+
+		# check for max report size
+		if ($repdest =~ m/^(\S+)!(\d{1,15})([kmgt])?$/i)
+		{
+			$repdest = $1;
+			$report_maxbytes = $2;
+			if ($3)
+			{
+				my $letter = lc($3);
+				if ($letter eq 'k')
+				{
+					$report_maxbytes = $report_maxbytes * 1024;
+				}
+				if ($letter eq 'm')
+				{
+					$report_maxbytes = $report_maxbytes * 1048576;
+				}
+				if ($letter eq 'g')
+				{
+					$report_maxbytes = $report_maxbytes * (2**30);
+				}
+				if ($letter eq 't')
+				{
+					$report_maxbytes = $report_maxbytes * (2**40);
+				}
+			}
+		}
+
+		# Test mode, just report what would have been done
+		if ($testmode)
+		{
+			print STDERR "$progname: would email $domain report for " .
+			             "$rowcount records to " .  $uri->opaque . "\n";
+		}
+		# ensure a scheme is present
+		elsif (!defined($uri->scheme))
+		{
+			if ($verbose >= 2)
+			{
+				print STDERR "$progname: unknown URI scheme in '$repuri' for domain $domain\n";
+			}
+
+			unlink($zipfile);
+			unlink($repfile);
+			next;
+		}
+		# send/post report
+		elsif ($uri->scheme eq "mailto")
+		{
+			my $datestr;
+			my $report_id;
+
+			if (!open($zipin, $zipfile))
+			{
+				print STDERR "$progname: can't read zipped report for $domain: $!\n";
+				unlink($zipfile);
+				unlink($repfile);
+				next;
+			}
+
+			$boundary = "report_section";
+
+ 			$report_id = $domain . "-" . $now . "@" . $repdom;
+			$datestr = strftime("%a, %e %b %Y %H:%M:%S %z (%Z)",
+			                    localtime);
+
+			$mailout  = "To: $repdest\n";
+			$mailout .= "From: $repemail\n";
+			$mailout .= "Subject: Report Domain: " . $domain . " Submitter: " . $repdom . " Report-ID: " . $report_id . "\n";
+			$mailout .= "X-Mailer: " . $progname . " v" . $version ."\n";
+			$mailout .= "Date: " . $datestr . "\n";
+			$mailout .= "Message-ID: <$report_id>\n";
+			$mailout .= "Auto-Submitted: auto-generated\n";
+			$mailout .= "MIME-Version: 1.0\n";
+			$mailout .= "Content-Type: multipart/mixed; boundary=\"$boundary\"\n";
+			$mailout .= "\n";
+			$mailout .= "This is a MIME-encapsulated message.\n";
+			$mailout .= "\n";
+			$mailout .= "--$boundary\n";
+			$mailout .= "Content-Type: text/plain;\n";
+			$mailout .= "\n";
+			$mailout .= "This is a DMARC aggregate report for $domain\n";
+			$mailout .= "generated at " . localtime() . "\n";
+			$mailout .= "\n";
+			$mailout .= "--$boundary\n";
+			$mailout .= "Content-Type: application/zip\n";
+			$mailout .= "Content-Disposition: attachment; filename=\"$zipfile\"\n";
+			$mailout .= "Content-Transfer-Encoding: base64\n";
+			$mailout .= "\n";
+
+			while (read($zipin, $buf, 60*57))
+			{
+				$mailout .= encode_base64($buf);
+			}
+
+			$mailout .= "\n";
+			$mailout .= "--$boundary--\n";
+			my $reportsize = length($mailout);
+
+			if ($reportsize > $report_maxbytes)
+			{
+				# XXX -- generate an error report here
+				print STDERR "$progname: report was too large ($reportsize bytes) per limitation of URI " . $uri->opaque . " for domain $domain\n";
+			}
+			else
+			{
+				$smtpstatus = "sent";
+				$smtpfail = 0;
+				if (!$smtp->mail($repemail) ||
+				    !$smtp->to($repdest) ||
+				    !$smtp->data() ||
+				    !$smtp->datasend($mailout) ||
+				    !$smtp->dataend())
+				{
+					$smtpfail = 1;
+					$smtpstatus = "failed to send";
+				}
+
+				if ($verbose || $smtpfail)
+				{
+					# now perl voodoo:
+					$answer = ${${*$smtp}{'net_cmd_resp'}}[1];
+					chomp($answer);
+					print STDERR "$progname: $smtpstatus report for $domain to $repdest ($answer)\n";
+				}
+			}
+
+			$smtp->reset();
+
+			close($zipin);
+		}
+		else
+		{
+			print STDERR "$progname: unsupported reporting URI scheme " . $uri->scheme . " for domain $domain\n";
+			unlink($zipfile);
+			unlink($repfile);
+			next;
+		}
+	}
+
+	# update "last sent" timestamp
+	if ($doupdate)
+	{
+		$dbi_s = $dbi_h->prepare("UPDATE requests SET lastsent = FROM_UNIXTIME(?) WHERE domain = ?");
+		if (!$dbi_s->execute($now, $domainid))
+		{
+			print STDERR "$progname: can't update last sent time for domain $domain: " . $dbi_h->errstr . "\n";
+			$dbi_s->finish;
+			$dbi_h->disconnect;
+			exit(1);
+		}
+	}
+
+	unlink($zipfile);
+	if (!$keepfiles)
+	{
+		unlink($repfile);
+	}
+}
+
+$smtp->quit();
+
+#
+# all done!
+#
+
+$dbi_s->finish;
+
+if ($verbose)
+{
+	print STDERR "$progname: terminating at " . localtime() . "\n";
+}
+
+$dbi_h->disconnect;
+
+exit(0);
diff --git a/usr/share/doc/opendmarc/README.Debian b/usr/share/doc/opendmarc/README.Debian
new file mode 100644
index 0000000..384fc8b
--- /dev/null
+++ b/usr/share/doc/opendmarc/README.Debian
@@ -0,0 +1,48 @@
+opendmarc for Debian
+-------------------
+
+Configuration Notes for Debian systes
+--------------------------------------------
+
+The DMARC protocol is built on top of SPF and DKIM.  OpenDMARC needs SPF and
+DKIM verification results as an input.  OpenDMARC uses RFC 5451 Authentication
+Results header fields to get those results.  OpenDMARC will use header fields
+with an AuthservID that matches either the one specified in
+/etc/opendmarc.conf or the system hostname.  It is important to verify that
+the AuthservID provided by SPF and DKIM verifiers matches the one that
+opendmarc expects.
+
+In Debian, postfix-policyd-spf-python and opendkim have been tested to
+generate appropriate A-R header fields.  For postfix-policyd-spf-python,
+however, it is not the default configuration.  See man 5 policyd-spf.conf for
+information on how to configure it to generate A-R header fields.
+
+To generate aggregate feedback reports a MySQL database is needed.  See the
+man pages for opendmarc-expire, opendmarc-import, opendmarc-params, and
+opendmarc-reports for details on how the aggregate report data collection and
+report generation works.  The database schema, setup script, and README.schema
+files can be found in /usr/share/doc/opendmarc.
+
+Notes for Postfix users
+-----------------------
+
+Postfix users who wish to access the opendmarc service via UNIX socket
+may need to add the postfix user to the opendmarc group and ensure that
+UMask is set to 002 in /etc/opendkim.conf, in order to make the socket
+ readable by Posfix.
+
+Users may also need to move the socket into a directory accessible by the
+Postfix chroot; this can be accomplished by setting the SOCKET variable
+in /etc/default/opendmarc.
+
+The default is to connect to the filter over TCP. The filter can be bound to
+localhost to prevent other hosts from accessing it.  For example, to bind to
+port 8892, specify "inet:8892@localhost".
+
+Changing group ownership of socket
+----------------------------------
+
+The group ID of the UNIX socket created by opendkim can be changed by
+changing the primary GID of the opendmarc user, e.g.:
+$ usermod -g mail opendmarc
+
diff --git a/usr/share/doc/opendmarc/README.schema b/usr/share/doc/opendmarc/README.schema
new file mode 100644
index 0000000..0f983d2
--- /dev/null
+++ b/usr/share/doc/opendmarc/README.schema
@@ -0,0 +1,39 @@
+This directory contains the OpenDMARC schema plus any related files.
+
+The tables in this schema are populated by the opendmarc filter as it processes
+messages and downloads policies.  The rows are then consumed by the scripts
+in the "reports" directory to generate regular aggregate reports.
+
+The tables are summarized here:
+
+domains		A table that maps domain names to unique integer IDs.
+		Automatically tracks a "first seen" timestamp, and includes
+		a column to record when the last report was sent.
+
+reporters	A table mapping reporting hosts to unique integer IDs.
+		Intended for use by multi-MX systems so it's possible to tell
+		where an inbound message landed.
+
+ipaddr		A table mapping IP addresses (as strings) to unique IDs.
+		Also tracks the "first seen" timestamp for each.
+
+messages	A table tracking salient properties of all messages received.
+		A messages is uniquely identified by a {date, jobid, reporter}
+		tuple.  Includes references to the "domains" table to track
+		the RFC5321.MailFrom domain, the RFC5322.From domain.
+		Also records the count of DKIM signatures, the SPF result,
+		and whether or not the SPF result was aligned with the
+		RFC5322.From domain.
+
+signatures	A table tracking DKIM signatures, each of which refers to
+		a rown in the "messages" table.  Tracks the signing domain,
+		whether the signature passed, whether there was a verification
+		error other than a broken signature, and whether or not the
+		signing domain aligned with the RFC5322.From domain.
+
+requests	A table containing a cache of DMARC reporting requests.
+		For each domain, the destination reporting URI for aggregate
+		reports is recorded along with a "last report sent" timestamp.
+
+--
+Copyright (c) 2012, The Trusted Domain Project.  All rights reserved.
diff --git a/usr/share/doc/opendmarc/copyright b/usr/share/doc/opendmarc/copyright
new file mode 100644
index 0000000..58d97f6
--- /dev/null
+++ b/usr/share/doc/opendmarc/copyright
@@ -0,0 +1,170 @@
+This package was debianized by Scott Kitterman <scott@kitterman.com> on
+Tue, 30 Oct 2012 14:46:53 +0100.
+
+It was downloaded from http://sourceforge.net/projects/opendkim
+
+Copyright Holder: The OpenDKIM Project.
+
+Based on code from DKIM Milter, copyright Sendmail Inc.
+
+Copyright:
+Copyright (c) 2009, 2010, 2012, 2013, 2014 The Trusted Domain Project.
+All rights reserved.
+
+Redistribution and use in source and binary forms, with or without
+modification, are permitted provided that the following conditions are met:
+    * Redistributions of source code must retain the above copyright
+      notice, this list of conditions and the following disclaimer.
+    * Redistributions in binary form must reproduce the above copyright
+      notice, this list of conditions and the following disclaimer in the
+      documentation and/or other materials provided with the distribution.
+    * Neither the name of The Trusted Domain Project nor the names of its
+      contributors may be used to endorse or promote products derived from
+      this software without specific prior written permission.
+
+Portions of this project are also covered by the Sendmail Open Source
+License, available in this distribution in the file "LICENSE.Sendmail".
+See the copyright notice(s) in each file to determine whether it is covered
+by either or both of the licenses.  For example:
+
+	Copyright (c) <year> Sendmail, Inc. and its suppliers.
+	  All rights reserved.
+
+Files bearing the banner above are covered under the Sendmail Open Source
+License (see LICENSE.Sendmail).
+
+	Copyright (c) <year>, The Trusted Domain Project.
+	  All rights reserved.
+
+Files bearing the banner above are covered under the Trusted Domain Project
+License (above).
+
+Files bearing both banners are covered under both sets of license terms.
+
+THIS SOFTWARE IS PROVIDED BY THE TRUSTED DOMAIN PROJECT ''AS IS'' AND ANY
+EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED
+WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE
+DISCLAIMED. IN NO EVENT SHALL THE TRUSTED DOMAIN PROJECT BE LIABLE FOR ANY
+DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES
+(INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
+LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
+ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
+(INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
+SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
+
+For files:
+opendmarc/parse.h  Copyright (c) 2004 Sendmail, Inc. and its suppliers.
+opendmarc/opendmarc-ar.c  Copyright (c) 2007-2009 Sendmail, Inc. and its suppliers.
+opendmarc/opendmarc-dstring.c  Copyright (c) 2005-2009 Sendmail, Inc. and its suppliers.
+opendmarc/opendmarc-dstring.h  Copyright (c) 2004, 2005, 2007-2009 Sendmail, Inc. and its suppliers.
+opendmarc/opendmarc-ar.h  Copyright (c) 2007-2009 Sendmail, Inc. and its suppliers.
+opendmarc/config.c  Copyright (c) 2006-2009 Sendmail, Inc. and its suppliers.
+opendmarc/parse.c  Copyright (c) 2005, 2007, 2008 Sendmail, Inc. and its suppliers.
+opendmarc/config.h  Copyright (c) 2006-2008 Sendmail, Inc. and its suppliers
+
+                   SENDMAIL OPEN SOURCE LICENSE
+
+The following license terms and conditions apply to this open source
+software ("Software"), unless a different license is obtained directly
+from Sendmail, Inc. ("Sendmail") located at 6475 Christie Ave, Suite 350,
+Emeryville, CA 94608, USA.
+
+Use, modification and redistribution (including distribution of any
+modified or derived work) of the Software in source and binary forms is
+permitted only if each of the following conditions of 1-6 are met:
+
+1. Redistributions of the Software qualify as "freeware" or "open
+   source software" under one of the following terms:
+
+   (a) Redistributions are made at no charge beyond the reasonable
+       cost of materials and delivery; or
+
+   (b) Redistributions are accompanied by a copy of the modified
+       Source Code (on an acceptable machine-readable medium) or by an
+       irrevocable offer to provide a copy of the modified Source Code
+       (on an acceptable machine-readable medium) for up to three years
+       at the cost of materials and delivery. Such redistributions must
+       allow further use, modification, and redistribution of the Source
+       Code under substantially the same terms as this license. For
+       the purposes of redistribution "Source Code" means the complete
+       human-readable, compilable, linkable, and operational source
+       code of the redistributed module(s) including all modifications.
+
+2. Redistributions of the Software Source Code must retain the
+   copyright notices as they appear in each Source Code file, these
+   license terms and conditions, and the disclaimer/limitation of
+   liability set forth in paragraph 6 below. Redistributions of the
+   Software Source Code must also comply with the copyright notices
+   and/or license terms and conditions imposed by contributors on
+   embedded code. The contributors' license terms and conditions
+   and/or copyright notices are contained in the Source Code
+   distribution.
+
+3. Redistributions of the Software in binary form must reproduce the
+   Copyright Notice described below, these license terms and conditions,
+   and the disclaimer/limitation of liability set forth in paragraph
+   6 below, in the documentation and/or other materials provided with
+   the binary distribution.  For the purposes of binary distribution,
+   "Copyright Notice" refers to the following language: "Copyright (c)
+   1998-2009 Sendmail, Inc. All rights reserved."
+
+4. Neither the name, trademark or logo of Sendmail, Inc. (including
+   without limitation its subsidiaries or affiliates) or its contributors
+   may be used to endorse or promote products, or software or services
+   derived from this Software without specific prior written permission.
+   The name "sendmail" is a registered trademark and service mark of
+   Sendmail, Inc.
+
+5. We reserve the right to cancel this license if you do not comply with 
+   the terms.  This license is governed by California law and both of us 
+   agree that for any dispute arising out of or relating to this Software, 
+   that jurisdiction and venue is proper in San Francisco or Alameda 
+   counties.  These license terms and conditions reflect the complete 
+   agreement for the license of the Software (which means this supercedes 
+   prior or contemporaneous agreements or representations).  If any term
+   or condition under this license is found to be invalid, the remaining
+   terms and conditions still apply.
+
+6. Disclaimer/Limitation of Liability: THIS SOFTWARE IS PROVIDED BY
+   SENDMAIL AND ITS CONTRIBUTORS "AS IS" WITHOUT WARRANTY OF ANY KIND
+   AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
+   IMPLIED WARRANTIES OF MERCHANTABILITY, NON-INFRINGEMENT AND FITNESS FOR A
+   PARTICULAR PURPOSE ARE EXPRESSLY DISCLAIMED. IN NO EVENT SHALL SENDMAIL
+   OR ITS CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL,
+   SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED
+   TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA,
+   OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY
+   OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING
+   WITHOUT LIMITATION NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE
+   USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGES.
+
+$Revision: 1.1 $ $Date: 2009/07/16 18:43:18 $
+
+For file contrib/rddmarc/dmarcfail.py:
+
+# Copyright 2012, Taughannock Networks. All rights reserved.
+
+# Redistribution and use in source and binary forms, with or without
+# modification, are permitted provided that the following conditions
+# are met:
+
+# Redistributions of source code must retain the above copyright
+# notice, this list of conditions and the following disclaimer.
+
+# Redistributions in binary form must reproduce the above copyright
+# notice, this list of conditions and the following disclaimer in the
+# documentation and/or other materials provided with the distribution.
+
+# THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS
+# "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT
+# LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR
+# A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT
+# HOLDER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT,
+# INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING,
+# BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS
+# OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED
+# AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT
+# LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY
+# WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE
+# POSSIBILITY OF SUCH DAMAGE.
+
diff --git a/usr/share/doc/opendmarc/mkdb.mysql b/usr/share/doc/opendmarc/mkdb.mysql
new file mode 100644
index 0000000..83f5de5
--- /dev/null
+++ b/usr/share/doc/opendmarc/mkdb.mysql
@@ -0,0 +1,77 @@
+-- Copyright (c) 2013, The Trusted Domain Project.  All rights reserved.
+
+-- MySQL command sequence to create a database to accumulate OpenDMARC
+-- report data
+
+-- 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 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 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 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 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;
diff --git a/usr/share/doc/opendmarc/schema.mysql b/usr/share/doc/opendmarc/schema.mysql
new file mode 100644
index 0000000..3f878cb
--- /dev/null
+++ b/usr/share/doc/opendmarc/schema.mysql
@@ -0,0 +1,93 @@
+-- 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';