Mailcow-dockerized: Guide - Bulk creation of mailboxes

Created on 30 Nov 2017 · 13Comments · Source: mailcow/mailcow-dockerized

Created today a little .php script (and guide) for a bulk creation of mailboxes.
Tested with the actual mailcow version and 111 mailboxes. Is a bulk insert function planned for mailcow? If not: Maybe this is something for the documentation 😊

This guide is created to show an easy way to bulk insert a lot e-mail user into your mailcow.
Tested with Mailcow Dockerized (30.11.2017).
Migration from files is not included. Please use the Sync Function or a tool like imapsync.
This script will keep every data on your mailcow server. It never heard something from the cloud. (Yes, it is dump 😉 )

Required:

SSH and Web access to the Mailcow
List of the users, passwords, email-address
Time: Half an hour or so
Basic Knowledge with Linux, nano, vi/vim
Domains configured!

Workflow

Connect via SSH to the Mailcow Server – Use the root user
Connect to the path of your mailcow-dockerized
2.1. cd /path/to/mailcow-dockerized
Get an Backup from the MySQL Database
3.1. https://mailcow.github.io/mailcow-dockerized-docs/u_e-backup_restore-mysql/

source mailcow.conf
DATE=$(date +"%Y%m%d_%H%M%S")
docker-compose exec -T mysql-mailcow mysqldump --default-character-set=utf8mb4 -u${DBUSER} -p${DBPASS} ${DBNAME} > backup_${DBNAME}_${DATE}.sql

Create a copy of the file (with a meaningful name) e.g.: bulk-import.sql
4.1. Use cp on Debian
Copy or create the file “mcb_create_sql_strings.php” in the path data/web
5.1. If create: Copy the content from the attached file
Copy or create the file “mcb_user_array.php” in the path data/web
6.1. If create: Copy the content from the attached file
6.2. Fill this file with the user array

$user_array = [
    ['Name', 'Surname', 'user', 'Password', 'E-mail', 'Domain'],
    ['Name2', 'Surname2', 'user2', 'Password2', 'E-mail2', 'Domain2'],
];

Open in your browser the website of your mailcow and navigate to the file “mcb_create_sql_strings.php” - https://mail.domain.tld/mcb_create_sql_strings.php
7.1. While loading it will create four SQL strings. It will hash, using the password hashing from mailcow, for every user, the password. It will keep your data on the mailcow server.
7.2. It will present you a website with the SQL strings
Open the copy of the sql backup (bulk-import.sql)
8.1. Locate the SQL strings for
alias (Around lines 71) | Search for: INSERT INTO alias VALUES
mailbox (Around lines 334) | Search for: INSERT INTO mailbox VALUES
quota2 (Around lines 359) | Search for: INSERT INTO quota2 VALUES
user_acl (Around lines 856) | Search for: INSERT INTO user_acl VALUES
8.2. Replace the SQL Strings with the strings from step 7
8.2.1. If you want to keep existing mailboxes, you have to modify the sql strings.
8.3. Save and leave the editor
If you want: Create a Snapshot from your server
Restore the sql backup (bulk-import.sql)
10.1. https://mailcow.github.io/mailcow-dockerized-docs/u_e-backup_restore-mysql/

source mailcow.conf
docker exec -i $(docker-compose ps -q mysql-mailcow) mysql -u${DBUSER} -p${DBPASS} ${DBNAME} < bulk-import.sql

10.2. Wait until it is done

Login into your mailcow - https://mail.domain.tld
11.1. Navigate to the mailboxes
11.2. Maybe you have to increase the quota of your domain

Now you can login with the user credentials!

mcb_user_array.php

<?php
/* ['Name', 'Surname', 'user', 'Password', 'E-mail', 'Domain'], */

$user_array = [
    ['Name', 'Surname', 'user', 'Password', 'E-mail', 'Domain'],
    ['Name2', 'Surname2', 'user2', 'Password2', 'E-mail2', 'Domain2'],
];
?>

mcb_create_sql_strings.php

<html>
    <head>
        <title>Mailcow Bulk SQL Generator for Mailboxes</title>
    </head>
    <body>
        <?php
            include 'inc/functions.inc.php';
            include 'mcb_user_array.php'; /* Contains an array with the user data */

            /* Mysql Strings */
            $mysql_mailbox = 'INSERT INTO `mailbox` VALUES ';
            $mysql_alias = 'INSERT INTO `alias` VALUES ';
            $mysql_quota2 = 'INSERT INTO `quota2` VALUES ';
            $mysql_user_acl = 'INSERT INTO `user_acl` VALUES ';

            $date = date("Y-m-d H:i:s");
            $dir_size = 512 * 1024 * 1024; /* 512 MB */
            $fullname = ''; /* Created automaticly */
            $password_hash = ''; /* Hashed password */
            $i = 0;

            $name = ''; /* From Array */
            $surname = ''; /* From Array */
            $user = ''; /* From Array */
            $password = ''; /* From Array */
            $email = ''; /* From Array */
            $domain = ''; /* From Array */

            $array_len = count($user_array);
            /* Read in Array */
            foreach($user_array as list($name, $surname, $user, $password, $email, $domain)) {
                $i++;
                $fullname = "$name $surname";
                $password_hash = hash_password($password);

                /* Insert information for mysql string */
                $mysql_mailbox .= "('$email','$password_hash','$fullname','$domain/$user/',$dir_size,'$user','$domain',0,0,'',0,0,'$date',NULL,1)";
                $mysql_alias .= "('$email','$email','$domain','$date',NULL,1)";
                $mysql_user_acl .= "('$email',1,1,1,1,1,1,1,1,1,1)";
                $mysql_quota2 .= "('$email',0,0)";

                if ($i == $array_len) {
                    $mysql_mailbox .=";";
                    $mysql_quota2 .=";";
                    $mysql_alias .=";";
                    $mysql_user_acl .=";";
                } else if ($i > 0) {
                    $mysql_mailbox .=",";
                    $mysql_alias .=",";
                    $mysql_quota2 .=",";
                    $mysql_user_acl .=",";
                }
            }

            /* Print result */
            echo "<h5>alias | Around Line 71</h5>$mysql_alias"; /* Around Line 71 */
            echo "<h5>Mailbox | Around Line 334</h5>$mysql_mailbox"; /* Around Line 334 */
            echo "<h5>quota2 | Around Line 359</h5>$mysql_quota2"; /* Around Line 359 */
            echo "<h5>user_acl | Around Line 856</h5>$mysql_user_acl"; /* Around Line 856 */
        ?>
    </body>
