# Explanation of Amavisd SQL database

Attention

Check out the lightweight on-premises email archiving software developed by iRedMail team: Spider Email Archiver.

## Summary

Amavisd has two settings to use its SQL tables:

- `@storage_sql_dsn` uses 4 sql tables (`msgs`, `msgrcpt`, `maddr`, `quarantine`), and it's used to store:

  - basic info of inbound and outbound message. e.g. mail subject, sender address, recipient address, timestamp, etc. Note: no mail body.
  - quarantined mails. Note: it stores full message of quarantined mail, including mail body.

- `@lookup_sql_dsn`: uses 4 sql tables (`mailaddr`, `policy`, `users`, `wblist`), and it's used to store:

  - per-account spam policy
  - per-account white/blacklists

  Note: iRedMail has addition sql table `outbound_wblist` to store white/blacklists for outbound emails, but it's used by iRedAPD (plugin `amavisd_wblist`), not by Amavisd.

## Details

### `@lookup_sql_dsn`

- Table `amavisd.mailaddr` stores email addresses NOT hosted on your server.

  Note: value of column `mailaddr.email` could be something like below:

  - `@.`: a catch-all address.
  - `@domain.com`: entire domain.
  - `@.domain.com`: entire domain and all its sub-domains.
  - `user@domain.com`: a single email address.
  - `user@*`: email addresses start with `user@`. Note: This is used by iRedAPD, not Amavisd.
  - `192.168.1.2`: a single IP address. Note: This is used by iRedAPD, not Amavisd.
  - `192.168.*.2`: wildcard IP address. Note: This is used by iRedAPD, not Amavisd.

  The addresses are used in several tables:

  - `amavisd.wblist`: used by Amavisd. If sender (of inbound message) is blacklisted, Amavisd will quarantine this email. But if you have iRedAPD plugin `amavisd_wblist` enabled, this smtp session will be rejected before queued by Postfix, so Amavisd doesn't know this rejected message at all.
  - `amavisd.outbound_wblist`. New in iRedMail-0.9.3, used by iRedAPD plugin `amavisd_wblist` for white/blacklisting for outbound message.

- `amavisd.users` stores mail addresses hosted on your server. NOTE: you don't need to sync all existing mail users in this table, just add mail users you want to define a per-account spam policy in this table.

  Value of column `users.email` uses same format as `amavisd.mailaddr` mentioned above.

- `amavisd.wblist` stores white/blacklists for inbound message. `wblist.sid` (sender id) refers to `mailaddr.id`, `wblist.rid` (recipient id) refers to `users.id`.

- `amavisd.outbound_wblist` stores white/blacklists for outbound message. `outbound_wblist.sid` (sender id) refers to `users.id`, `outbound_wblist.rid` (recipient id) refers to `mailaddr.id`.

  Note: this table is used by iRedAPD, not Amavisd.

- `amavisd.policy`: used to define per-recipient spam policy, and max message size limit.

### TODO: `@storage_sql_dsn`

- `maddr`
- `msgs`
- `msgrcpt`
- `quarantine`

Amavisd is configured to stores quarantined emails in SQL db, you can get raw email from table `quarantine`.

Structure of `quarantine`:

    MariaDB [amavisd]> desc quarantine;
    +---------------+------------------+------+-----+---------+-------+
    | Field         | Type             | Null | Key | Default | Extra |
    +---------------+------------------+------+-----+---------+-------+
    | partition_tag | int(11)          | NO   | PRI | 0       |       |
    | mail_id       | varbinary(16)    | NO   | PRI | NULL    |       |
    | chunk_ind     | int(10) unsigned | NO   | PRI | NULL    |       |
    | mail_text     | blob             | NO   |     | NULL    |       |
    +---------------+------------------+------+-----+---------+-------+

Large email will be splited to multiple sql records, ordered by `chunk_ind`, and `mail_text` is the mail message.

You can query it like below, then concat value of `mail_text` column one by one to get full email message with all headers and body:

    SELECT mail_text FROM quarantine WHERE mail_id='<mail-id>' ORDER BY chunk_ind ASC;

Since Amavisd will store basic info of every inbound/outbound email, the SQL database will grow bigger and bigger, iRedMail setups a daily cron job to clean up old records with script shipped in iRedAdmin (available in both iRedAdmin open source edition and iRedAdmin-Pro): `tools/cleanup_amavisd_db.py`.