Postfix-Dovecot-MySQL: MySQL

-- create_postfix_dovecot_mysql.sql

-- the databae itself
CREATE DATABASE mailserver;

-- some users:
GRANT SELECT,INSERT,UPDATE,DELETE ON mailserver.* TO 'mailadmin'@'127.0.0.1' IDENTIFIED BY 'ChangeMe';
GRANT SELECT                      ON mailserver.* TO 'postfix'@'127.0.0.1'   IDENTIFIED BY 'ChangeMe';
GRANT SELECT                      ON mailserver.* TO 'dovecot'@'127.0.0.1'   IDENTIFIED BY 'ChangeMe';

-- the tables:

-- postconf virtual_mailbox_domains=mysql:/etc/postfix/private/mysql_virtual_mailbox_domains.cf
USE mailserver;
CREATE TABLE IF NOT EXISTS `virtual_domains` (
 `id` int(11) NOT NULL auto_increment,
 `name` varchar(50) NOT NULL,
 PRIMARY KEY (`id`),
 UNIQUE KEY `name` (`name`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- postconf virtual_mailbox_maps=mysql:/etc/postfix/private/mysql_virtual_mailbox_maps.cf
USE mailserver;
CREATE TABLE IF NOT EXISTS `virtual_users` (
 `id` int(11) NOT NULL auto_increment,
 `domain_id` int(11) NOT NULL,
 `email` varchar(100) NOT NULL,
 `password` varchar(150) NOT NULL,
 PRIMARY KEY (`id`),
 UNIQUE KEY `email` (`email`),
 FOREIGN KEY (domain_id) REFERENCES virtual_domains(id) ON DELETE CASCADE
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- postconf virtual_alias_maps=mysql:/etc/postfix/private/mysql_virtual_alias_maps.cf
USE mailserver;
CREATE TABLE IF NOT EXISTS `virtual_aliases` (
 `id` int(11) NOT NULL auto_increment,
 `domain_id` int(11) NOT NULL,
 `source` varchar(100) NOT NULL,
 `destination` varchar(100) NOT NULL,
 PRIMARY KEY (`id`),
 UNIQUE KEY source_destination (`source`,`destination`),
 FOREIGN KEY (domain_id) REFERENCES virtual_domains(id) ON DELETE CASCADE
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- testdata_postfix_dovecot_mysql.sql INSERT INTO `mailserver`.`virtual_domains` ( `id` , `name` ) VALUES ( '1', 'example.org' ); INSERT INTO `mailserver`.`virtual_users` ( `id` , `domain_id` , `password` , `email` ) VALUES ('1', '1', '{SHA256-CRYPT}$5$.QKHwADm5PF2M88Y$USF2LLgf.6GHtTOKaUU0JxuVUcXPlFygOTpe2yWSImB' , 'foo@example.org'); INSERT INTO `mailserver`.`virtual_aliases` (`id`,`domain_id`,`source`,`destination`) VALUES ('1', '1', 'bar@example.org', 'foo@example.org'); INSERT INTO `mailserver`.`virtual_aliases` (`id`,`domain_id`,`source`,`destination`) VALUES ('2', '1', 'bar@example.org', 'foo@example.net'); INSERT INTO `mailserver`.`virtual_aliases` (`id`,`domain_id`,`source`,`destination`) VALUES ('3', '1', '@example.org', 'baz@example.org');

Postfix-Dovecot-MySQL: MySQL

Pakete installieren

Datenbank anlegen

Testdaten einspielen

Links