Dovecot + Postfix + MariaDB FUNC&PROC

[DJMaze]

Ik zou graag van jullie willen weten of ik met onderstaande oplossing in de problemen kom.
Met name de SQL result sets, etc. etc.
Want in tests werkt het, maar geen idee of dat nog zo is als er 100 domeinen op staan met druk verkeer.

Ik ben een CentOS 8 aan het inrichten met Dovecot 2.3.7 en Postfix 3.3.1

De structuur is als volgt:
LOCALUSER = maildir:/home/LOCALUSER/mail
USER@DOMAIN = maildir:/home/LOCALUSER/mail/DOMAIN/USER/

Door een MariaDB log aan te maken, kwam ik er achter dat Postfix veel sql queries aanroept met %s.
Door te testen met %u en %d bleek dat een heel stuk minder.

Daarnaast heb ik geëxperimenteerd met VIEW, PROCEDURE en FUNCTION om te zien wat het beste resultaat geeft.

Omdat de DB procedures en functions gebruik maken van de interne @@hostname kan je mailboxen gebruiken als:

• Postfix
  • Alias: [e-mail] => [e-mail]
  • Catch-all Alias: @example.com => LOCALUSER
  • LOCALUSER => LOCALUSER@hostname
• Dovecot
  • Login passdb: LOCALUSER
  • Login passdb: USER@DOMAIN
  • Lookup userdb: LOCALUSER
  • Lookup userdb: LOCALUSER@hostname
  • Lookup userdb: USER@DOMAIN

En toch op de server de hostname wijzigen (als nodig).

Postfix smtpd_sender_login_maps.cf

code:

query = SELECT logins FROM (SELECT get_mail_sender_logins('%u', '%d') AS logins) t WHERE t.logins IS NOT NULL

Postfix virtual_mailbox_domains.cf

code:

query = SELECT 1 FROM DUAL WHERE mailbox_domain_exists('%s')

Postfix virtual_mailbox_maps.cf

code:

query = SELECT 1 FROM DUAL WHERE mailbox_exists('%u','%d')

Postfix virtual_alias_maps.cf

code:

query = SELECT destination FROM (SELECT get_virtual_alias_destination('%u', '%d') AS destination) t WHERE t.destination IS NOT NULL

Dovecot-sql.conf.ext

code:

password_query = CALL dovecot_get_pass('%u','%n','%d');
user_query = CALL dovecot_get_user('%u','%n','%d')

MariaDB (dus met name dit gedeelte bij druk bezoek)

SQL:

DELIMITER $$

/* Postfix */
CREATE FUNCTION get_mail_virtual_alias_destination (user VARCHAR(254), domain VARCHAR(254)) RETURNS varchar(254) CHARSET utf8mb4 COLLATE utf8mb4_bin
BEGIN
    DECLARE destination VARCHAR(254) DEFAULT NULL;
    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET destination = NULL;
    IF 'localhost' = domain THEN
        RETURN destination;
    END IF;
    SET @address = CONCAT(user,'@',domain);
    IF domain = @@hostname THEN
        SELECT @address INTO destination FROM users WHERE user_name = user;
    ELSEIF mailbox_domain_exists(domain) THEN
        SELECT mvu_mail INTO destination FROM users_mail_virtual_users WHERE mvu_mail = @address;
        IF destination IS NULL THEN
            SELECT mva_destination INTO destination FROM users_mail_virtual_aliases WHERE mva_source IN (@address, CONCAT('@',domain)) ORDER BY mva_source DESC;
            IF 0 = INSTR(destination, '@') THEN
                SET destination = CONCAT(destination,'@',@@hostname);
            END IF;
        END IF;
    END IF;
    RETURN destination;
END$$

CREATE FUNCTION mailbox_domain_exists (domain VARCHAR(254)) RETURNS BOOLEAN
BEGIN
    DECLARE hasdomain BOOLEAN DEFAULT FALSE;
    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET hasdomain = FALSE;
    IF 'localhost' = domain THEN
        RETURN FALSE;
    END IF;
    IF domain = @@hostname THEN
        RETURN TRUE;
    END IF;
    SELECT TRUE INTO hasdomain FROM users_domains WHERE 1 = domain_mx AND domain_name = domain;
    RETURN hasdomain;
END$$

