[2019-12-26]

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

9 thoughts on “Managing users, aliases and domains

  • 2019-12-31 at 08:08
    Permalink

    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.

    Reply
  • 2019-12-31 at 09:08
    Permalink

    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);
    }

    Reply
    • 2020-01-02 at 01:57
      Permalink

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

      Reply
      • 2020-01-02 at 10:55
        Permalink

        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.

        Reply
    • 2020-01-14 at 03:35
      Permalink

      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!

      Reply
      • 2020-01-14 at 10:00
        Permalink

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

        Reply
        • 2020-01-14 at 15:02
          Permalink

          Thanks Alison! ISPMailAdmin works perfectly now!

          Reply
          • 2020-01-18 at 16:56
            Permalink

            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
    Permalink

    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).

    Reply

Leave a Reply

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