Delete read emails from before Plesk n days

Category：Plesk

Pub.date：2018-05-21

Plesk 17.8.11 Even if you ask users to set their mail settings to "Do not leave messages on the server" or "Delete read messages after 5 days", the default setting of recent mailers is "Leave messages on the server". If you do not want to use IMAP, you may want to automatically delete old read messages from the server.

## Delete mails in /var/qmail/mailnames/ by conditional search?

I also found an article that says to simply rm the corresponding file, but it is temporarily inconsistent with the index file and does not show the correct numbers. I heard that the index is updated when a user logs in, but it's not very pleasant.

## Delete with doveadm

There was a standard tool for Dovecot, so we will use that.

Combining the information I found with the information on the official Dovecot website, the following is what I found.
```
(Show read messages older than 30 days.)
doveadm -Dv search mailbox inbox before 30d SEEN -u info@example.com
(Delete read messages from 30 days ago.)
doveadm -Dv expunge mailbox inbox before 30d SEEN -u info@example.com
```

## -A option is not available?

```
Error: Failed to iterate through some users
```
The -A option, which targets all mailboxes in the server, caused an error. 
The same error occurs when specifying wildcards.

It seems that it is necessary to specify the list that doveadm refers to. In this case, I wanted to have a log of what kind of deletion was done, so I decided to do it for each account instead of batch processing.

## First, create a list of accounts.

```sql
SELECT CONCAT(mail.mail_name,'@',domains.name) AS username
FROM mail
INNER JOIN domains ON mail.dom_id=domains.id
INNER JOIN DomainServices ON mail.dom_id=DomainServices.dom_id
AND DomainServices.type='mail'
AND DomainServices.status=0
WHERE mail.postbox='true'
ORDER BY domains.id;
```
Issue a SELECT statement to the Plesk database psa to output the list of accounts.

The mail and domains tables are easily related if you look into the database, but if you don't refer to the DomainServices table, domains that are in the process of stopping mail service will also be listed, and doveadm will throw an error that the user cannot be found.

See DomainServices.status to see if the mail service is stopped or not.
```
status value          value   binary desсription
STATUS_ACTIVE          0     00000000 active
STATUS_STATUS          1     00000001 suspended
STATUS_WITH_PARENT     2     00000010 suspended because of parent object
STATUS_BACKUP_RESTORE  4     00000100 suspended because of backup/restore
STATUS_ADMIN           16    00001000 suspended by administrator
STATUS_RESELLER        32    00010000 suspended by reseller
STATUS_CLIENT          64    00100000 suspended by client
STATUS_EXPIRED         256  100000000 suspended due to expiration
```

## User list output command

```
mysql -uadmin -p`cat /etc/psa/.psa.shadow` -Dpsa -N -B -e "SELECT CONCAT(mail.mail_name,'@',domains.name) FROM mail INNER JOIN domains ON mail.dom_id=domains.id INNER JOIN DomainServices ON mail.dom_id=DomainServices.dom_id AND DomainServices.type='mail' AND DomainServices.status=0 WHERE mail.postbox='true';"
```
In fact, we don't need the ORDER BY, so we can remove it and use 
-N to hide column names, 
-B to tab-delimited output (no border)

```
info@example.com
sales@example.com
info@example.co.jp
admin@example.co.jp
```
A list of accounts only will be output.

## Pass the output list to doveadm

Pass the list to doveadm with while do. 
Create a file with .sh extension and set permissions, and call it periodically with CRON.

```
#!/bin/bash

mysql -uadmin -p`cat /etc/psa/.psa.shadow` -Dpsa -N -B -e "SELECT CONCAT(mail.mail_name,'@',domains.name) FROM mail INNER JOIN domains ON mail.dom_id=domains.id INNER JOIN DomainServices ON mail.dom_id=DomainServices.dom_id AND DomainServices.type='mail' AND DomainServices.status=0 WHERE mail.postbox='true';" | while read line
do
	doveadm expunge mailbox inbox before 30d SEEN -u $line
done
```

In this state, nothing will be printed out when the program finishes successfully. If you want to include the list of deleted files or processed accounts, you can add them in the loop.

## I'll rewrite it a bit more neatly.

```
#!/bin/bash

sql=$(cat <<'EOT'
SELECT CONCAT(mail.mail_name,'@',domains.name)
 FROM mail
 INNER JOIN domains ON mail.dom_id=domains.id
 INNER JOIN DomainServices ON mail.dom_id=DomainServices.dom_id
 AND DomainServices.type='mail'
 AND DomainServices.status=0
 WHERE mail.postbox='true';
EOT
)
echo $sql | mysql -uadmin -p`cat /etc/psa/.psa.shadow` -Dpsa -N -B | while read line
do
	doveadm expunge mailbox inbox before 30d SEEN -u $line
done
```

## This topic is unofficial

This is not the method described in the official Plesk website, so we cannot guarantee it. However, we have not encountered any problems with this operation. The expected effect has also been confirmed.