Thumbnail image

🐄🚀 Mailcow + Clickhouse + Redash: Behalte Den Überblick Über Deinen E-Mail-Server!

Table of Contents

Intro

mailcow macht das Self-Hosting von E-Mails wirklich super einfach und bequem. Aber ganz ehrlich: Mir fehlt da was. Sobald man alles eingerichtet hat, merkt man schnell, dass man keine wirkliche Übersicht über seinen E-Mail Server hat. Wie viele Mails gehen rein? Wie viele raus? Und noch wichtiger: Wird vielleicht sogar Spam über meinen Server verschickt? Ohne solche Infos fühlt sich das Ganze ein bisschen wie Blindflug an.

Zum Glück gibt’s Open Source! Mit dem bereits integrierten Spamfilter Rspamd können wir alle möglichen Statistiken in eine Clickhouse Datenbank packen und mit Redash basteln wir uns dann ein schickes Dashboard.

Das ganze integrieren wir mittels einer docker-compose.override.yml direkt im mailcow Stack 🚀

Redash und Clickhouse bereitstellen

  1. Wir switchen in das mailcow Verzeichnis und erstellen uns eine docker-compose.override.yml
cd /opt/mailcow-dockerzied
nano docker-compose.override.yml

## docker-compose.override.yml

services:
  redash:
    image: redash/redash:latest
    restart: always
    command: server
    environment:
      REDASH_DATABASE_URL: "${REDASH_DATABASE_URL}"
      REDASH_REDIS_URL: "${REDASH_REDIS_URL}"
      REDASH_COOKIE_SECRET: "${REDASH_COOKIE_SECRET}"
      REDASH_SECRET_KEY: "${REDASH_SECRET_KEY}"
    depends_on:
      - postgres-redash
      - redis-redash
    volumes:
      - redash_redash_data:/app
    networks:
        mailcow-network:

  scheduler-redash:
    image: redash/redash:latest
    restart: always
    command: scheduler
    environment:
      REDASH_DATABASE_URL: "${REDASH_DATABASE_URL}"
      REDASH_REDIS_URL: "${REDASH_REDIS_URL}"
      REDASH_COOKIE_SECRET: "${REDASH_COOKIE_SECRET}"
      REDASH_SECRET_KEY: "${REDASH_SECRET_KEY}"
    depends_on:
      - redash
    networks:
        mailcow-network:

  scheduled-worker-redash:
    image: redash/redash:latest
    restart: always
    command: worker
    environment:
      REDASH_DATABASE_URL: "${REDASH_DATABASE_URL}"
      REDASH_REDIS_URL: "${REDASH_REDIS_URL}"
      REDASH_COOKIE_SECRET: "${REDASH_COOKIE_SECRET}"
      REDASH_SECRET_KEY: "${REDASH_SECRET_KEY}"
      QUEUES: "scheduled_queries,schemas"
      WORKERS_COUNT: 1
    depends_on:
      - redash
    networks:
        mailcow-network:

  adhoc-worker-redash:
    image: redash/redash:latest
    restart: always
    command: worker
    environment:
      REDASH_DATABASE_URL: "${REDASH_DATABASE_URL}"
      REDASH_REDIS_URL: "${REDASH_REDIS_URL}"
      REDASH_COOKIE_SECRET: "${REDASH_COOKIE_SECRET}"
      REDASH_SECRET_KEY: "${REDASH_SECRET_KEY}"
      QUEUES: "queries"
      WORKERS_COUNT: 2
    depends_on:
      - redash
    networks:
        mailcow-network:

  postgres-redash:
    image: postgres:12-alpine
    restart: always
    environment:
      POSTGRES_USER: "${POSTGRES_USER}"
      POSTGRES_PASSWORD: "${POSTGRES_PASSWORD}"
      POSTGRES_DB: "${POSTGRES_DB}"
    volumes:
      - postgres_redash_data:/var/lib/postgresql/data
    networks:
        mailcow-network:

  redis-redash:
    image: redis:7-alpine
    restart: always
    volumes:
      - redis_redash_data:/data/
    environment:
      - TZ=${TZ}
    sysctls:
      - net.core.somaxconn=4096
    networks:
        mailcow-network:

  clickhouse-redash:
    image: yandex/clickhouse-server:latest
    restart: always
    volumes:
      - clickhouse_redash_data:/var/lib/clickhouse
    networks:
        mailcow-network:

  nginx-mailcow:
    ports:
      - "${HTTPS_BIND:-}:${HTTPS_PORT:-443}:${HTTPS_PORT:-443}"
      - "${HTTP_BIND:-}:${HTTP_PORT:-80}:${HTTP_PORT:-80}"
      - "${HTTPS_REDASH_BIND:-}:${HTTPS_REDASH_PORT:-443}:${HTTPS_REDASH_PORT:-443}"
      - "${HTTP_REDASH_BIND:-}:${HTTP_REDASH_PORT:-80}:${HTTP_REDASH_PORT:-80}"

