Mail Relay Log Tracker

A Python-based tool for monitoring Postfix mail relay logs, tracking authentication results (SPF, DKIM, DMARC), and recording delivery outcomes to a MySQL database.

Features

• Real-time log monitoring with automatic file rotation detection
• Correlates multiple log entries by Postfix queue ID
• Tracks SPF, DKIM, and DMARC authentication results
• Records delivery success/failure with detailed rejection reasons
• Tracks NOQUEUE rejections - messages rejected before being queued (sender blocklists, policy violations)
• Handles multiple recipients per message
• Persistent state tracking (survives restarts)
• Configurable timeout for incomplete message sequences
• Database reconnection on connection loss

Requirements

• Python 3.6+
• MySQL/MariaDB database
• Python packages:
  • mysql-connector-python

Installation

1. Clone the repository:

   git clone <repository-url>
   cd mail_relay_tracker

2. Install Python dependencies:

   pip3 install mysql-connector-python

   Or use the provided requirements.txt:

   pip3 install -r requirements.txt

3. Create the database and table:

   mysql -u root -p < schema.sql

   Or manually create the database and import the schema:

   CREATE DATABASE mail_tracking;
   USE mail_tracking;
   SOURCE schema.sql;

4. Create a database user:

   CREATE USER 'mailtracker'@'localhost' IDENTIFIED BY 'secure_password';
   GRANT INSERT, SELECT, UPDATE ON mail_tracking.* TO 'mailtracker'@'localhost';
   FLUSH PRIVILEGES;

   For remote database access from MX servers:

   CREATE USER 'mailtracker'@'mx-server-ip' IDENTIFIED BY 'secure_password';
   GRANT INSERT, SELECT, UPDATE ON mail_tracking.* TO 'mailtracker'@'mx-server-ip';
   FLUSH PRIVILEGES;

5. Create configuration file:

   cp mail_relay_tracker.conf.template mail_relay_tracker.conf

   Edit mail_relay_tracker.conf with your database credentials and settings.

6. Create state directory:

   sudo mkdir -p /var/lib/mail_relay_tracker
   sudo chown <your-user>:<your-group> /var/lib/mail_relay_tracker

Configuration

Edit mail_relay_tracker.conf to customize:

[database]
host = your-database-server
port = 3306
database = mail_tracking
user = mailtracker
password = your_password

[logging]
log_file = /var/log/mail.log
queue_timeout = 30
cleanup_interval = 10

[state]
state_file = /var/lib/mail_relay_tracker/state.json

[system]
server_hostname = mx1.example.com
rotation_check_interval = 5

[filters]
# Recommended: Only track your domains to exclude bounce messages to spammers
track_domains = gmail.com,mydomain.net

Domain Filtering

The track_domains setting filters which messages are stored in the database by checking the recipient domains. This is highly recommended to exclude outbound bounce messages to third-party spammers.

How it works:

• Checks both orig_to (original recipient) and upstream_to (forwarded recipient)
• A message is tracked if either field matches one of your domains
• Handles local usernames (e.g., orig_to=user with [email protected])
• Automatically excludes SRS rewritten addresses (bounce messages back to spammers)

Examples:

• ✅ [email protected], [email protected] → TRACKED
• ✅ orig_to=user, [email protected] → TRACKED (local user)
• ❌ [email protected], [email protected] → NOT TRACKED
• ❌ [email protected] → NOT TRACKED (SRS bounce)

What is SRS? When your mail server generates bounce messages to external domains, it rewrites the sender address using Sender Rewriting Scheme (SRS). These addresses start with SRS0= and are automatically filtered out because they represent bounces being sent back to spammers, not legitimate mail to your users.

Leave blank to track all messages (not recommended as it includes bounce notifications to spammers).

Usage

Manual Execution

# Basic usage (WARNING level logging)
./mail_relay_tracker.py -c mail_relay_tracker.conf

# With INFO level logging
./mail_relay_tracker.py -c mail_relay_tracker.conf -v

# With DEBUG level logging
./mail_relay_tracker.py -c mail_relay_tracker.conf -vv

Running as a Service

A systemd service file is provided for running the tracker as a background service:

1. Copy the service file:

   sudo cp mail_relay_tracker.service /etc/systemd/system/

