Managing users, aliases and domains

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/

ima_screenshot

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:

grsoft-vmm-screenshot1
grsoft-vmm-screenshot2
grsoft-vmm-screenshot3
grsoft-vmm-screenshot4
grsoft-vmm-screenshot5
grsoft-vmm-screenshot6
grsoft-vmm-screenshot7
grsoft-vmm-screenshot8
grsoft-vmm-screenshot9

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 domainInsert a new row into the virtual_domains table and set the “name” to the name of the new domain.
Delete a mail domainDelete 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 userFind 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 userFind 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 userFind 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 forwardingYou 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 forwarddingFind 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 domainINSERT INTO virtual_domains (name) VALUES (“example.org”);
Delete a mail domainDELETE FROM virtual_domains where name=’example.org’;
Create a mail userINSERT 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 userUPDATE virtual_users SET password='{BLF-CRYPT}$2y$05$.We…’ WHERE email=’email@address’;
Delete a mail userDELETE FROM virtual_users WHERE email=’john@example.org’;
Create a mail forwardingINSERT 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 forwardingDELETE FROM virtual_aliases WHERE source=’melissa@example.org’;

3 thoughts on “Managing users, aliases and domains”

  1. hardbidouille

    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!

  2. Sadly the link to GRSoft Virtual Mail Manager no longer works. Also the guide still mentions mentions Debian Jessie.

Leave a Reply

Your email address will not be published. Required fields are marked *

Scroll to Top