From 7df5e532c1ce2ab9e8527615c08c1178990870e6 Mon Sep 17 00:00:00 2001
From: =?utf8?q?Isma=C3=ABl=20Bouya?= <ismael.bouya@normalesup.org>
Date: Sun, 26 Apr 2020 03:04:56 +0200
Subject: [PATCH] Add dmarc reports

---
 modules/private/environment.nix               |   8 ++
 .../private/websites/tools/tools/default.nix  |  16 +++
 .../websites/tools/tools/dmarc_reports.nix    |  56 ++++++++
 .../tools/tools/dmarc_reports/api.php         |  87 ++++++++++++
 .../websites/tools/tools/dmarc_reports/app.js |  86 ++++++++++++
 .../tools/tools/dmarc_reports/default.css     | 126 ++++++++++++++++++
 .../tools/tools/dmarc_reports/index.html      | 118 ++++++++++++++++
 .../websites/tools/tools/landing/config.yml   |   3 +
 8 files changed, 500 insertions(+)
 create mode 100644 modules/private/websites/tools/tools/dmarc_reports.nix
 create mode 100644 modules/private/websites/tools/tools/dmarc_reports/api.php
 create mode 100644 modules/private/websites/tools/tools/dmarc_reports/app.js
 create mode 100644 modules/private/websites/tools/tools/dmarc_reports/default.css
 create mode 100644 modules/private/websites/tools/tools/dmarc_reports/index.html

diff --git a/modules/private/environment.nix b/modules/private/environment.nix
index f7994a1..5fbd023 100644
--- a/modules/private/environment.nix
+++ b/modules/private/environment.nix
@@ -820,6 +820,14 @@ in
               };
             };
           };