volumes:
  redash_redash_data:
  postgres_redash_data:
  redis_redash_data:
  clickhouse_redash_data:
  1. Nun erweitern wir die mailcow.conf und fügen folgende Variablen ans Ende an (Postgres Password und Secrets müssen selber generiert werden)
nano mailcow.conf

## mailcow.conf

# Clickhouse Redash
REDASH_DATABASE_URL=postgresql://redash:<POSTGRES_SQL_PASSWORD>@postgres-clickhouse/redash
REDASH_REDIS_URL=redis://redis-clickhouse:6379/0
REDASH_COOKIE_SECRET=<RANDOM_COOKIE_SECRET>
REDASH_SECRET_KEY=<RANDOM_KEY_SECRET>
HTTP_REDASH_BIND=
HTTPS_REDASH_BIND=
HTTP_REDASH_PORT=8008
HTTPS_REDASH_PORT=8009
# Clickhouse PostgreSQL
POSTGRES_USER=redash
POSTGRES_PASSWORD=<POSTGRES_SQL_PASSWORD>
POSTGRES_DB=redash
# Clickhouse Redis
REDIS_URL=redis://redis-clickhouse:6379/0
  1. Als nächsten stellen wir Redash über den mailcow Nginx Reverse Proxy zur Verfügung. Dazu müssen wir eine redash.conf im Nginx Config Ordner erstellen
nano data/conf/nginx/redash.conf

## data/conf/nginx/redash.conf

server {
  listen 8008;
  listen 8009 ssl;
  listen [::]:8008;
  listen [::]:8009 ssl;
  http2 on;

  ssl_certificate /etc/ssl/mail/cert.pem;
  ssl_certificate_key /etc/ssl/mail/key.pem;
  ssl_protocols TLSv1.2 TLSv1.3;
  ssl_prefer_server_ciphers on;
  ssl_ciphers ECDHE-ECDSA-AES256-GCM-SHA384:ECDHE-RSA-AES256-GCM-SHA384:ECDHE-ECDSA-AES128-GCM-SHA256:ECDHE-RSA-AES128-GCM-SHA256:ECDHE-ECDSA-CHACHA20-POLY1305:ECDHE-RSA-CHACHA20-POLY1305;
  ssl_ecdh_curve X25519:X448:secp384r1:secp256k1;
  ssl_session_cache shared:SSL:50m;
  ssl_session_timeout 1d;
  ssl_session_tickets off;

  include /etc/nginx/conf.d/server_name.active;

  location / {
    proxy_set_header Host $http_host;
    proxy_set_header X-Real-IP $remote_addr;
    proxy_set_header X-Forwarded-For $proxy_add_x_forwarded_for;
    proxy_set_header X-Forwarded-Proto $scheme;
    proxy_pass http://redash:5000;
    proxy_redirect off;
  }
}
  1. !!! WICHTIG !!! Danach initialisieren wir die Redash Datenbank in PostgresSQL und starten unseren mailcow Stack neu.