CREATE FUNCTION mailbox_exists (user varchar(254), domain varchar(254)) RETURNS BOOLEAN
BEGIN
    DECLARE mailbox BOOLEAN DEFAULT FALSE;
    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET mailbox = FALSE;
    IF domain = @@hostname THEN
        SELECT TRUE INTO mailbox FROM users WHERE user_name = user;
    ELSEIF mailbox_domain_exists(domain) THEN
        SELECT TRUE INTO mailbox FROM users_mail_virtual_users WHERE mvu_mail = CONCAT(user,'@',domain);
    END IF;
    RETURN mailbox;
END$$

CREATE FUNCTION get_mail_sender_logins (user varchar(254), domain varchar(254)) RETURNS varchar(254) CHARSET utf8mb4 COLLATE utf8mb4_bin
BEGIN
    DECLARE mailboxes VARCHAR(254) DEFAULT NULL;
    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET mailboxes = NULL;
    IF 'localhost' = domain THEN
        RETURN NULL;
    END IF;
    SELECT GROUP_CONCAT(address) INTO mailboxes FROM (
        SELECT mva_destination AS address FROM users_mail_virtual_aliases WHERE mva_source = CONCAT('@',domain) OR mva_source = CONCAT(user,'@',domain)
        UNION SELECT mvu_mail AS address FROM users_mail_virtual_users WHERE mvu_mail = CONCAT(user,'@',domain)
    ) senders;
    RETURN mailboxes;
END$$

/* dovecot */
CREATE PROCEDURE dovecot_get_pass (IN address varchar(254), IN user varchar(254), IN domain varchar(254))
BEGIN
    IF 0 = INSTR(address, '@') THEN
        SELECT address AS user, user_pass AS password, user_id AS userdb_uid, user_id AS userdb_gid, CONCAT('/home/', user_name, '/mail/') AS userdb_home
        FROM users WHERE user_name = address;
    ELSE
        SELECT mvu_mail AS user, mvu_pass AS password, user_id AS userdb_uid, user_id AS userdb_gid, CONCAT('/home/', user_name, '/mail/', domain_name, '/', mvu_name, '/') AS userdb_home
        FROM users_mail_virtual_users
        INNER JOIN users_domains USING (domain_id)
        INNER JOIN users USING (user_id)
        WHERE mvu_mail = address;
    END IF;
END$$

CREATE PROCEDURE dovecot_get_user (IN address varchar(254), IN user varchar(254), IN domain varchar(254))
BEGIN
    IF 0 = INSTR(address, '@') THEN
        SELECT address AS user, user_id AS uid, user_id AS gid, CONCAT('/home/', user_name, '/mail/') AS home
        FROM users WHERE user_name = address;
    ELSEIF domain = @@hostname THEN
        SELECT address AS user, user_id AS uid, user_id AS gid, CONCAT('/home/', user_name, '/mail/') AS home
        FROM users WHERE user_name = user;
    ELSE
        SELECT mvu_mail AS user, user_id AS uid, user_id AS gid, CONCAT('/home/', user_name, '/mail/', domain_name, '/', mvu_name, '/') AS home
        FROM users_mail_virtual_users
        INNER JOIN users_domains USING (domain_id)
        INNER JOIN users USING (user_id)
        WHERE mvu_mail = address
        UNION SELECT address AS user, user_id AS uid, user_id AS gid, CONCAT('/home/', user_name, '/mail/', domain, '/.INBOX.', user, '/') AS home
        FROM users_domains
        INNER JOIN users USING (user_id)
        WHERE 1 = domain_mx AND domain_name = domain;
    END IF;
END$$

[ Voor 14% gewijzigd door DJMaze op 16-11-2019 19:14 ]

[Kalentum]

DJMaze schreef op donderdag 31 oktober 2019 @ 17:16:
Ik zou graag van jullie willen weten of ik met onderstaande oplossing in de problemen kom.
Met name de SQL result sets, etc. etc.
Want in tests werkt het, maar geen idee of dat nog zo is als er 100 domeinen op staan met druk verkeer.

Geen glazen bol hier helaas. Wat ik zou doen: een testdatabase vullen met realistische data qua aantallen. En dan kijken hoe snel die queries zijn dmv een setje scripts. 100.000 x runnen, kijken wat de performance is.