2. Edit the service file to set correct paths and user:

   sudo nano /etc/systemd/system/mail_relay_tracker.service

3. Enable and start the service:

   sudo systemctl daemon-reload
   sudo systemctl enable mail_relay_tracker
   sudo systemctl start mail_relay_tracker

4. Check status:

   sudo systemctl status mail_relay_tracker
   sudo journalctl -u mail_relay_tracker -f

Database Schema

The mail_relay_log table includes:

• Tracking fields: server_hostname, queue_id, timestamps
• Source information: client IP/hostname, message-id, size
• Sender information: envelope_from (SRS-wrapped), original_envelope_from (unwrapped original sender)
• Authentication: SPF (from policyd-spf), DKIM (signatures, selector, domain, algorithm), DMARC
• SpamAssassin: spam_status (Yes/No), spam_score, spam_required threshold, spam_tests triggered
• Delivery: original and forwarded recipients, relay host, status (sent/bounced/rejected), codes, messages
• Milter actions: hold/reject reasons

NOQUEUE Rejections: Messages rejected before being queued (e.g., sender blocklist, policy violations) are stored with relay_status='rejected' and a pseudo queue_id like NOQUEUE-20241117104529123456. These rejections are inserted immediately rather than being accumulated.

SRS Unwrapping: The script automatically detects and unwraps SRS (Sender Rewriting Scheme) addresses in the envelope_from field. If the address is SRS-wrapped, the unwrapped original sender is stored in original_envelope_from. If the address is NOT SRS-wrapped, original_envelope_from is populated with the same value as envelope_from. This means original_envelope_from is always populated and can be used directly in queries without needing to fall back to envelope_from.

SPF Correlation: When using policyd-spf, the script correlates SPF results with mail by matching client IP and timestamp, even though policyd-spf logs don't include Postfix queue IDs.

SpamAssassin Integration: When SpamAssassin is configured as a Postfix milter, the script extracts spam scores, status, threshold, and triggered tests for comprehensive spam analysis.

See schema.sql for complete table definition.

Testing

Test domain filtering logic:

python3 test_domain_filtering.py

This verifies that the filtering correctly handles:

• Full email addresses in tracked domains
• Local usernames (no @domain) forwarded to tracked domains
• Third-party addresses that should be excluded
• Subdomains of tracked domains

Test SRS unwrapping:

python3 test_srs_unwrapping.py

This verifies that SRS (Sender Rewriting Scheme) addresses are correctly unwrapped to reveal the original sender addresses, including:

• Standard SRS0 format addresses
• Complex addresses with special characters
• Addresses with multiple = signs in the local part
• Non-SRS addresses (should return None)

Test SpamAssassin parsing:

python3 test_spamassassin_parsing.py

This verifies that SpamAssassin log entries are correctly parsed to extract:

• Spam status (Yes/No)
• Spam score (negative, zero, or positive)
• Required threshold
• Tests that were triggered

Test policyd-spf SPF correlation:

python3 test_policyd_spf.py

This verifies that policyd-spf log entries are correctly parsed to extract:

• SPF result (Pass/Fail/Softfail/Neutral/etc.)
• Client IP (IPv4 and IPv6)
• Envelope-from address
• Demonstrates how SPF results are correlated with mail by IP and timestamp

How It Works

1. Log Monitoring: The script tails /var/log/mail.log in real-time
2. Pattern Matching: Parses various Postfix log entries using regex patterns
3. Correlation: Groups log entries by Postfix queue ID
4. NOQUEUE Handling: NOQUEUE rejections (blocked before queuing) are inserted immediately with status='rejected'
5. Timeout Processing: After 30 seconds (configurable) of inactivity for a queue ID, the accumulated data is written to the database
6. State Persistence: The current file position is saved to JSON every cleanup interval
7. Rotation Detection: Checks every 5 seconds if the log file has been rotated (different inode)
8. Initial Startup: On first run (no state file), processes from the beginning of the current log file to capture historical data

Querying the Data

With domain filtering enabled, your database will only contain messages relevant to your tracked domains, making all queries more meaningful and reducing storage requirements.

Example queries:

-- Messages rejected by Gmail
SELECT COUNT(*) FROM mail_relay_log 
WHERE relay_status = 'bounced' AND smtp_code = 550;

-- Success rate by original recipient
SELECT orig_to, 
       COUNT(*) as total,
       SUM(CASE WHEN relay_status = 'sent' THEN 1 ELSE 0 END) as successful,
       ROUND(100.0 * SUM(CASE WHEN relay_status = 'sent' THEN 1 ELSE 0 END) / COUNT(*), 2) as success_rate
FROM mail_relay_log
GROUP BY orig_to;

-- DMARC failures
SELECT client_ip, dmarc_domain, COUNT(*) as failures
FROM mail_relay_log
WHERE dmarc_result = 'fail'
GROUP BY client_ip, dmarc_domain
ORDER BY failures DESC;

-- Recent bounces with reasons
SELECT first_seen, client_ip, orig_to, smtp_code, relay_message
FROM mail_relay_log
WHERE relay_status = 'bounced'
ORDER BY first_seen DESC
LIMIT 20;

-- Check domain distribution (verify filtering is working)
SELECT 
    SUBSTRING_INDEX(upstream_to, '@', -1) as domain,
    COUNT(*) as message_count,
    SUM(CASE WHEN relay_status = 'sent' THEN 1 ELSE 0 END) as successful,
    SUM(CASE WHEN relay_status = 'bounced' THEN 1 ELSE 0 END) as bounced
FROM mail_relay_log
WHERE first_seen >= DATE_SUB(NOW(), INTERVAL 1 DAY)
GROUP BY domain
ORDER BY message_count DESC;

-- Top senders (always use original_envelope_from)
SELECT 
    SUBSTRING_INDEX(original_envelope_from, '@', -1) as sender_domain,
    COUNT(*) as messages,
    SUM(CASE WHEN relay_status = 'sent' THEN 1 ELSE 0 END) as delivered,
    SUM(CASE WHEN relay_status = 'bounced' THEN 1 ELSE 0 END) as bounced
FROM mail_relay_log
WHERE first_seen >= DATE_SUB(NOW(), INTERVAL 7 DAY)
GROUP BY sender_domain
ORDER BY messages DESC
LIMIT 20;

-- Check SRS unwrapping effectiveness
SELECT 
    COUNT(*) as total_records,
    SUM(CASE WHEN envelope_from LIKE 'SRS0=%' THEN 1 ELSE 0 END) as srs_wrapped,
    SUM(CASE WHEN original_envelope_from IS NOT NULL THEN 1 ELSE 0 END) as has_original,
    SUM(CASE WHEN original_envelope_from != envelope_from THEN 1 ELSE 0 END) as was_unwrapped,
    ROUND(100.0 * SUM(CASE WHEN original_envelope_from != envelope_from THEN 1 ELSE 0 END) / COUNT(*), 2) as unwrap_percentage
FROM mail_relay_log
WHERE first_seen >= DATE_SUB(NOW(), INTERVAL 1 DAY);

-- SpamAssassin statistics
SELECT 
    COUNT(*) as total_checked,
    SUM(CASE WHEN spam_status = 'Yes' THEN 1 ELSE 0 END) as marked_spam,
    SUM(CASE WHEN spam_status = 'No' THEN 1 ELSE 0 END) as not_spam,
    AVG(spam_score) as avg_score,
    MAX(spam_score) as max_score,
    MIN(spam_score) as min_score
FROM mail_relay_log
WHERE spam_status IS NOT NULL
  AND first_seen >= DATE_SUB(NOW(), INTERVAL 7 DAY);

-- Messages that passed spam filter but were still bounced
SELECT 
    client_ip,
    original_envelope_from as sender,
    spam_score,
    spam_tests,
    relay_message
FROM mail_relay_log
WHERE spam_status = 'No'
  AND relay_status = 'bounced'
  AND first_seen >= DATE_SUB(NOW(), INTERVAL 1 DAY)
ORDER BY spam_score DESC
LIMIT 20;

-- Most common SpamAssassin tests triggered
SELECT 
    spam_tests,
    COUNT(*) as count,
    AVG(spam_score) as avg_score,
    SUM(CASE WHEN spam_status = 'Yes' THEN 1 ELSE 0 END) as spam_count
FROM mail_relay_log
WHERE spam_tests IS NOT NULL
  AND first_seen >= DATE_SUB(NOW(), INTERVAL 7 DAY)
