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 explained the database schema it on the page 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 the previous Wheezy tutorial and they will probably still work but only create weak password hashes. With a little experience in web development you should be able to develop your own management system. I have documented a few common SQL queries further down.

These web interfaces were specifically made for this Jessie guide:

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-screenshot1grsoft-vmm-screenshot2grsoft-vmm-screenshot3grsoft-vmm-screenshot4grsoft-vmm-screenshot5grsoft-vmm-screenshot6grsoft-vmm-screenshot7grsoft-vmm-screenshot8grsoft-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 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 SHA256-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 SHA256-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’,CONCAT(‘{SHA256-CRYPT}’, ENCRYPT (‘new password’, CONCAT(‘$5$’, SUBSTRING(SHA(RAND()), -16)))));
Change the password of a user UPDATE virtual_users SET password=CONCAT(‘{SHA256-CRYPT}’, ENCRYPT (‘new password’, CONCAT(‘$5$’, SUBSTRING(SHA(RAND()), -16)))) 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’;

43 thoughts on “Managing users, aliases and domains

  • 2015-11-21 at 18:38
    Permalink

    Thanks for the great updated guide for Jessie! 🙂
    I successfully deployed it, working great. Except… web interfaces. I use ISPmail Admin now, but I can only login as admin user. When I try to login as a normal user, it doesn’t work. I think because it doesn’t understand the updates password scheme in the database (with {PLAIN-MD5} or {SHA256-CRYPT} in front of the password).
    Any idea how to solve this? Or is there another interface which understands the new scheme? Thanks!

    Reply
    • 2015-11-21 at 20:18
      Permalink

      Hi Hans. Thanks for the feedback. My ancient ISPmailAdmin is so old… I’m not even sure it can be installed any more because it is based on a deprecated web framework. However I am already working on a new web interface called ISPadmin that is half done. It will handle the SHA-256 hashes and also be able to assign administrators for the domains so that you can delegate user handling. The weather is rainy outside anyway so perhaps I’ll get some more lines of code done this weekend.

      Reply
      • 2015-11-21 at 20:30
        Permalink

        That’s the best news of the day Christoph! 🙂 Thanks in advance and keep up the good work! I’ll do another rain dance tomorrow 😉 😉

        Reply
    • 2015-11-29 at 12:51
      Permalink

      p.s. I added a multilanguage-package to the download-page including translations in spanish, French, italian, russian and swedish. All those Translation have been done by former users of the VMM.

      Reply
    • 2015-11-29 at 13:10
      Permalink

      Peter! Wow! This is very welcome! Thank you very much, it works great (except it doesn’t run on PHP7, I had to go back to 5.6). 🙂

      Reply
  • 2016-01-25 at 22:44
    Permalink

    Hello! It’s possible to use the roundcube plugin “password” to permit the user to change the password?
    With the default config file i had no luck…

    Many thanks in advance

    Reply
    • 2016-01-25 at 22:47
      Permalink

      Already fixed, was just a typo 😛

      Thx!

      Reply
  • 2016-02-03 at 15:27
    Permalink

    Mal ganz dumm gefragt: wie sieht denn nun eine Zeile in der Tabelle virtual_users aus für john@example.org mit dem Passwort start123 ? Muss es so sein:

    1 1 john@example.org {SHA256-CRYPT}$5$EvfedKzPsSlPCj2T$.yQ7um2G0wQ83wR8TlQSfboy1yR8FjOdJcxNT5H3pv1
    oder so:
    1 1 john@example.org $5$EvfedKzPsSlPCj2T$.yQ7um2G0wQ83wR8TlQSfboy1yR8FjOdJcxNT5H3pv1
    oder so:
    1 1 john@example.org EvfedKzPsSlPCj2T$.yQ7um2G0wQ83wR8TlQSfboy1yR8FjOdJcxNT5H3pv1

    Ist ja toll das SHA256 eingesetzt wird, aber das Einfügen von Hand ist umständlich und funktioniert bei mir auch nicht wie angegeben (SQL beklagt Syntaxfehler). Mit dem GRSoft Manager kann ich zwar Mailadressen anlegen, aber über Roundcube oder Thunderbird kann man sich dann anschließend trotzdem nicht anmelden. Die Passwörter die GRSoft Manager erzeugt, gebinnen auch immer mit $5$rounds=5000$0dPp… ich weiss ja nicht ob das so sein soll, aber die von dovecot pw generierten beginnen anders, warum auch immer…

    Reply
    • 2016-03-17 at 10:04
      Permalink

      Der Zusatz {SHA256-CRYPT} bei den Passwörtern braucht es nicht und wenn du deine Passwörter über den Mailmanager anlegst bzw. editierst, funktioniert das auch mit Roundcube und Thunderbird bzw. anderen Mailclients.

      Reply
  • 2016-02-04 at 10:26
    Permalink

    Für die anderen Leser:

    1.) Die Fehler in der SQL-Syntax passieren, wenn man den Code aus der Website in die Konsole kopiert, dann werden scheinbar die falschen Anführungsstriche eingefügt. Richtig ist der einfache Anführungsstrich, der auf einem deutschen Tastaturlayout auf der Rautetaste # liegt

    2.) Aus einem anderen Tutorial geht hervor, dass wohl die 2. Version des Passworthashes richtig ist, man sollte also nur {SHA256-CRYPT} am Anfang weglassen, dann sollte es gehen.

    Reply
  • 2016-02-07 at 10:56
    Permalink

    Hi,

    May be i have miss it, but are there mail quota implemented, so that the diferent mailbox sizes can be defined?

    Thanks in advance

    Reply
    • 2016-03-02 at 14:49
      Permalink

      In Wheezy to get quota support in postfix you had to download and apply a patch and rebuild the deb files, think that still applies to the postfix that comes with Jessie…. I initially setup for quotas but keepign postfix both up to date and patched for quota support became painful, and I realized I wasn’t even really using quotas (mail for me and my family…) so I re-did my server w/o the quota support. If you want to add those quotas, check the “perfect server” docs on howtoforge.com – they have a mail set up *very* similar to this one for wheezy and I’m sure the process is pretty much the same (extra column in db, changed queries to match, apply patch to postfix)

      Reply
  • 2016-02-09 at 16:49
    Permalink

    Hello! I installed GRS Virtual Mail Manager and it works so far but when switching the language to German the special characters are not displayed correctly.

    Thx!

    Reply
    • 2016-03-17 at 09:44
      Permalink

      That depends on the Defaultcharset of your server …
      If you add “AddDefaultCharset ISO-8859-1” (Apache-Syntax) to your Server, it will be displayed correct.

      Reply
      • 2016-03-23 at 16:19
        Permalink

        Alternative:
        add ‘header(‘Content-Type: text/html; charset=ISO-8859-1′);’ to mgr_main.php that it looks like:

        <?php
        header('Content-Type: text/html; charset=ISO-8859-1');


        Reply
  • 2016-03-03 at 18:22
    Permalink

    Thanks, that’s a reeeally awesome guide!
    But there is a mistake in SQL queries Create a mail user.
    You would hash the password 2 times, because you’re using dovecot and MySQL for it.
    So please remove this sentence “Generate a password hash using dovecot pw -s SHA256-CRYPT first.”

    Reply
    • 2016-03-04 at 11:30
      Permalink

      Fixed it – thanks.

      Reply
  • 2016-03-24 at 00:26
    Permalink

    I am working on a Rails app to manage domains and users on my ISPmail server.

    Not quite ready for prime time, but I intend to make use of this shortly.

    I have not tested against an existing database yet. You are warned.

    See https://github.com/ringe/ispmail-on-rails

    Reply
  • 2016-03-28 at 13:05
    Permalink

    Everything appears to have worked right up to the point of creating virtual users. The SQL command above gives me the error.

    Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘@notmydomain.net’,CONCAT(‘{SHA256-CRYPT}’, ENCRYPT (‘notmypassword’ at line 2

    I am using MySQL workbench to run the command against the server. MySQL is version 5.5.44-0+deb8u1

    Reply
    • 2016-05-01 at 12:31
      Permalink

      Me too… For me it was a cut&paste issue with the quotes…

      I was using this character ‘ instead of ‘ and that caused the error message…

      Hope it helps…

      Raffaele

      Reply
      • 2016-07-28 at 12:56
        Permalink

        Yeah the blog software strikes again, this time fucking up the quotes instead of turning two dashes into an emdash. God how i hate blog software

        Reply
  • 2016-04-14 at 21:17
    Permalink

    GRSoft Virtual Mail Manager – oddly I’ve got all the other stuff in this guide yet I am stuck at installing this? Any pointers on how / where it goes and how I get this to install?

    Reply
    • 2016-05-23 at 22:12
      Permalink

      Hey Runar, nice work I was thinking to write something similar 🙂 but now you already started. I might jump in a contribute in the near future, I just forked. Thanks!

      Reply
    • 2016-05-23 at 22:12
      Permalink

      Hey Runar, nice work I was thinking to write something similar 🙂 but now you already started. I might jump in a contribute in the near future, I just forked. Thanks!

      Reply
  • 2016-05-04 at 00:56
    Permalink

    I have an issue where I added a second virtual domain (using GRSoft) and the emails being sent from that domain are failing spamassassin HK_RANDOM _ENVFROM an HK_RANDOM_FROM on the recipient’s end. It doesn’t look like postfix is setting the headers properly. It works fine on the other domain that I went through setup with. Thoughts?

    Reply
  • 2016-05-23 at 22:06
    Permalink

    Since I have not found any pointers to Mailbox size restrictions aka quotas! Are you planning to add a section on Quotas as in older Tutorials? Last one I read was the one on debian Lenny, so it has been a while? Is there anything new in jessie?

    Reply
    • 2016-09-26 at 10:31
      Permalink

      Are you sure? Last time I checked the database schema was so different that it didn’t work. Can you give directions for its configuration?

      Reply
      • 2016-09-26 at 11:23
        Permalink

        Sure, here you go:

        mysql-virtual-mailbox-domains.cf
        query = SELECT 1 FROM domain WHERE domain=’%s’

        mysql-virtual-mailbox-maps.cf
        query = SELECT 1 FROM mailbox WHERE username=’%s’

        mysql-virtual-alias-maps.cf
        query = SELECT goto FROM alias WHERE address=’%s’

        mysql-email2email.cf
        SELECT username FROM mailbox WHERE username=’%s’

        dovecot-sql.conf.ext
        user_query = SELECT ‘/var/vmail/%d/%n’ AS home, ‘maildir:/var/vmail/%d/%n/maildir’ AS mail, 5000 AS uid, 5000 AS gid, CONCAT(‘*:bytes=’, IF(mailbox.quota = 0, domain.maxquota*1024000, mailbox.quota)) as quota_rule FROM mailbox, domain WHERE username = ‘%u’ AND mailbox.active = ‘1’ AND domain.domain = ‘%d’ AND domain.active = ‘1’
        password_query = SELECT username as user, password, ‘/var/vmail/%d/%n’ AS userdb_home, ‘/var/vmail/%d/%n/maildir’ AS userdb_mail FROM mailbox WHERE username = ‘%u’ AND active = ‘1’

        /etc/dovecot/dovecot-dict-sql.conf.ext
        connect = host=localhost dbname=mailaccounts user=dovecot password=yourpasswordhere
        map {
        pattern = priv/quota/storage
        table = quota2
        username_field = username
        value_field = bytes
        }
        map {
        pattern = priv/quota/messages
        table = quota2
        username_field = username
        value_field = messages
        }

        add to /etc/dovecot/conf.d/90-quota.conf
        plugin {
        # Using SQL Tables to store current quota size
        quota = dict:domain:%d:proxy::sqlquota
        quota2 = dict:User Quota::proxy::sqlquota
        # Allow 10% more for Trash Folder
        #quota_rule2 = Trash:storage=+10%%
        }
        dict {
        sqlquota = mysql:/etc/dovecot/dovecot-dict-sql.conf.ext
        }

        That’s it. Hopefully, I didn’t forget anything. As you can see, I’m also using quota with dovecot, which is directly suported by postfixadmin, hence the additional configs for dovecot. Please be careful with manual quota recalculation as dovecot could possibly still have this bug: http://dovecot.org/list/dovecot/2013-February/088440.html

        For strong password encryption with postfixadmin/dovecot/roundcube see https://kaworu.ch/blog/2016/04/20/strong-crypt-scheme-with-dovecot-postfixadmin-and-roundcube/

        Additionally I’ve setup lucene/solr for fulltest indexing with dovecot, but that is another story…

        Reply
        • 2016-09-26 at 23:29
          Permalink

          Many thanks, Christoph, for the guide which I’ve worked through and set up an – almost – fully functional email server. Thanks also to Anton for posting the configuration for postfixadmin which I have also used. Rather than using the version available from sourceforge I installed the stable version from Debian’s main repository and can confirm that everything seems to work fine with this configuration although there’s one remaining glitch which I’m trying to remedy now which is that incoming mail is being rejected:
          When sending an email from a Gmail account, the email is returned with: “The error that the other server returned was: “550 5.1.1 : Recipient address rejected: User unknown in virtual mailbox table.” But that, I think, is nothing to do with postfixadmin.

          Reply
        • 2016-09-28 at 13:15
          Permalink

          Typo in mysql-email2email.cf, forgrot “query =”

          Reply
  • 2016-09-28 at 08:18
    Permalink

    Just to add to my previous comment about postfixadmin: Having re-checked all the configuration files, email is successfully sent using a postfixadmin accounte and now isn’t rejected by the server. I subsequently set up roundcube as described in the guide but the login fails. With the icedove, when setting up an account the configuration, apparently, works flawlessly finding both incoming imap.example.com,STARTTLS and outgoing smtp.example.com, STARTTLS but after checking the password returns “User or password invalid” and “Configuration could not be verified — is the username or password wrong? Lastly, I set up Roundcube exactly as detailed in the guide but am unable to login either; so something is awry somewhere. Oh well, back to delving into the logs and configuration files again. 🙂

    Reply
  • 2016-10-02 at 14:10
    Permalink

    Finally, having corrected several errors in the files, everything works fine and I’m able to login, send and receive emails with Roundcube — and Icedove — as set out in this guide.
    @Anton. I tried Postfixadmin before correcting the issues mentioned in my last post and it looks very good but I’ve decided not to use it for the time being as I’m not sure how to configure /etc/fstab for quota yet.
    Now I’m looking at the GRSoft Virtual Mail Manager as an alternative but I’m not too sure how to run the install.php from a browser so I’ll just manage the mailserver database directly till I’ve figured it out either option.

    Reply
  • 2016-10-08 at 09:09
    Permalink

    Thanks Anton for the reply. I was laboring under a misapprehension about quota which I was not familiar with at all.
    I’d just like to observe that, because the configuration you posted is not wrapped in code blocks, if cut and pasted as such, gives rise to SQL syntax errors on account of the normal quotation marks for code being substituted by unicode quotes used for blogging purposes but, having corrected that, everything seems to work fine except for one — I hope final — error after trying to login using Roundcube, namely:
    dovecot: auth-worker(27941): Warning: mysql: Query failed, retrying: Table ‘mailserver.mailbox’ doesn’t exist
    dovecot: auth-worker(27941): Error: sql(user@example.net,127.0.0.1): Password query failed: Table ‘mailserver.mailbox’ doesn’t exist
    dovecot: imap-login: Disconnected (auth failed, 1 attempts in 2 secs): user=, method=PLAIN, rip=127.0.0.1, lip=127.0.0.1, secured, session=

    Reply
  • 2016-10-25 at 18:33
    Permalink

    Thanks for a great tutorial!

    How are aliases handled in this scenario?:
    I have set up two full accounts: a@domain.com and b@domain.com

    I create, in addition to those above, 1 alias that forwards a@domain.com to b@domain.com. Basically I want to have a copy of the email sent to a in b’s mailbox.

    In my tests, it looks like as if an incoming email is then only forwarded to b@domain.com and does not show up in the a@domain.com inbox? Is that correct?

    Reply
      • 2016-10-25 at 20:09
        Permalink

        Hi, I looked at example 2 at the page. This is exactly the scenario. However, it did not work in my case. I got this warning in the log files:

        … “status=deferred (mail system configuration error) ”
        … “postfix/pipe[12345]: warning: pipe flag `D’ requires dovecot_destination_recipient_limit = 1 ”

        So I added “dovecot_destination_recipient_limit = 1” to Postfix’ main.cf. Afterwards it works! (Must admit I am running Cent OS 7).

        Reply
  • 2017-01-18 at 03:46
    Permalink

    I’ve asked a lot of questions and you’re guide is amazing. I’ve got a mail server up and running and everything seems to be happy. just wanted to say thank you very much for the amazing guide and for your help!

    Reply

Leave a Reply

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

By continuing to use the site, you agree to the use of cookies. more information

The cookie settings on this website are set to "allow cookies" to give you the best browsing experience possible. If you continue to use this website without changing your cookie settings or you click "Accept" below then you are consenting to this.

Close