[DJMaze]

@Kalentum ga ik zeker doen!

Maar hoe gooi ik er 300 imap tls connecties tegelijkertijd tegenaan (dovecot).
En 50 smtp ontvangsten per seconde (postfix).

Want de aanvallen zijn er al 😉

Ik weet dat virtual files sneller is dan db.
Maar wil graag het hele management zonder root toegang eens uitproberen.
`postmap virtual | postfix reload` dan niet nodig.

[gekkie]

Als je weet welke queries die gaat doen, kun je toch ook gewoon een scriptje maken dat dat faked ?
En ik weet niet of je al een paar mooie indexen op die data hebt zitten .. maar ik neem toch aan dat je mini user databaseje wel door mysql in het geheugen gehouden kan worden, zou dat toch een peulenschil moeten zijn.
En verder greylisting en fail2ban ... zodat aanvallen weinig belasting opleveren (ook belangrijk voor als je een spamfilter implementeert, vooral spamfilteren kost relatief veel brinta, dus alles wat er met greylisting en fail2ban al uit is scheelt enorm.
Greylisting houdt bij mij 90% van al het smtp verkeer tegen. Dat is dus 90% waar geen spamfilter naar hoeft te kijken.

[ Voor 37% gewijzigd door gekkie op 31-10-2019 19:56 ]

[Kalentum]

DJMaze schreef op donderdag 31 oktober 2019 @ 19:47:
@Kalentum ga ik zeker doen!

Maar hoe gooi ik er 300 imap tls connecties tegelijkertijd tegenaan (dovecot).
En 50 smtp ontvangsten per seconde (postfix).

Als je gaat zoeken naar 'stress test smtp' of 'stress test imap' ofzo dan vind je wel wat.

[DJMaze]

gekkie schreef op donderdag 31 oktober 2019 @ 19:53:
Als je weet welke queries die gaat doen, kun je toch ook gewoon een scriptje maken dat dat faked ?
En ik weet niet of je al een paar mooie indexen op die data hebt zitten .. maar ik neem toch aan dat je mini user databaseje wel door mysql in het geheugen gehouden kan worden, zou dat toch een peulenschil moeten zijn.

Kalentum schreef op donderdag 31 oktober 2019 @ 20:20:
Als je gaat zoeken naar 'stress test smtp' of 'stress test imap' ofzo dan vind je wel wat.

Ja de database heeft de juiste indexes, maar met 50 users en 1000 domeinnamen verkiest MySQL liever een WHERE in zo'n kleine database. Kan hem inderdaad gewoon helemaal in het geheugen plempen
Bedankt, ik ga er mee aan de slag!

gekkie schreef op donderdag 31 oktober 2019 @ 19:53:
En verder greylisting en fail2ban ... zodat aanvallen weinig belasting opleveren (ook belangrijk voor als je een spamfilter implementeert, vooral spamfilteren kost relatief veel brinta, dus alles wat er met greylisting en fail2ban al uit is scheelt enorm.
Greylisting houdt bij mij 90% van al het smtp verkeer tegen. Dat is dus 90% waar geen spamfilter naar hoeft te kijken.

fail2ban zit er al op.

postgrey nog niet beschikbaar voor CentOS 8. Wel een vage EL8 RPM ergens gevonden, maar natuurlijk niet te controleren of die 100% valide is.
Net als de Dovecot 2.3 die ik nu gebruik i.v.m. bcrypt en argon2i(d), die ook niet in EL8 zit.

Dus liever nog ff geen live data/wachtwoorden. Ik knal er wel een berg pseudo's in.

[DiedX]

Als je rspamd kan installeren, dan zit daar greylisting bij. En blacklisting heb je ook al neem ik aan? Zen.spamhaus.org is geweldig.

[DJMaze]

Ik ben al een heel stuk verder.
De load op de server viel mee omdat MariaDB bijna alles in de cache gooit.

Verder heb de bugs in de functions/procedures opgelost en uitgebreid met eentje voor de postfix smtpd_sender_login_maps.
Nu is er dus ook een controle of de ingelogde gebruiker wel mag e-mailen met afzender "[e-mail]".

Het werkt voor nu en ik ga wat mensen met echte e-mail er op zetten, kijken wat daar uit komt.
Kan altijd nog mysqltuner er op los laten.