Metadata-Version: 2.4
Name: mailman-stalwart-sqlite
Version: 0.1.0
Summary: Mailman3 plugin to sync list addresses to SQLite for Stalwart
Author: Andreas Schneider
License: GPL-3.0-or-later
Project-URL: Homepage, https://gitlab.com/cryptomilk/mailman-stalwart-sqlite
Project-URL: Repository, https://gitlab.com/cryptomilk/mailman-stalwart-sqlite
Project-URL: Issues, https://gitlab.com/cryptomilk/mailman-stalwart-sqlite/-/issues
Keywords: mailman,stalwart,sqlite,mailing-list,mta
Classifier: Development Status :: 3 - Alpha
Classifier: Intended Audience :: System Administrators
Classifier: License :: OSI Approved :: GNU General Public License v3 or later (GPLv3+)
Classifier: Programming Language :: Python :: 3
Classifier: Programming Language :: Python :: 3.11
Classifier: Programming Language :: Python :: 3.12
Classifier: Programming Language :: Python :: 3.13
Classifier: Programming Language :: Python :: 3.14
Classifier: Topic :: Communications :: Email :: Mailing List Servers
Requires-Python: >=3.11
Description-Content-Type: text/markdown
License-File: LICENSE
Requires-Dist: mailman>=3.3
Requires-Dist: public
Requires-Dist: zope.component
Requires-Dist: zope.interface
Dynamic: license-file

# mailman-stalwart-sqlite

A Mailman3 plugin that syncs mailing list addresses to a SQLite database for
Stalwart Mail Server recipient lookup.

## Overview

This plugin implements Mailman's `IMailTransportAgentLifecycle` interface to
automatically maintain a SQLite database of mailing list addresses. Stalwart
reads this database to validate recipients and route messages to Mailman's
LMTP server.

When lists are created or deleted in Mailman, the plugin updates the SQLite
database with all list addresses (posting address, -bounces, -confirm, -join,
-leave, -owner, -request, -subscribe, -unsubscribe).

## Installation

Install from source:

```bash
pip install .
```

## Configuration

### 1. Create Plugin Configuration

Create `/etc/mailman3/stalwart-sqlite.cfg`:

```ini
[stalwart]
db_path: /etc/stalwart/data/mailman-lists.db
```

### 2. Configure Mailman

Edit `/etc/mailman3/mailman.cfg`:

```ini
[mta]
incoming: mailman_stalwart_sqlite.mta.LMTP
outgoing: mailman.mta.deliver.deliver
configuration: /etc/mailman3/stalwart-sqlite.cfg
lmtp_host: localhost
lmtp_port: 8024
```

### 3. Create SQLite Database

```bash
sudo mkdir -p /etc/stalwart/data
sudo sqlite3 /etc/stalwart/data/mailman-lists.db <<'SQL'
CREATE TABLE IF NOT EXISTS mailman_lists (
    address TEXT PRIMARY KEY,
    list_id TEXT NOT NULL
);
SQL

# Set permissions (Stalwart reads, Mailman writes)
sudo chown mailman:stalwart /etc/stalwart/data/mailman-lists.db
sudo chmod 660 /etc/stalwart/data/mailman-lists.db
sudo chown root:mailman /etc/stalwart/data
sudo chmod 775 /etc/stalwart/data
```

### 4. Configure Stalwart

Add to `config.toml`:

```toml
[store."mailman-lists"]
type = "sqlite"
path = "/etc/stalwart/data/mailman-lists.db"

[lookup."mailman-lists"]
store = "mailman-lists"

[session.rcpt]
directory = [
  { if = "key_exists('mailman-lists', rcpt)", then = "'mailman-noop'" },
  { else = "'my-directory'" }
]
relay = "!is_empty(authenticated_as) || key_exists('mailman-lists', rcpt)"

[queue.strategy]
route = [
  { if = "key_exists('mailman-lists', rcpt)", then = "'mailman'" },
  { if = "is_local_domain('my-directory', rcpt_domain)", then = "'local'" },
  { else = "'default'" }
]

[queue.route.mailman]
type = "relay"
address = "localhost"
port = 8024
protocol = "lmtp"
```

> **Note:** The `directory` expression maps Mailman addresses to
> `'mailman-noop'`, a directory that does not exist. This causes Stalwart to
> skip the directory recipient check for those addresses and fall through to
> the `relay` expression, which accepts them via `key_exists()`. Non-Mailman
> addresses are checked against your real user directory as usual.

> **Security Note:** The `relay` expression in `session.rcpt` ensures that
> only authenticated users or known Mailman list addresses are accepted for
> relay. Without this check, your server could become an open relay. Always
> verify that unauthenticated senders can only deliver to local mailboxes
> and known Mailman list addresses.

### 5. Initialize and Restart

```bash
# Generate initial database from existing lists
sudo -u mailman mailman aliases

# Restart services
sudo systemctl restart mailman3
sudo systemctl restart stalwart
```

## Usage

The plugin automatically syncs addresses when lists are created or deleted.
No manual intervention is needed.

### Manual Regeneration

If the database gets out of sync:

```bash
sudo -u mailman mailman aliases
```

### Verify Database

```bash
sudo sqlite3 -header -column /etc/stalwart/data/mailman-lists.db \
  "SELECT * FROM mailman_lists ORDER BY list_id, address;"
```

## How It Works

```
List created/deleted in Mailman
    ↓
Plugin updates SQLite DB
    ↓
Stalwart checks key_exists('mailman-lists', rcpt)
    ↓
If found → route to LMTP (localhost:8024)
If not found → normal delivery
```

## Requirements

- Python >= 3.11
- Mailman >= 3.3
- Stalwart Mail Server
- SQLite3

## License

GPL-3.0-or-later