</html>

dunno

Source

Fvbor

👍1

Most helpful comment

Everything exits. I just haven’t enabled it yet. Stupid docs. :-)

andryyy on 25 Jan 2018

🎉2 😄2 👍2

All 13 comments

I believe $mysql_user_acl .= "('$email',1,1,1,1,1,1,1,1)"; must be $mysql_user_acl .= "('$email',1,1,1,1,1,1,1,1,1,1)";. (10 instead of 8 ones.)

mritzmann on 23 Jan 2018

👍1

You are right. Thank you!
In one of the lastest versions two new values were added.
Updated the row in the guide.

Fvbor on 24 Jan 2018

👍1

Another little thing: Chrome and Firefox don't show anything for me because </title> is missing.

mritzmann on 25 Jan 2018

Sounds like you should create a PR with this functionality.

Braintelligence on 25 Jan 2018

@Braintelligence I think a API (There's already something on https://mail.example.com/api/v1/add/mailbox, but we can not use it in in a script because thy ned a CSRF token) were nice. Or a form in mailcow for bulk adding.

mritzmann on 25 Jan 2018

Or a form in mailcow for bulk adding.

That's what I meant.

Braintelligence on 25 Jan 2018

👍1

Everything exits. I just haven’t enabled it yet. Stupid docs. :-)

andryyy on 25 Jan 2018

🎉2 😄2 👍2

@mritzmann Oh, this got lost, for some reason, while I create the little guide. But in a time x it is no longer needed.
I will fix the guide in the next seconds.

Fvbor on 25 Jan 2018

Everything exits. I just haven’t enabled it yet. Stupid docs. :-)

I look forward to it. Need the possibility in the next few days. :-)

mritzmann on 26 Jan 2018

I wanted to add users from bash so I added a function and a procedure to the mariadb database so i can add users with isql. Maybe someone will find this useful:

DELIMITER $$
CREATE DEFINER=`mailcow`@`%` FUNCTION `mailcow_password`(_password varchar(128),_salt char(16)) RETURNS varchar(128) CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci
BEGIN
    if _salt IS NULL then 
        set _salt=HEX(CONCAT(CHAR(RAND()*256),CHAR(RAND()*256),CHAR(RAND()*256),CHAR(RAND()*256),CHAR(RAND()*256),CHAR(RAND()*256),CHAR(RAND()*256),CHAR(RAND()*256)));
    elseif LENGTH(_salt)<>16 then 
        set _salt=HEX(CONCAT(CHAR(RAND()*256),CHAR(RAND()*256),CHAR(RAND()*256),CHAR(RAND()*256),CHAR(RAND()*256),CHAR(RAND()*256),CHAR(RAND()*256),CHAR(RAND()*256)));
    end if;
    --
    RETURN CONCAT('{SSHA256}',TO_BASE64(UNHEX(CONCAT(SHA2(CONCAT(_password,_salt),256),HEX(_salt)))));
END$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`mailcow`@`%` PROCEDURE `add_mailbox`(_user varchar(80), _domain varchar(80), _password varchar(80), _name varchar(80), _surname varchar(80),_quota_bytes integer)
BEGIN

    declare _email varchar(128);
    declare _full_name varchar(128);
    declare _maildir varchar(128);
    --
    set _email=CONCAT(TRIM(_user),'@',TRIM(_domain));
    set _full_name=CONCAT(TRIM(_name),' ',TRIM(_surName));
    set _maildir=CONCAT(TRIM(_domain),'/',TRIM(_user));
    -- mailbox
    INSERT INTO mailbox(username,password,name,maildir,local_part,domain,quota,attributes)
    VALUES(_email, mailcow_password(_password,null),_full_name,_maildir,_user,_domain,_quota_bytes,'{"force_pw_update": "0", "tls_enforce_in": "0", "tls_enforce_out": "0"}')
    ON DUPLICATE KEY UPDATE username = _email;
    -- alias
    INSERT INTO alias(address,goto,domain) 
    VALUES (_email,_email,_domain)
    ON DUPLICATE KEY UPDATE address = _email;
    -- quota2
    INSERT INTO quota2(username,bytes,messages)
    VALUES (_email,0,0)
    ON DUPLICATE KEY UPDATE username = _email;
    -- user_acl
    INSERT INTO user_acl(username) 
    VALUES (_email)
    ON DUPLICATE KEY UPDATE username = _email;
END$$
DELIMITER ;
DELIMITER $$

fawzibr on 19 Apr 2018

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions.