# Virtual Mailboxes Implementation Plan

## Project

**Project:** Vexib mail platform  
**Branch:** `virtual-mailboxes`  
**Target project folder:** `/home/phil/projects/vexib`  
**Documentation folder:** `documents/vmail_boxes/`  
**SQL folder:** `sql/`  

## Objective

Move the Vexib mail platform from Linux system-user mailboxes to MySQL-backed virtual mailboxes.

This will allow Vexib to host mail addresses for multiple hosted websites and subdomains without creating Linux users for each mailbox.

Examples:

- `info@vexib.co.uk`
- `admin@vexib.co.uk`
- `info@photosite.vexib.co.uk`
- `admin@photosite.vexib.co.uk`
- `noreply@photosite.vexib.co.uk`
- `info@albumdb.vexib.co.uk`
- `admin@albumdb.vexib.co.uk`
- `noreply@albumdb.vexib.co.uk`

## Current Mail Position

The current system uses:

- Ubuntu 24.04
- Postfix
- Dovecot
- SMTP2GO for outbound relay
- GoDaddy DNS for domain records
- `mail.vexib.co.uk` as the mail host

The existing SMTP2GO outbound relay should remain in place.

The major change is that Postfix and Dovecot will use MySQL to validate domains, mailboxes and aliases.

## Target Architecture

```text
Internet
   |
GoDaddy DNS
   |
mail.vexib.co.uk
   |
Postfix
   |
Dovecot LMTP / virtual delivery
   |
/var/vmail
   |
MySQL vexib_mail database
```

## Mail Storage Model

Virtual mailbox files will be stored under:

```text
/var/vmail
```

Suggested layout:

```text
/var/vmail/
    vexib.co.uk/
        info/
        admin/
    photosite.vexib.co.uk/
        info/
        admin/
        noreply/
    albumdb.vexib.co.uk/
        info/
        admin/
        noreply/
```

The mailbox owner should be a dedicated non-login Linux user:

```text
user:  vmail
uid:   5000
group: vmail
gid:   5000
home:  /var/vmail
```

## Database

Database name:

```text
vexib_mail
```

Core tables:

- `mail_domains`
- `mail_users`
- `mail_aliases`
- `mail_audit_log`

The first SQL implementation file is:

```text
sql/0001_create_vexib_mail.sql
```

## Phase 1 — Database Foundation

Create the MySQL database and tables.

Expected actions:

1. Review `sql/0001_create_vexib_mail.sql`.
2. Run the SQL as a MySQL administrative user.
3. Confirm the database and tables exist.
4. Confirm seed domains and initial mailboxes have been created.

Suggested command:

```bash
mysql -u root -p < sql/0001_create_vexib_mail.sql
```

Verification:

```bash
mysql -u root -p -e "USE vexib_mail; SHOW TABLES;"
mysql -u root -p -e "USE vexib_mail; SELECT domain_name, active FROM mail_domains;"
mysql -u root -p -e "USE vexib_mail; SELECT email, active FROM mail_users;"
```

## Phase 2 — Mail Storage User and Directory

Create the virtual mail user and storage directory.

Commands:

```bash
sudo groupadd -g 5000 vmail
sudo useradd -g vmail -u 5000 vmail -d /var/vmail -m -s /usr/sbin/nologin
sudo mkdir -p /var/vmail
sudo chown -R vmail:vmail /var/vmail
sudo chmod 770 /var/vmail
```

Verification:

```bash
id vmail
ls -ld /var/vmail
```

## Phase 3 — Postfix MySQL Lookup Configuration

Postfix will need MySQL map files for:

- virtual mailbox domains
- virtual mailbox users
- virtual aliases

Likely files:

```text
/etc/postfix/mysql-virtual-mailbox-domains.cf
/etc/postfix/mysql-virtual-mailbox-users.cf
/etc/postfix/mysql-virtual-alias-maps.cf
```

Postfix will then be configured to use these maps.

Important: current SMTP2GO relay configuration should remain active for outbound mail.

Expected retained settings include:

```text
relayhost = [mail-eu.smtp2go.com]:2525
smtp_sasl_auth_enable = yes
smtp_tls_security_level = encrypt
```