docker compose run --rm redash create_db
docker compose down && docker compose up -d

Rspamd konfigurieren

Damit Rspamd die Statistiken in Clickhouse speichert, müssen wir eine entsprechende clickhouse.conf erstellen.
Für mehr Informationen, wie man das Modul konfiguriert: https://rspamd.com/doc/modules/clickhouse.html
limit = 1 habe ich nur für Tests so niedrig gesetzt.

nano data/conf/rspamd/local.d/clickhouse.conf

## data/conf/rspamd/local.d/clickhouse.conf

# Push update when 1 (for testing!) records are collected (1000 if unset) (until 2.1, see limits section below for >2.1)
limit = 1;
# IP:port of Clickhouse server ("localhost:8123" if unset)
server = "clickhouse-redash:8123";  # clickhouse-redash is the containername
# Timeout to wait for response (5 seconds if unset)
timeout = 5;
# How many bits of sending IP to mask in logs for IPv4 (19 if unset)
ipmask = 19;
# How many bits of sending IP to mask in logs for IPv6 (48 if unset)
ipmask6 = 48;
# Record URL paths? (default false)
full_urls = false;
# This parameter points to a map of domain names
# If a message has a domain in this map in From: header and DKIM signature,
# record general metadata in a table named after the domain
#from_tables = "/etc/rspamd/clickhouse_from.map";
# store digest/hash of email (default false)
enable_digest = false;
# store Symbols.Names, Symbols.Scores, Symbols.Options, Groups.Names and Groups.Scores (default false)
enable_symbols = false;
# Subject related (from 1.9)
insert_subject = false; 
# Privacy is off
subject_privacy = false; 
# Default hash-algorithm to obfuscate subject
subject_privacy_alg = 'blake2';
# Prefix to show it's obfuscated
subject_privacy_prefix = 'obf';
# Cut the length of the hash
subject_privacy_length = 16;
# Store data for local scanes
allow_local = true;

# Other options
#database = 'default';
#use_https = false;
# Transport compression
#use_gzip = true;
# Basic auth
#user = null;
#password = null;
# Disable SSL verification
#no_ssl_verify = false,

# This section configures how long the data will be stored in ClickHouse
#retention {
#  # disabled by default
#  enable = true;
#  # drop | detach, please refer to ClickHouse docs for details
#  # https://clickhouse.com/docs/en/sql-reference/statements/alter/partition
#  method = "drop";
#  # how many month the data should be kept in ClickHouse
#  period_months = 3;
#  # how often run the cleanup process
#  run_every = 7d;
#}
# This section defines how often Rspamd will send data to Clickhouse (from 2.1)
#limits {
#  max_rows = 1000; # How many rows are allowed (0 for disable this)
#  max_memory = 50mb; # How many memory should be occupied before sending collection
#  max_interval = 60s; # Maximum collection interval
#}

Danach müssen wir den Rspamd Container mit docker compose restart rspamd-mailcow neustarten.

Dashboard erstellen

