diff options
Diffstat (limited to 'doc/wiki/Plugins.Expire.txt')
| -rw-r--r-- | doc/wiki/Plugins.Expire.txt | 354 |
1 files changed, 354 insertions, 0 deletions
diff --git a/doc/wiki/Plugins.Expire.txt b/doc/wiki/Plugins.Expire.txt new file mode 100644 index 0000000..12b9f08 --- /dev/null +++ b/doc/wiki/Plugins.Expire.txt @@ -0,0 +1,354 @@ +Expire plugin +============= + +(See also <autoexpunge> [MailboxSettings.txt] setting, which partially +obsoletes this plugin.) + +Typically this plugin is used to optimize expunging old mails from users' +mailboxes. For example: + +---%<------------------------------------------------------------------------- +doveadm expunge -A mailbox Trash savedbefore 30d +---%<------------------------------------------------------------------------- + +Note that: + + * *This command runs fine even without the expire plugin.* Then it just goes + through all users rather than those users who have something to actually + expunge. + * The plugin actually works for all doveadm mail commands, not just expunge. + So for testing you could use e.g.'doveadm search -A ...' to see which + messages it matches. + * The optimization is done only when -A parameter is used to go through all + users. If you use -u parameter to separately go through users, the plugin + does nothing. + * The optimization is done only when "savedbefore" is used in the search + query. It's possible to have further restrictions also, but the user + filtering is based only on the "savedbefore" timestamp. + * The optimization is done only when the given mailbox or mailboxes match the + list of expire mailboxes specified in 'plugin { expire* } ' settings. + +So the expire plugin's job is to reduce disk I/O when figuring out which users +have work to do. Unless you have thousands of users, you probably shouldn't +bother with this plugin. + +Details +------- + +When expire plugin is enabled, it keeps track of the "oldest mail's saved +timestamp" for the specified mailboxes. When doveadm command uses "savedbefore" +search key, the timestamps in the database can be used to figure out which +users have matching mails. + +Expire plugin tracks/optimizes only "savedbefore" search query, i.e. the date +when message was *saved or copied to the mailbox* (*NOT the time message was +originally received*) while expire plugin was loaded. If mailbox contained +existing messages before the plugin was loaded for the first time, they'll get +expunged eventually when the first message saved/copied after expire plugin was +enabled gets expunged. + +The save/copy date may not be exact if it's not cached in +'dovecot.index.cache': + + * mbox: The current lookup time is used and added to cache. + * maildir: File's ctime is used. + * dbox: Save/copy time is taken from the dbox file if it exists (it normally + should), fallbacking to file's ctime if not. + +You need to configure a list of mailboxes that are tracked. Mailbox patterns +can contain IMAP LIST command-compatible wildcards: + + * "*" works in a standard way: It matches any number of characters. + * "%" works by matching any number of characters, but it stops at the + hierarchy separator. Currently the separator is hardcoded to "/", so it + doesn't work correctly if you've configured separator to something else + (e.g. "." is the default for Maildir). + +Example configuration +--------------------- + +Make sure you enable the plugin globally, not just for specific protocols. + +---%<------------------------------------------------------------------------- +mail_plugins = $mail_plugins expire + +plugin { + expire = Trash + expire2 = Trash/* + expire3 = Spam + + # Enable caching of dict value in dovecot.index file. This significantly +reduces + # the number of dict lookups. It makes initial testing more confusing though, +so + # it's better to enable it only after you've verified that the expire plugin +is + # working as wanted. (v2.2.16+) + expire_cache = yes +} +---%<------------------------------------------------------------------------- + + * See <Dict.txt> for more information about how to use dict service, + especially about permission issues. + * You need to get 'doveadm -A' working using your userdb. See iterate settings + for <SQL> [AuthDatabase.SQL.txt] and <LDAP> [AuthDatabase.LDAP.txt]. + +MySQL Backend +------------- + +dovecot.conf: + +---%<------------------------------------------------------------------------- +plugin { + expire_dict = proxy::expire +} +dict { + expire = mysql:/etc/dovecot/dovecot-dict-expire.conf.ext +} +---%<------------------------------------------------------------------------- + +Create the table like this: + +---%<------------------------------------------------------------------------- +CREATE TABLE expires ( + username varchar(75) not null, + mailbox varchar(255) not null, + expire_stamp integer not null, + primary key (username, mailbox) +); +---%<------------------------------------------------------------------------- + +dovecot-dict-expire.conf.ext: + +---%<------------------------------------------------------------------------- +connect = host=localhost dbname=mails user=sqluser password=sqlpass + +map { + pattern = shared/expire/$user/$mailbox + table = expires + value_field = expire_stamp + + fields { + username = $user + mailbox = $mailbox + } +} +---%<------------------------------------------------------------------------- + +PostgreSQL Backend +------------------ + +Like MySQL configuration above, but you'll also need to create a trigger: + +---%<------------------------------------------------------------------------- +CREATE OR REPLACE FUNCTION merge_expires() RETURNS TRIGGER AS $$ +BEGIN + UPDATE expires SET expire_stamp = NEW.expire_stamp + WHERE username = NEW.username AND mailbox = NEW.mailbox; + IF FOUND THEN + RETURN NULL; + ELSE + RETURN NEW; + END IF; +END; +$$ LANGUAGE plpgsql; + +CREATE TRIGGER mergeexpires BEFORE INSERT ON expires + FOR EACH ROW EXECUTE PROCEDURE merge_expires(); +---%<------------------------------------------------------------------------- + +SQLite Backend +-------------- + +Like MySQL configuration above, but you'll also need to create a trigger: + +---%<------------------------------------------------------------------------- +CREATE TRIGGER mergeexpires BEFORE INSERT ON expires FOR EACH ROW +BEGIN + UPDATE expires SET expire_stamp=NEW.expire_stamp + WHERE username = NEW.username AND mailbox = NEW.mailbox; + SELECT raise(ignore) + WHERE (SELECT 1 FROM expires WHERE username = NEW.username AND +mailbox = NEW.mailbox) IS NOT NULL; +END; +---%<------------------------------------------------------------------------- + +Vpopmail (+MySQL) +----------------- + +Configure expire plugin like MySQL configuration. From here, there are two +cases: + +If you are using the mysql database for authentication directly, you only need +to make iterate work for <MySQL> [AuthDatabase.SQL.txt]. + +When using Vpopmail backend, doveadm -A won't work. To be able to use the +expire database, you can use a bash script like this: + +---%<------------------------------------------------------------------------- +#!/bin/bash + +# SQL connection data +HOST="localhost" +USER="dovecot" +PWD="yourpassword" +DBNAME="vpopmail" +TBLNAME="expires" +# expunge messages older than this (days) +TTL=30 + +expiredsql=`cat <<EOF + USE $DBNAME; + SELECT CONCAT(username, "~", mailbox) + FROM $TBLNAME + WHERE expire_stamp>0 AND expire_stamp<UNIX_TIMESTAMP()-86400*$TTL; +EOF` + +for row in `echo "$expiredsql" | mysql -h $HOST -u $USER -p$PWD -N`; do + username=`echo $row | cut -d"~" -f1` + mailbox=`echo $row | cut -d"~" -f2-` + + # Check if mailbox name is empty. Just in case. + if [ -n $mailbox ]; then + echo "Expunging: "$row + doveadm expunge -u $username mailbox "$mailbox" savedbefore $TTL"d" + fi +done +---%<------------------------------------------------------------------------- + +Example #1 timeline +------------------- + +FIXME: expire-tool no longer exists, update these examples. Let's say Trash is +configured to expire in 7 days and today is 2009-07-10. Initially the database +and the Trash mailbox is empty. + +User moves the first message to Trash. The expires table is updated: + +---%<------------------------------------------------------------------------- +mysql> select mailbox, from_unixtime(expire_stamp), username from expires; ++---------+-----------------------------+----------+ +| mailbox | from_unixtime(expire_stamp) | username | ++---------+-----------------------------+----------+ +| Trash | 2009-07-17 15:57:36 | tss | ++---------+-----------------------------+----------+ +---%<------------------------------------------------------------------------- + +The expire_stamp contains the date when expire-tool will look into that mailbox +and try to find messages to expunge. Until then it skips the mailbox. + +A day later user moves another message to Trash. The expire_stamp isn't +updated, because the second message's save date is newer than the first one's. +Checking Trash's contents via IMAP you can see something like: + +---%<------------------------------------------------------------------------- +1 fetch 1:* (internaldate x-savedate) +* 1 FETCH (INTERNALDATE "16-Dec-2008 09:52:38 -0500" X-SAVEDATE "10-Jul-2009 +15:57:36 -0400") +* 2 FETCH (INTERNALDATE "29-Jun-2003 23:20:09 -0400" X-SAVEDATE "11-Jul-2009 +16:03:11 -0400") +1 OK Fetch completed. +---%<------------------------------------------------------------------------- + +Note how the message's INTERNALDATE (received date) can be very old compared to +the save date. Now, running expire-tool --test: + +---%<------------------------------------------------------------------------- +Info: tss/Trash: stop, expire time in future: Fri Jul 17 15:57:36 2009 +---%<------------------------------------------------------------------------- + +So it does nothing, because the expire time is in future. Fast forward 6 more +days into future. Running expire-tool --test: + +---%<------------------------------------------------------------------------- +Info: tss/Trash: seq=1 uid=1: Expunge +Info: tss/Trash: timestamp 1247860656 (Fri Jul 17 15:57:36 2009) -> 1247947391 +(Sat Jul 18 16:03:11 2009) +---%<------------------------------------------------------------------------- + +The first message would be expunged and the second message's timestamp would +become the new expire_stamp in database. After running expire-tool without +--test, the database is updated: + +---%<------------------------------------------------------------------------- +mysql> select mailbox, from_unixtime(expire_stamp), username from expires; ++---------+-----------------------------+----------+ +| mailbox | from_unixtime(expire_stamp) | username | ++---------+-----------------------------+----------+ +| Trash | 2009-07-18 16:03:11 | tss | ++---------+-----------------------------+----------+ +---%<------------------------------------------------------------------------- + +Also you can see the first message has been expunged from Trash: + +---%<------------------------------------------------------------------------- +2 fetch 1:* (internaldate x-savedate) +* 1 FETCH (INTERNALDATE "29-Jun-2003 23:20:09 -0400" X-SAVEDATE "11-Jul-2009 +16:03:11 -0400") +2 OK Fetch completed. +---%<------------------------------------------------------------------------- + +Example #2 timeline +------------------- + +Again you have Trash configured for 7 days, but this time you have an existing +message there before expire plugin has been enabled. Initially the expire +database is empty. Today is 2009-07-20. + +---%<------------------------------------------------------------------------- +1 fetch 1:* (internaldate x-savedate) +* 1 FETCH (INTERNALDATE "29-Jun-2003 23:20:09 -0400" X-SAVEDATE "11-Jul-2009 +16:03:11 -0400") +1 OK Fetch completed. +---%<------------------------------------------------------------------------- + +If you run expire-tool, you'll notice that it does nothing for the mailbox. +There's nothing in expire database, so expire-tool doesn't even mention it when +running with --test. + +After user moves the first message to Trash, the database gets updated: + +---%<------------------------------------------------------------------------- +mysql> select mailbox, from_unixtime(expire_stamp), username from expires; ++---------+-----------------------------+----------+ +| mailbox | from_unixtime(expire_stamp) | username | ++---------+-----------------------------+----------+ +| Trash | 2009-07-27 16:32:11 | tss | ++---------+-----------------------------+----------+ +---%<------------------------------------------------------------------------- + +The messages in Trash are: + +---%<------------------------------------------------------------------------- +2 fetch 1:* (internaldate x-savedate) +* 1 FETCH (INTERNALDATE "29-Jun-2003 23:20:09 -0400" X-SAVEDATE "11-Jul-2009 +16:03:11 -0400") +* 2 FETCH (INTERNALDATE "16-Dec-2002 11:02:39 -0500" X-SAVEDATE "20-Jul-2009 +16:32:11 -0400") +2 OK Fetch completed. +---%<------------------------------------------------------------------------- + +So the first message should be expiring already, right? No. It doesn't because +the timestamp in database is still in future. expire-tool --test says: + +---%<------------------------------------------------------------------------- +Info: tss/Trash: stop, expire time in future: Mon Jul 27 16:32:11 2009 +---%<------------------------------------------------------------------------- + +OK, let's see what happens when we finally reach July 27th: + +---%<------------------------------------------------------------------------- +Info: tss/Trash: seq=1 uid=3: Expunge +Info: tss/Trash: seq=2 uid=4: Expunge +Info: tss/Trash: no messages left +---%<------------------------------------------------------------------------- + +They both got expunged! The expire database's timestamp simply tells +expire-tool when to start looking into messages in that mailbox. After that +expire-tool looks at the actual save dates and figures out which messages +exactly need to be expunged. + +After running expire-tool without --test you'll see that the Trash mailbox is +empty and the database row is deleted. + +(This file was created from the wiki on 2019-06-19 12:42) |