Thumbnail image

🐄🚀 Mailcow + Clickhouse + Redash: Keep Track of Your Email Server!

Table of Contents

Intro

mailcow makes self-hosting emails super easy and convenient. But honestly, something’s missing for me. Once everything is set up, you quickly realize that you don’t really have an overview of your email server. How many emails are coming in? How many are going out? And even more important: is spam possibly being sent through my server? Without such insights, the whole thing feels a bit like flying blind.

Thankfully, there’s Open Source! With the already integrated Rspamd spam filter, we can push all kinds of statistics into a Clickhouse database and use Redash to create a sleek dashboard.

We integrate all of this directly into the mailcow stack using a docker-compose.override.yml 🚀

Deploy Redash and Clickhouse

  1. Navigate to the mailcow directory and create a 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. Next, extend the mailcow.conf file by adding the following variables at the end (Postgres password and secrets need to be generated manually):
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. Next, we make Redash accessible through the mailcow Nginx reverse proxy. To do this, create a redash.conf file in the Nginx config folder.
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. !!! IMPORTANT !!! After that, initialize the Redash database in PostgreSQL and restart your mailcow stack.
docker compose run --rm redash create_db
docker compose down && docker compose up -d

Configure Rspamd

To enable Rspamd to store statistics in Clickhouse, we need to create an appropriate clickhouse.conf.
For more information on configuring the module, visit: Rspamd Clickhouse Module Documentation
Note: I set limit = 1 this low only for testing purposes.

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
#}

After that, you need to restart the Rspamd container with docker compose restart rspamd-mailcow.

Create a Dashboard

We can now access Redash via HTTPS on port 8009 (https://mailcow.tld:8009).
First, create an admin account.
Once logged in as admin, follow these steps:

  1. Add a Data Source
    Go to https://mailcow.tld:8009/data_sources and add a new Data Source of type Clickhouse.
    Image alt
    Image alt

  2. Create a Query
    Now, create your first query at https://mailcow.tld:8009/queries/new, which will show us how many emails we have received on that day.

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

Next, add a visualization below. Click on + New Visualization and configure it as follows: Image alt

  1. Create a Dashboard
    Next, create a new dashboard and then click on Add Widget at the bottom right.
    You should now be able to see and select your query. Image alt
    Image alt

  2. Done 🥳
    Now, send some test emails back and forth, and after a while, you should be able to see how many emails we’ve received.

Example 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