GROUP BY spam_tests
ORDER BY count DESC
LIMIT 20;

-- SPF results distribution
SELECT 
    spf_result,
    COUNT(*) as count,
    SUM(CASE WHEN relay_status = 'sent' THEN 1 ELSE 0 END) as delivered,
    SUM(CASE WHEN relay_status = 'bounced' THEN 1 ELSE 0 END) as bounced
FROM mail_relay_log
WHERE spf_result IS NOT NULL
  AND first_seen >= DATE_SUB(NOW(), INTERVAL 7 DAY)
GROUP BY spf_result
ORDER BY count DESC;

-- Messages that failed SPF but were delivered
SELECT 
    client_ip,
    original_envelope_from as sender,
    spf_result,
    dmarc_result,
    spam_score,
    relay_status
FROM mail_relay_log
WHERE spf_result IN ('Fail', 'Softfail')
  AND relay_status = 'sent'
  AND first_seen >= DATE_SUB(NOW(), INTERVAL 7 DAY)
ORDER BY first_seen DESC
LIMIT 20;

-- NOQUEUE rejections (messages blocked before being queued)
SELECT 
    first_seen,
    client_ip,
    client_hostname,
    original_envelope_from as sender,
    orig_to,
    smtp_code,
    relay_message
FROM mail_relay_log
WHERE relay_status = 'rejected'
  AND first_seen >= DATE_SUB(NOW(), INTERVAL 7 DAY)
ORDER BY first_seen DESC
LIMIT 50;

-- Top rejected senders (sender blocklist effectiveness)
SELECT 
    SUBSTRING_INDEX(original_envelope_from, '@', -1) as sender_domain,
    COUNT(*) as rejection_count,
    GROUP_CONCAT(DISTINCT relay_message SEPARATOR '; ') as rejection_reasons
FROM mail_relay_log
WHERE relay_status = 'rejected'
  AND first_seen >= DATE_SUB(NOW(), INTERVAL 7 DAY)
GROUP BY sender_domain
ORDER BY rejection_count DESC
LIMIT 20;

-- Delivery status breakdown (sent vs bounced vs rejected)
SELECT 
    relay_status,
    COUNT(*) as count,
    ROUND(100.0 * COUNT(*) / SUM(COUNT(*)) OVER (), 2) as percentage
FROM mail_relay_log
WHERE first_seen >= DATE_SUB(NOW(), INTERVAL 7 DAY)
GROUP BY relay_status
ORDER BY count DESC;

-- Top rejection reasons from sender blocklist
SELECT 
    relay_message,
    COUNT(*) as count,
    COUNT(DISTINCT client_ip) as unique_ips,
    MIN(first_seen) as first_seen,
    MAX(first_seen) as last_seen
FROM mail_relay_log
WHERE relay_status = 'rejected'
  AND first_seen >= DATE_SUB(NOW(), INTERVAL 7 DAY)
GROUP BY relay_message
ORDER BY count DESC
LIMIT 20;

Troubleshooting

Database connection issues:

• Verify MySQL is running: sudo systemctl status mysql
• Test connection: mysql -h HOST -u USER -p DATABASE
• Check firewall rules if using remote database

No data being inserted:

• Check log file permissions: ls -l /var/log/mail.log
• Verify script is running: ps aux | grep mail_relay_tracker
• Check script logs with -vv flag for detailed output
• Verify mail is flowing: tail -f /var/log/mail.log

Encoding errors:

• The script handles non-UTF-8 characters gracefully by replacing them
• If you see "codec can't decode" warnings in debug mode, this is normal for mail logs with international characters
• Invalid UTF-8 bytes are replaced with � (replacement character) and processing continues

State file errors:

• Ensure state directory exists and is writable
• Check disk space: df -h
• To process from beginning: delete state file rm /var/lib/mail_relay_tracker/state.json
• To skip historical processing on large log files (start from current position):

  echo '{"position": '$(stat -c%s /var/log/mail.log)', "inode": '$(stat -c%i /var/log/mail.log)'}' > /var/lib/mail_relay_tracker/state.json

Missing data fields:

• Some fields are optional (SPF may not be logged by all setups)
• Check your Postfix configuration for what's being logged
• milter_action only appears for milter-held messages

License

[Add your license here]

Contributing

[Add contribution guidelines here]