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/


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 / PHPMyAdmin

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’;

21 thoughts on “Managing users, aliases and domains

  • 2019-12-31 at 08:08

    http://ima.jungclaussen.com/ this GUI does not support bcrypt
    in the config file there’s :

    ** Pasword hashes
    ** Enable only *one* of the following
    // define(‘IMA_CFG_USE_SHA256_HASHES’, true);
    // define(‘IMA_CFG_USE_MD5_HASHES’, true);
    ** access control: uncomment the type you want to use.

  • 2019-12-31 at 09:08

    For https://www.ima.jungclaussen.com/ to accept dovecot blowfish password, you may change one method in the file IspMailAdminApp.inc.php like this :

    protected function makePwd_DbHash($sPwdPlain)
    return ‘{BLF-CRYPT}’.password_hash($sPwdPlain, PASSWORD_BCRYPT);

    • 2020-01-02 at 01:57

      Thankyou for this Gompali, I am hoping that this ispmailadmin GUI works with the Buster guide, except for viewing/changing quotas.

      • 2020-01-02 at 10:55

        The GUI is ok and makes the coffee 😉 thanks to its author.

        I’m working not on a GUI but on an API that may help manipulate the database with http requests with Symfony framework. I’ll link the source code when i’ll have time to write a doc.

    • 2020-01-14 at 03:35

      When I replace the original function MakePwd_DbHash with the BLF-CRYPT version from this comment, I get in the apache2 log file:

      PHP Parse error: syntax error, unexpected ‘{‘ , expecting ‘;’ in ispmailadmin/inc/IspMailAdminApp.inc.php on line 474

      where line 474 is the new return statement. Triple checking both my cut/paste and removal of the original function doesn’t help. Suggestions please!

      • 2020-01-14 at 10:00

        had the same issue was from having hidden characters when copy and pasting, just write it manually and it should work.

        • 2020-01-14 at 15:02

          Thanks Alison! ISPMailAdmin works perfectly now!

          • 2020-01-18 at 16:56

            I spoke to soon. If I use ISPMailAdmin to change a password without adapting to BLF-CRYPT, it correctly updates the password using {SHA256-CRYPT}. But if I make the {BLF-CRYPT} change, it fails entering a blank password.

  • 2020-01-08 at 05:28

    Hello, I am following the new ispmail guide, the thing is that instead of trying with ispmail for the creation of users and aliases, I tried the new thing that the guide brings, I did it grsoft, the thing is that it is already installed but I created it users never recognize my password, I try to log in to the roundcube with the new users that I create, but nothing, I also try it with mutt from console and nothing, and I know it’s not the server itself because I did some tests with ispmail and with him if they log in.
    i install this Alternative PHP7 – Version (latest version).

  • 2020-03-02 at 12:22

    If anybody is interested, I have modified Ole’s ISPmail Admin 0.9.6 to manage quotas:

    Simply uncomment `define(‘IMA_CFG_QUOTAS’, true);` and you should be good to go. I have also added a switch for the BCRYPT passwords (uncomment `define(‘IMA_CFG_USE_BCRYPT_HASHES’, true);`), default is SHA256 for backwards compatibility (I removed the two obsolete defines).

    • 2020-05-18 at 16:13

      Excellent, works like a charm. Thanks a lot!

    • 2020-03-08 at 23:05

      Unfortunately the data schemas differ completely. Both projects emerged roughly at the same time and we never synced. 🙁

    • 2020-03-14 at 21:11

      Yes, I have it working. AddOns like the vacation plugin take some extra work, but IIRC just to get it working, you only have to adjust the SQL queries.

  • 2020-04-17 at 20:12

    I’ve been trying Tomas’ ‘Buster’ version of IspmailAdmin, with the options of quotas and bcrypt as per the latest guide. Most actions I’ve tried work but:
    1) creating new accounts doesn’t. You get an error message which indicates a mismatch in columns.
    I’ve managed to fix the issue *just for DB with quotas defined* by replacing the section starting at line 461 in EmailAccounts.inc with one that only works, but only with quotas:

    “INSERT INTO virtual_users (domain_id, password, email, quota)
    VALUES ( “.strval($iIdDomain).”,

    but I’ve not been able to work out how to add back the ‘with and without quota’ switching (I’n not familiar with php syntax.

    2) I find that I can login as ‘mailadmin’ if defining the logon password to be the same as the database one but I can’t define a different user with the admin rights in config.inc.php although as this is defined separately I’d assume it ought to work.

    3) Also login to IspmailAdmin as a non-admin user that I’ve created doesn’t seem to work. And changing a users password doesn’t to work properly: if you change it then IMAPing in fails, whether with the old or new password. Putting it back to the old password then restores IMAP functionality.

    For (2) and (3) I suspect some confusion is happening between passwords and their bcrypt hashes.

    • 2020-04-17 at 23:24

      Following up on my login/imap problem with ISPMailAdmin:

      Users created by ISPMailAdmin *are* in MariaDB virtual user database, but unlike those created directly in MariaDB or ones created by Adminer they don’t seem to be in the virtual mailbox table –
      If I try to send an email to them (e.g using swaks) I get a rejection:
      550 5.1.1 : Recipient address rejected: User unknown in virtual mailbox table.
      In adminer they appear greyed out and don’t show s as mailable.

      Any suggestions?

  • 2020-04-21 at 23:00

    Hi jfoss. A Buster-only version would work for me. I attempted to access your version of IspMailAdmin but the link doesn’t work (not even a ping response).

    • 2020-05-14 at 20:46

      Hi Marjorie,
      I have fixed small typos in the code when creating users with quotas. It should work now (I did not commit the fix and realized this after I moved my mail server and cloned the repository; luckily I had the fix on other machine).

  • 2020-06-06 at 00:52

    The link “section that dealt with preparing the database” is to the stretch version of the page

  • 2020-06-06 at 07:51

    Would any of the web interfaces featured here work with PostgreSQL rather than MySQL? I’ve gone through their documentation and they both seem to rely on the latter

  • 2020-06-06 at 22:41

    I reinstalled my email server just few weeks ago with that guide, but used postgresSQL instead of MySQL. Worked pretty well. Roundcube is supporting postgresSQL natively


Leave a Reply

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