Maybe you already know what you have to do to create mail domains and mail users. After all I tried to explain the database schema in the section that dealt with preparing the database. But if that wasn’t clear enough let me explain what you need to do to manage your mail accounts.
Web interfaces
If you don’t like using SQL queries to manage your mail server you may like to install a web-based management software. Several developers contributed web interfaces for earlier versions of this guide and they will probably still work. The only change in the database schema is the quota field in the virtual_users table. I assume that the tools will add support for it. However you should be able to develop your own management system or integrate the mail server into your own system easily. I have documented a few common SQL queries further down.
These web interfaces have been provided to you by other diligent hands:
ISPmail Admin
Homepage: http://ima.jungclaussen.com/
Demo: http://ima.jungclaussen.com/demo/
GRSoft Virtual Mail Manager
Peter Gutwein has updated his PHP-based web interface to create strong password hashes as recommended in this Jessie guide. It supports english, german, spanish, french, italian, russian and swedish.
Homepage -> http://www.grs-service.ch/pub/grs_mminstallation.html
Here are screenshots of the working application:
Managing the database directly
Common tasks / Adminer
This table explains what changes are required in the database for your everyday tasks. You can click your way through PHPMyAdmin following the instructions in this table:
| Create a mail domain | Insert a new row into the virtual_domains table and set the “name” to the name of the new domain. |
| Delete a mail domain | Delete the row from the virtual_domains table that has the right “name”. All aliases and users will automatically be deleted, too. However the mailboxes will stay on disk at /var/vmail/… and you need to delete them manually. |
| Create a mail user | Find out the “id” of the right domain from the virtual_domains table. The insert a new row into the virtual_users table. Set the domain_id to the value you just looked up in the virtual_domains table. Set the “email” field to the complete email address of the new user. Create a new password in a shell using the “dovecot pw -s BLF-CRYPT” command and insert the result into the “password” field. |
| Change the password of a user | Find the row in the virtual_users table by looking for the right “email” field. Create a new password in a shell using the “dovecot pw -s BLF-CRYPT” command and insert the result into the “password” field. |
| Delete a mail user | Find the row in the virtual_users table by looking for the right “email” field and delete it. The mailbox will stay on disk at /var/vmail/… and you need to delete it manually |
| Create a mail forwarding | You can forward emails from one (source) email to other addresses (destinations) – even outside of your mail server. Find out the “id” of the right domain (the part after the “@” of the source email address) from the virtual_domains table. Create a new row in the virtual_aliases table for each destination (if you have multiple destination addresses). Set the “source” field to the complete source email address. And set the “destination” field to the respective complete destination email address. |
| Delete a mail forwardding | Find all rows in the virtual_aliases table by looking for the right “source” email address. Remove all rows that you lead to “destination” addresses you don’t want to forward email to. |
SQL queries
| Create a mail domain | INSERT INTO virtual_domains (name) VALUES (“example.org”); |
| Delete a mail domain | DELETE FROM virtual_domains where name=’example.org’; |
| Create a mail user | INSERT INTO virtual_users (domain_id, email, password) VALUES (SELECT id FROM virtual_domains WHERE name=’example.org’), ‘john@example.org’,'{BLF-CRYPT}$2y$05$.We…’; |
| Change the password of a user | UPDATE virtual_users SET password='{BLF-CRYPT}$2y$05$.We…’ WHERE email=’email@address’; |
| Delete a mail user | DELETE FROM virtual_users WHERE email=’john@example.org’; |
| Create a mail forwarding | INSERT INTO virtual_aliases (domain_id, source, destination) VALUES ( (SELECT id FROM virtual_domains WHERE name=’example.org’), ‘melissa@example.org’, ‘juila@example.net’); |
| Delete a mail forwarding | DELETE FROM virtual_aliases WHERE source=’melissa@example.org’; |
Hello and thank you for this really qualitative documentation.
I think there might be an error in the query used to “Create a mail user”.
The query :
INSERT INTO virtual_users (domain_id, email, password) VALUES (SELECT id FROM virtual_domains WHERE name=’example.org’), ‘john@example.org’,'{BLF-CRYPT}$2y$05$.We…’;
Should be :
INSERT INTO virtual_users (domain_id, email, password) VALUES ((SELECT id FROM virtual_domains WHERE name = ‘example.org’), ‘john@example.org’, ‘{BLF-CRYPT}$2y$05$.We…’);
With the parenthesis before “(SELECT…” and at the end before the closing “;”.
Also the “’” character should be replaced by “‘”.
Thanks again!
Sadly the link to GRSoft Virtual Mail Manager no longer works. Also the guide still mentions mentions Debian Jessie.
I made a modified version of ISPmail Admin to work with PostgreSQL… just in case someone needs it.
https://github.com/SushiTee/ispmailadmin-postgresql
In order to have ISP Mail Admin 0.9.11 working fine (also dealing with non-zero quotas) on Debian 11, I had to install “php7.4-bcmath”.
Now it’s ok.