## Phase 4 — Dovecot MySQL Authentication

Dovecot will authenticate virtual users against the `vexib_mail.mail_users` table.

Dovecot will also need to know where virtual mailbox files are stored:

```text
mail_location = maildir:/var/vmail/%d/%n/Maildir
```

Expected authentication query:

```sql
SELECT email AS user, password_hash AS password
FROM mail_users
WHERE email = '%u'
  AND active = 1;
```

The stored password hash should be compatible with Dovecot.

For this project, initial mailbox passwords should be generated using `doveadm pw`, not plain PHP password hashes.

Example:

```bash
doveadm pw -s SHA512-CRYPT
```

## Phase 5 — Initial Manual Testing

Start with one low-risk mailbox, for example:

```text
test@photosite.vexib.co.uk
```

Test sequence:

1. Confirm Postfix accepts the domain.
2. Confirm Postfix accepts the mailbox.
3. Send inbound test mail.
4. Confirm Maildir creation under `/var/vmail`.
5. Confirm Dovecot login with Thunderbird or command-line IMAP test.
6. Confirm outbound mail still sends through SMTP2GO.

## Phase 6 — DNS Review

For each mail-enabled domain or subdomain, review DNS.

Examples:

```text
vexib.co.uk
photosite.vexib.co.uk
albumdb.vexib.co.uk
```

Each may need:

- MX record pointing to `mail.vexib.co.uk`
- SPF record allowing SMTP2GO
- DKIM records supplied by SMTP2GO
- DMARC record

SMTP2GO may also need each sending domain/subdomain verified if mail will be sent from addresses such as:

```text
noreply@photosite.vexib.co.uk
noreply@albumdb.vexib.co.uk
```

## Phase 7 — Migration from Existing System Mailboxes

Only migrate real addresses after the test mailbox works.

Suggested migration order:

1. Create matching virtual domains.
2. Create matching virtual mailboxes.
3. Test new mailbox authentication.
4. Move or archive existing mail if required.
5. Switch delivery to virtual mailbox handling.
6. Confirm inbound and outbound mail.
7. Remove dependency on Linux system mail users only after testing.

## Phase 8 — Vexib Mail Admin Interface

Once infrastructure is stable, build a Vexib web administration layer.

Initial pages may include:

```text
vm_dashboard.php
vm_domains.php
vm_mailboxes.php
vm_aliases.php
vm_audit.php
```

Initial access should be Vexib administrator only.

Later delegation can allow application-specific administrators to manage only their own domain.

Example permissions:

```text
Vexib admin:
    Can manage all domains, mailboxes and aliases.

Photosite admin:
    Can manage only *@photosite.vexib.co.uk.

AlbumDB admin:
    Can manage only *@albumdb.vexib.co.uk.
```

## Rollback Strategy

Before editing Postfix or Dovecot configuration:

1. Back up `/etc/postfix`.
2. Back up `/etc/dovecot`.
3. Confirm current mail sending and receiving behaviour.
4. Make one change at a time.
5. Restart services only after configuration tests pass.

Suggested commands:

```bash
sudo cp -a /etc/postfix /etc/postfix.pre-vmail.$(date +%Y%m%d-%H%M%S)
sudo cp -a /etc/dovecot /etc/dovecot.pre-vmail.$(date +%Y%m%d-%H%M%S)

sudo postfix check
sudo dovecot -n
```

If rollback is required, restore the backed-up configuration directories and restart services.

## Important Notes

- Do not store plain-text mailbox passwords.
- Do not use PHP `password_hash()` for Dovecot mailbox passwords unless Dovecot is explicitly configured to support that format.
- Use `doveadm pw` for initial mailbox password hashes.
- Keep SMTP2GO as the outbound relay.
- Make DNS changes gradually.
- Test using a non-critical mailbox first.

## Immediate Next Steps

1. Commit this implementation plan and SQL file to the `virtual-mailboxes` branch.
2. Review the SQL file before running it.
3. Decide the first test mailbox password.
4. Generate Dovecot-compatible password hashes.
5. Replace placeholder password hashes in the seed data.
6. Run the SQL.
7. Move on to Postfix and Dovecot configuration files.