+          dmarc_reports = mkOption {
+            description = "DMARC reports configuration";
+            type = submodule {
+              options = {
+                mysql = mkMysqlOptions "DMARC" {};
+              };
+            };
+          };
           etherpad-lite = mkOption {
             description = "Etherpad configuration";
             type = submodule {
diff --git a/modules/private/websites/tools/tools/default.nix b/modules/private/websites/tools/tools/default.nix
index 0cb7a10..a5e7f2e 100644
--- a/modules/private/websites/tools/tools/default.nix
+++ b/modules/private/websites/tools/tools/default.nix
@@ -47,6 +47,9 @@ let
   webhooks = pkgs.callPackage ./webhooks.nix {
     env = config.myEnv.tools.webhooks;
   };
+  dmarc-reports = pkgs.callPackage ./dmarc_reports.nix {
+    env = config.myEnv.tools.dmarc_reports;
+  };
 
   landing = pkgs.callPackage ./landing.nix {};
 
@@ -65,6 +68,7 @@ in {
       ++ ttrss.keys
       ++ wallabag.keys
       ++ yourls.keys
+      ++ dmarc-reports.keys
       ++ webhooks.keys;
 
     services.duplyBackup.profiles = {
@@ -88,6 +92,7 @@ in {
       ++ rompr.apache.modules
       ++ shaarli.apache.modules
       ++ dokuwiki.apache.modules
+      ++ dmarc-reports.apache.modules
       ++ phpbb.apache.modules
       ++ ldap.apache.modules
       ++ kanboard.apache.modules;
@@ -147,6 +152,7 @@ in {
         (kanboard.apache.vhostConf pcfg.kanboard.socket)
         (grocy.apache.vhostConf pcfg.grocy.socket)
         (phpbb.apache.vhostConf pcfg.phpbb.socket)
+        (dmarc-reports.apache.vhostConf pcfg.dmarc-reports.socket)
         ''
           Alias /paste /var/lib/fiche
           <Directory "/var/lib/fiche">
@@ -342,6 +348,15 @@ in {
         group = "wwwrun";
         settings = shaarli.phpFpm.pool;
       };
+      dmarc-reports = {
+        user = "wwwrun";
+        group = "wwwrun";
+        settings = dmarc-reports.phpFpm.pool;
+        phpEnv = dmarc-reports.phpFpm.phpEnv;
+        phpOptions = config.services.phpfpm.phpOptions + ''
+          extension=${pkgs.php}/lib/php/extensions/mysqli.so
+        '';
+      };
       dokuwiki = {
         user = "wwwrun";
         group = "wwwrun";
@@ -386,6 +401,7 @@ in {
 
     services.websites.webappDirs = {
       _adminer = adminer.webRoot;
+      "${dmarc-reports.apache.webappName}" = dmarc-reports.webRoot;
       "${dokuwiki.apache.webappName}" = dokuwiki.webRoot;
       "${phpbb.apache.webappName}" = phpbb.webRoot;
       "${ldap.apache.webappName}" = "${ldap.webRoot}/htdocs";
diff --git a/modules/private/websites/tools/tools/dmarc_reports.nix b/modules/private/websites/tools/tools/dmarc_reports.nix
new file mode 100644
index 0000000..2e44526
--- /dev/null
+++ b/modules/private/websites/tools/tools/dmarc_reports.nix
@@ -0,0 +1,56 @@
+{ env }:
+rec {
+  keys = [{
+    dest = "webapps/tools-dmarc-reports.php";
+    user = "wwwrun";
+    group = "wwwrun";
+    permissions = "0400";
+    text = ''
+      <?php
+      $dbhost = "${env.mysql.host}";
+      $dbname = "${env.mysql.database}";
+      $dbuser = "${env.mysql.user}";
+      $dbpass = "${env.mysql.password}";
+      $dbport = "${env.mysql.port}";
+      ?>
+    '';
+  }];
+  webRoot = ./dmarc_reports;
+  apache = rec {
+    user = "wwwrun";
+    group = "wwwrun";
+    modules = [ "proxy_fcgi" ];
+    webappName = "tools_dmarc_reports";
+    root = "/run/current-system/webapps/${webappName}";
+    vhostConf = socket: ''
+      Alias /dmarc-reports "${root}"
+      <Directory "${root}">
+        DirectoryIndex index.html
+        <FilesMatch "\.php$">
+          SetHandler "proxy:unix:${socket}|fcgi://localhost"
+        </FilesMatch>
+
+        AllowOverride None
+        Options +FollowSymlinks
+        Require all granted
+      </Directory>
+      '';
+  };
+  phpFpm = rec {
+    basedir = builtins.concatStringsSep ":"
+      [ webRoot "/var/secrets/webapps/tools-dmarc-reports.php" ];
+    pool = {
+      "listen.owner" = apache.user;
+      "listen.group" = apache.group;
+      "pm" = "ondemand";
+      "pm.max_children" = "60";
+      "pm.process_idle_timeout" = "60";
+
+      # Needed to avoid clashes in browser cookies (same domain)
+      "php_admin_value[open_basedir]" = "${basedir}:/tmp";
+    };
+    phpEnv = {
+      SECRETS_FILE = "/var/secrets/webapps/tools-dmarc-reports.php";
+    };
+  };
+}
diff --git a/modules/private/websites/tools/tools/dmarc_reports/api.php b/modules/private/websites/tools/tools/dmarc_reports/api.php
new file mode 100644
index 0000000..9b7f0c0
--- /dev/null
+++ b/modules/private/websites/tools/tools/dmarc_reports/api.php
@@ -0,0 +1,87 @@
+<?php
+
+require(getenv("SECRETS_FILE"));
+
+$response = array(
+  "status" => "ok",
+);
+$mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname, $dbport);
+
+function error_die($text, $number) {
+  http_response_code("500");
+  $message = array(
+      "status"  => "error",
+      "message" => $text,
+      "code"    => $number
+  );
+
+  die(json_encode($message));
+}
+
+if ($mysqli->connect_errno) {
+  error_die($mysqli->connect_error, $mysqli->connect_errno);
+}
+
+if (!isset($_GET['serial'])) {
+  $response["domains"] = array();
+  $query = $mysqli->query("SELECT DISTINCT domain FROM `report` ORDER BY domain");
+  if ($mysqli->error) { error_die($mysqli->error, $mysqli->errno); }
+  while($row = $query->fetch_assoc()) {
+    $response["domains"][] = $row['domain'];
+  }
+
+  $response["orgs"] = array();
+  $query = $mysqli->query("SELECT DISTINCT org FROM `report` ORDER BY org");
+  if ($mysqli->error) { error_die($mysqli->error, $mysqli->errno); }
+  while($row = $query->fetch_assoc()) {
+    $response["orgs"][] = $row['org'];
+  }
+
+  $response["dates"] = array();
+  $query = $mysqli->query("SELECT DISTINCT DISTINCT year(mindate) as year, month(mindate) as month FROM `report` ORDER BY year DESC,month DESC");
+  if ($mysqli->error) { error_die($mysqli->error, $mysqli->errno); }
+  while($row = $query->fetch_assoc()) {
+    $response["dates"][] = sprintf( "%'.04d-%'.02d", $row['year'], $row['month'] );
+  }
+
+  $response["summaries"] = array();
+  if (isset($_GET['errors_only'])) {
+    $where = " WHERE (spfresult != 'pass' or dkimresult != 'pass')";
+  } else {
+    $where = "";
+  }
+
+  $sql = "SELECT report.* , sum(rptrecord.rcount) AS rcount, MIN(rptrecord.dkimresult) AS dkimresult, MIN(rptrecord.spfresult) AS spfresult FROM report LEFT JOIN (SELECT rcount, COALESCE(dkimresult, 'neutral') AS dkimresult, COALESCE(spfresult, 'neutral') AS spfresult, serial FROM rptrecord) AS rptrecord ON report.serial = rptrecord.serial$where GROUP BY serial ORDER BY mindate ASC, maxdate ASC, org";
+  $query = $mysqli->query($sql);
+  if ($mysqli->error) { error_die($mysqli->error, $mysqli->errno); }
+  while($row = $query->fetch_assoc()) {
+    unset($row["raw_xml"]);
+    $response["summaries"][] = $row;
+  }
+} else {
+  $response["rptrecord"] = [];
+  $sql = $mysqli->prepare("SELECT * FROM rptrecord where serial = ?");
+  $sql->bind_param("s", $_GET["serial"]);
+  $sql->execute();
+  $query = $sql->get_result();
+  if ($mysqli->error) { error_die($mysqli->error, $mysqli->errno); }
+  while($row = $query->fetch_assoc()) {
+    if ($row['ip']) {
+      $ip = long2ip($row['ip']);
+      $host = gethostbyaddr($ip);
+    } elseif ( $row['ip6'] ) {
+      $ip = inet_ntop($row['ip6']);
+      $host = gethostbyaddr($ip);
+    } else {
+      $ip = "-";
+      $host = "-";
+    }
+    $row['ip'] = $ip;
+    $row['host'] = $host;
+    unset($row['ip6']);
+    $response["rptrecord"][] = $row;
+  }
+}
+
+echo json_encode($response, JSON_PRETTY_PRINT);
+?>
diff --git a/modules/private/websites/tools/tools/dmarc_reports/app.js b/modules/private/websites/tools/tools/dmarc_reports/app.js
new file mode 100644
index 0000000..7fe67d0
--- /dev/null
+++ b/modules/private/websites/tools/tools/dmarc_reports/app.js
@@ -0,0 +1,86 @@
+const app = new Vue({
+  el: '#app',
+  data: {
+    info: null,
+    summaries: [],
+    selectedSummary: null,
+    filterGreen: true,
+    filterDomain: null,
+    filterOrg: null,
+    //filterDate: (new Date()).toISOString().substring(0, 7),
+    filterDate: null,
+    reverse: true,
+  },
+  created: async function () {
+    let that = this;
+
+    try {
+      this.info = await this.getInfo();
+      this.summaries = this.info.summaries;
+    } catch (error) {}
+  },
+  methods: {
+    filtered: function () {
+      let that = this;
+      let filtered = this.summaries.filter(function (summary) {
+        return (!that.filterGreen || that.getColor(summary) !== "lime")
+          && (!that.filterDomain || summary.domain === that.filterDomain)
+          && (!that.filterOrg || summary.org === that.filterOrg)
+          && (!that.filterDate || that.inDates(summary));
+      });
+      if (this.reverse) {
+        return filtered.reverse();
+      } else {
+        return filtered;
+      }
+    },
+    toggle: async function(summary) {
+      if (this.selectedSummary && this.selectedSummary.serial === summary.serial) {
+        this.selectedSummary = null;
+      } else {
+        if (!summary.details) {
+          summary.details = await this.getDetails(summary.serial);
+        }
+        this.selectedSummary = summary;
+      }
+    },
+    inDates: function(summary) {
+      if (!this.filterDate) { return true; }
+
+      let mindate = (new Date(summary.mindate)).toISOString().substring(0, 7);
+      let maxdate = (new Date(summary.maxdate)).toISOString().substring(0, 7);
+
+      return mindate === this.filterDate || maxdate === this.filterDate;
+    },
+    printDate: function (date) {
+      return (new Date(date)).toISOString();
+    },
+    getColor: function (element) {
+      if (element.dkimresult === "fail" && element.spfresult === "fail") {
+        return "red";
+      } else if (element.dkimresult === "fail" || element.spfresult === "fail") {
+        return "orange";
+      } else if (element.dkimresult === "pass" && element.spfresult === "pass") {
+        return "lime";
+      } else {
+        return "yellow";
+      }
+    },
+    getInfo: function (event) {
+      return fetch('api.php').then(function (response) {
+        if (response.status != 200) { return; }
+        return response.text().then(function (body) {
+          return JSON.parse(body);
+        });
+      });
+    },
+    getDetails: function (serial) {
+      return fetch(`api.php?serial=${serial}`).then(function (response) {
+        if (response.status != 200) { return; }
+        return response.text().then(function (body) {
+          return JSON.parse(body);
+        });
+      });
+    }
+  }
+});
diff --git a/modules/private/websites/tools/tools/dmarc_reports/default.css b/modules/private/websites/tools/tools/dmarc_reports/default.css
new file mode 100644
index 0000000..11608cc
--- /dev/null
+++ b/modules/private/websites/tools/tools/dmarc_reports/default.css
@@ -0,0 +1,126 @@
+h1 {
+  text-align: center;
+}
+
+table.reportlist {
+  margin: 2em auto 2em auto;
+  border-collapse: collapse;
+  clear: both;
+}
+
+table.reportlist td, table.reportlist th {
+  padding:3px;
+}
+
+table.reportlist thead {
+  border-top: 1px solid grey;
+  border-bottom: 1px solid grey;
+
+}
+table.reportlist tbody tr:first-child td {
+  padding-top: 10px;
+}
+table.reportlist tr.sum {
+  border-top: 1px solid grey;
+}
+table.reportlist tr.selected {
+  background-color: lightgrey;
+}
+.reportdesc {
+  font-weight: bold;
+  width: 90%;
+  margin-left: auto;
+  margin-right: auto;
+}
+
+tr.summaryrow {
+  cursor: pointer;
+}
+
+tr.summaryrow:hover, tr.summaryrow.selected {
+  background-color: lightgray;
+  border-left: 1px solid lightgray;
+}
+
+td.reportcell {
+  border-bottom: 1px solid lightgray;
+  border-left: 1px solid lightgray;
+  border-right: 1px solid lightgray;
+}
+
+table.reportdata {
+  margin: 0px auto 0px auto;
+  border-collapse: separate;
+  border-spacing: 2px;
+}
+
+table.reportdata tr th, table.reportdata tr td {
+  text-align: center;
+  padding: 3px;
+}
+
+table.reportdata tr.red {
+  background-color: #FF0000;
+}
+
+table.reportdata tr.orange {
+  background-color: #FFA500;
+}
+
+table.reportdata tr.lime {
+  background-color: #00FF00;
+}
+
+table.reportdata tr.yellow {
+  background-color: #FFFF00;
+}
+
+.optionblock {
+  background: lightgrey;
+  padding: 0.4em;
+  float: right;
+  margin: auto 2em 1em auto;
+  white-space: nowrap;
+}
+
+.optionlabel {
+  font-weight: bold;
+  float: left; clear: left; 
+  margin-right: 1em;
+}
+
+.options {
+  font-size: 70%;
+  text-align: right;
+  border: none;
+  width: 97%;
+  padding: 0.4em;
+}
+
+.center {
+  text-align:center;
+}
+
+.circle_lime:before {
+  content: ' \25CF';
+  font-size: 25px;
+  color: #00FF00;
+}
+
+.circle_red:before {
+  content: ' \25CF';
+  font-size: 25px;
+  color: #FF0000;
+}
+
+.circle_yellow:before {
+  content: ' \25CF';
+  font-size: 25px;
+  color: #FFFF00;
+}
+
+.circle_orange:before {
+  content: ' \25CF';
+  font-size: 25px;
+  color: #FFA500;
+}
diff --git a/modules/private/websites/tools/tools/dmarc_reports/index.html b/modules/private/websites/tools/tools/dmarc_reports/index.html
new file mode 100644
index 0000000..ded05e1
--- /dev/null
+++ b/modules/private/websites/tools/tools/dmarc_reports/index.html
@@ -0,0 +1,118 @@
+<!DOCTYPE html>
+<html>
+
+<head>
+  <meta charset="utf-8">
+  <meta name="viewport" content="width=device-width, initial-scale=1">
+  <meta name="robots" content="noindex">
+  <title>Dmarc reports</title>
+  <link rel="stylesheet" href="default.css">
+</head>
+
+<body>
+  <div id="app" style="width: 100%">
+    <div class="optionblock" v-if="info">
+      <div class='options'>
+        <span class='optionlabel'>Hide all-green lines:</span>
+        <label><input type="radio" :value="false" v-model="filterGreen"> no</label>
+        <label><input type="radio" :value="true" v-model="filterGreen"> yes</label>
+      </div>
+      <div class='options'>
+        <span class='optionlabel'>Sort order:</span>
+        <label><input type="radio" :value="false" v-model="reverse"> ascending</label>
+        <label><input type="radio" :value="true" v-model="reverse"> descending</label>
+      </div>
+      <div class='options'>
+        <span class='optionlabel'>Domain(s):</span>
+        <select v-model="filterDomain">
+          <option selected="selected" :value="null">[all]</option>
+          <option v-for="domain in info.domains" :value="domain">{{ domain }}</option>
+        </select>
+      </div>
+      <div class='options'>
+        <span class='optionlabel'>Organisation(s):</span>
+        <select v-model="filterOrg">
+          <option selected="selected" :value="null">[all]</option>
+          <option v-for="org in info.orgs" :value="org">{{ org }}</option>
+        </select>
+      </div>
+      <div class='options'>
+        <span class='optionlabel'>Time:</span>
+        <select v-model="filterDate">
+          <option selected="selected" :value="null">[all]</option>
+          <option v-for="date in info.dates" :value="date">{{ date }}</option>
+        </select>
+      </div>
+    </div>
+
+    <h1 class='main'>DMARC Reports</h1>
+    <table class='reportlist' v-if="summaries">
+      <thead>
+        <tr>
+          <th></th>
+          <th>Start Date</th>
+          <th>End Date</th>
+          <th>Domain</th>
+          <th>Reporting Organization</th>
+          <th>Report ID</th>
+          <th>Messages</th>
+        </tr>
+      </thead>
+      <tbody>
+        <template v-for="summary in filtered()">
+          <tr v-on:click="toggle(summary)" class="summaryrow"
+              v-bind:class="[{ selected: selectedSummary && summary.serial === selectedSummary.serial }]">
+            <td class='right'><span :class="'circle_' + getColor(summary)"></span></td>
+            <td class='right'>{{ printDate(summary.mindate) }}</td>
+            <td class='right'>{{ printDate(summary.maxdate) }}</td>
+            <td class='center'>{{ summary.domain }}</td>
+            <td class='center'>{{ summary.org }}</td>
+            <td class='center'>{{ summary.reportid }}</td>
+            <td class='center'>{{ summary.rcount }}</td>
+          </tr>
+          <tr v-if="selectedSummary && summary.serial === selectedSummary.serial">
+            <td colspan="6" class="reportcell">
+              <div class='center reportdesc'>
+                <p>Policies: adkim={{ summary.policy_adkim }}, aspf={{ summary.policy_aspf }}, p={{ summary.policy_none }}, sp={{ summary.policy_sp }}, pct={{ summary.policy_pct }}</p>
+              </div>
+              <table v-if="summary.details" class='reportdata'>
+                <thead>
+                  <tr>
+                    <th>IP Address</th>
+                    <th>Host Name</th>
+                    <th>Message Count</th>
+                    <th>Disposition</th>
+                    <th>Reason</th>
+                    <th>DKIM Domain</th>
+                    <th>Raw DKIM Result</th>
+                    <th>SPF Domain</th>
+                    <th>Raw SPF Result</th>
+                  </tr>
+                </thead>
+                <tbody>
+                  <tr v-for="record in summary.details.rptrecord" :class='getColor(record)'>
+                    <td>{{ record.ip }}</td>
+                    <td>{{ record.host }}</td>
+                    <td>{{ record.rcount }}</td>
+                    <td>{{ record.disposition }}</td>
+                    <td>{{ record.reason }}</td>
+                    <td>{{ record.dkimdomain }}</td>
+                    <td>{{ record.dkimresult }}</td>
+                    <td>{{ record.spfdomain }}</td>
+                    <td>{{ record.spfresult }}</td>
+                  </tr>
+                </tbody>
+              </table>
+            </td>
+            <td></td>
+          </tr>
+        </template>
+      </tbody>
+    </table>
+  </div>
+
+  <script src="https://cdn.jsdelivr.net/npm/vue@2.6.11"></script>
+  <script src="app.js"></script>
+</body>
+
+</html>
diff --git a/modules/private/websites/tools/tools/landing/config.yml b/modules/private/websites/tools/tools/landing/config.yml
index 42ebcd1..f97b3f3 100644
--- a/modules/private/websites/tools/tools/landing/config.yml
+++ b/modules/private/websites/tools/tools/landing/config.yml
@@ -13,6 +13,9 @@ links:
     icon: "fas fa-desktop"
     url: "https://status.immae.eu"
     target: '_blank' # optionnal html a tag target attribute
+  - name: "DMARC status"
+    url: "https://tools.immae.eu/dmarc-reports"
+    target: '_blank'
   - name: "Change password"
     url: "https://tools.immae.eu/ldap_password.php"
     target: '_blank'
-- 
2.41.0