Wir können Redash jetzt unter dem Port 8009 via HTTPS erreichen (https://mailcow.tld:8009).
Dort erstellen wir uns als erstes ein Admin Account.
Sind wir als Admin eingeloggt, können wir wie folgt vorgehen:

  1. Data Source anlegen
    Wir legen unter https://mailcow.tld:8009/data_sources eine neue Data Source vom Typ Clickhouse an
    Image alt
    Image alt

  2. Query erstellen
    Jetzt erstellen wir unser erstes Query unter https://mailcow.tld:8009/queries/new, welches uns anzeigt, wie viele E-Mails wir an diesem Tag erhalten haben.

SELECT
    count() AS c
FROM {{database}}.rspamd
WHERE (Date = today()) AND AuthUser == '' AND From != '' AND MimeFrom != 'localhost'

Weiter unten fügen wir noch eine Visualisierung hinzu. Dazu auf + New Visualization klicken und wie folgt konfigurieren
Image alt

  1. Dashboard erstellen Als nächstes erstellen wir uns ein neues Dashboard und klicken danach unten recht auf Add Widget.
    Hier sollten wir nun user Query sehen und auswählen können.
    Image alt
    Image alt

  2. Fertig 🥳
    Jetzt können wir ein paar Tests E-Mails hin und her schicken und nach einiger Zeit sollten wir sehen, wie viele E-Mails wir erhalten haben.

Beispiel Queries

Recieved Emails Today

SELECT
    count() AS c
FROM {{database}}.rspamd
WHERE (Date = today()) AND AuthUser == '' AND From != '' AND MimeFrom != 'localhost'

Recieved Spam today

SELECT
    count() AS c
FROM {{database}}.rspamd
WHERE (Date = today()) AND AuthUser == '' AND ((Action = 'reject') OR (Action = 'add header'))

Sent Emails Today

SELECT
    count() AS c
FROM {{database}}.rspamd
WHERE (Date = today()) AND AuthUser != ''

Top Incoming Domains for Spam and Ham

SELECT
    From,
    count() AS c
FROM {{database}}.rspamd
WHERE (Date >= today() - INTERVAL {{last_days}} DAY)  AND ((Action = 'reject') OR (Action = 'add header')) AND AuthUser == '' AND From != '' AND MimeFrom != 'localhost'
GROUP BY From
ORDER BY c DESC
LIMIT {{limit}}

Top Incoming IP

SELECT
    IP,
    count() AS c
FROM {{database}}.rspamd
WHERE (Date >= today() - INTERVAL {{last_days}} DAY) AND AuthUser == '' AND From != '' AND MimeFrom != 'localhost'
GROUP BY IP
ORDER BY c DESC
LIMIT {{limit}}

Top Incoming Senders

SELECT
    concat(FromUser, '@', From) AS user,
    count() AS c
FROM {{database}}.rspamd
WHERE (Date >= today() - INTERVAL {{last_days}} DAY) AND AuthUser == '' AND From != '' AND MimeFrom != 'localhost'
GROUP BY user
ORDER BY c DESC
LIMIT {{limit}}

Top Incoming Sender Domains

SELECT
    From AS domain,
    count() AS c
FROM {{database}}.rspamd
WHERE (Date >= today() - INTERVAL {{last_days}} DAY) AND AuthUser == '' AND From != '' AND MimeFrom != 'localhost'
GROUP BY domain
ORDER BY c DESC
LIMIT {{limit}}

Top Outgoing Senders

SELECT
    AuthUser AS user,
    count() AS c
FROM {{database}}.rspamd
WHERE (Date >= today() - INTERVAL {{last_days}} DAY) AND AuthUser != ''
GROUP BY user
ORDER BY c DESC
LIMIT {{limit}}

Top Outgoing Sender Domains

SELECT
    From AS domain,
    count() AS c
FROM {{database}}.rspamd
WHERE (Date >= today() - INTERVAL {{last_days}} DAY) AND AuthUser != ''
GROUP BY domain
ORDER BY c DESC
LIMIT {{limit}}

Top Outgoing IP

SELECT
    IP,
    count() AS c
FROM {{database}}.rspamd
WHERE (Date >= today() - INTERVAL {{last_days}} DAY) AND AuthUser != ''
GROUP BY IP
ORDER BY c DESC
LIMIT {{limit}}

Top Outgoing Domains for Spam and Ham

SELECT
    From,
    count() AS c
FROM {{database}}.rspamd
WHERE (Date >= today() - INTERVAL {{last_days}} DAY) AND ((Action = 'reject') OR (Action = 'add header')) AND AuthUser != ''
GROUP BY From
ORDER BY c DESC
LIMIT {{limit}}
mailcowemaillinuxserver