mail server - mysql - database, tabellen en data
Het heeft straks niet veel zin om postfix of courier te testen zonder gebruikers of domeinen. Vandaar dat we met mysql beginnen.
- installatie mysql
bert@mail:~$ sudo apt-get install mysql-server
Als we in ub14.04 het pakket mysql-server installeren, komt de client ook mee:Reading package lists... Done Building dependency tree Reading state information... Done The following extra packages will be installed: libaio1 libdbd-mysql-perl libdbi-perl libhtml-template-perl libmysqlclient18 libterm-readkey-perl mysql-client-5.5 mysql-client-core-5.5 mysql-common mysql-server-5.5 mysql-server-core-5.5 Suggested packages: libclone-perl libmldbm-perl libnet-daemon-perl libplrpc-perl libsql-statement-perl libipc-sharedcache-perl tinyca mailx The following NEW packages will be installed: libaio1 libdbd-mysql-perl libdbi-perl libhtml-template-perl libmysqlclient18 libterm-readkey-perl mysql-client-5.5 mysql-client-core-5.5 mysql-common mysql-server mysql-server-5.5 mysql-server-core-5.5 0 upgraded, 12 newly installed, 0 to remove and 0 not upgraded. Need to get 8987 kB of archives. After this operation, 97.1 MB of additional disk space will be used.Tijdens de installatie vraagt mysql-server achter een root paswoord. Vergeet dit paswoord niet; we hebben het zo dadelijk al nodig om de nodige database en tabellen aan te maken.
┌──────────┤ Configuring mysql-server-5.5 ├──────────────────────┐ │ While not mandatory, it is highly recommended │ │ that you set a password for the MySQL administrative "root" │ │ user. │ │ If this field is left blank, the password will not be changed. │ │ │ │ New password for the MySQL "root" user: │ │ │ │ *x*x*x*x*x*x*x*x* │ │ │ │ <Ok> │ └────────────────────────────────────────────────────────────────┘ - aanmaken database en tabellen
Het is makkelijker cut-&-paste te gebruiken als je exact dezelfde database, tabellen en data wil aanmaken.
We loggen in op mysql ...
bert@mail:~$ mysql -u root -p
Enter password:*x*x*x*x*x*x*x*x*Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 37 Server version: 5.5.52-0ubuntu0.14.04.1 (Ubuntu) Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.We creëren de database met als naam
maildb
mysql> create database maildb;
Query OK, 1 row affected (0.00 sec)
We creëren een gebruiker met als naammailmysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP ON maildb.* TO 'mail'@'localhost' IDENTIFIED by 'sdf12345'; Query OK, 0 rows affected (0.00 sec) mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP ON maildb.* TO 'mail'@'%' IDENTIFIED by 'sdf12345'; Query OK, 0 rows affected (0.00 sec)We loggen uit als user
rooten loggen daarna in met de net gecreëerde usermailom tabellen in de database aan te maken:
mysql> exit
Bye
bert@mail:~$ mysql -u mail -p
Enter password:sdf12345Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 38 Server version: 5.5.52-0ubuntu0.14.04.1 (Ubuntu) ...We geven aan met de database
maildbte willen werken:
mysql> use maildb;
Database changed
we maken eentabelmet als naamaliassesmysql> CREATE TABLE aliases ( pkid smallint(3) NOT NULL auto_increment, mail varchar(120) NOT NULL default '', destination varchar(120) NOT NULL default '', enabled tinyint(1) NOT NULL default '1', PRIMARY KEY (pkid), UNIQUE KEY mail (mail) ); Query OK, 0 rows affected (0.01 sec)we maken een
tabelmet als naamdomainsmysql> CREATE TABLE domains ( pkid smallint(6) NOT NULL auto_increment, domain varchar(120) NOT NULL default '', transport varchar(120) NOT NULL default 'virtual:', enabled tinyint(1) NOT NULL default '1', PRIMARY KEY (`pkid`) ) ; Query OK, 0 rows affected (0.01 sec)we maken een
tabelmet als naamusersmysql> CREATE TABLE `users` ( id varchar(128) NOT NULL default '', name varchar(128) NOT NULL default '', uid smallint(5) unsigned NOT NULL default '5000', gid smallint(5) unsigned NOT NULL default '5000', home varchar(255) NOT NULL default '/var/spool/mail/virtual', maildir varchar(255) NOT NULL default 'blah/', enabled tinyint(1) NOT NULL default '1', change_password tinyint(1) NOT NULL default '1', clear varchar(128) NOT NULL default 'ChangeMe', crypt varchar(128) NOT NULL default 'sdtrusfX0Jj66', quota varchar(255) NOT NULL default '', PRIMARY KEY (`id`), UNIQUE KEY `id` (`id`) ) ; Query OK, 0 rows affected (0.01 sec)vervolgens vragen we de structuur van de drie tabellen op om ze na te kijken ...
mysql> describe aliases; +-------------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------------+--------------+------+-----+---------+----------------+ | pkid | smallint(3) | NO | PRI | NULL | auto_increment | | mail | varchar(120) | NO | UNI | | | | destination | varchar(120) | NO | | | | | enabled | tinyint(1) | NO | | 1 | | +-------------+--------------+------+-----+---------+----------------+ 4 rows in set (0.00 sec) mysql> describe domains; +-----------+--------------+------+-----+----------+----------------+ | Field | Type | Null | Key | Default | Extra | +-----------+--------------+------+-----+----------+----------------+ | pkid | smallint(6) | NO | PRI | NULL | auto_increment | | domain | varchar(120) | NO | | | | | transport | varchar(120) | NO | | virtual: | | | enabled | tinyint(1) | NO | | 1 | | +-----------+--------------+------+-----+----------+----------------+ 4 rows in set (0.00 sec) mysql> describe users; +-----------------+----------------------+------+-----+----------------------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------------+----------------------+------+-----+----------------------+-------+ | id | varchar(128) | NO | PRI | | | | name | varchar(128) | NO | | | | | uid | smallint(5) unsigned | NO | | 5000 | | | gid | smallint(5) unsigned | NO | | 5000 | | | home | varchar(255) | NO | | /var/spool/mail/virtual | | | maildir | varchar(255) | NO | | blah/ | | | enabled | tinyint(1) | NO | | 1 | | | change_password | tinyint(1) | NO | | 1 | | | clear | varchar(128) | NO | | ChangeMe | | | crypt | varchar(128) | NO | | sdtrusfX0Jj66 | | | quota | varchar(255) | NO | | | | +-----------------+----------------------+------+-----+----------------------+-------+ 11 rows in set (0.00 sec)tenslotte verlaten we mysql ...
mysql> exit
Bye
- configuratie ````
Het is kan nuttig zijn de logs vanmysqlaan te zetten . Daarvoor editeren we het configuratie bestand:
bert@mail:~$ sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf
We moeten dan de volgende 2 regels enablen:# # Both location gets rotated by the cronjob. # Be aware that this log type is a performance killer. # As of 5.1 you can enable the log at runtime! general_log_file = /var/log/mysql/mysql.log general_log = 1 # # Error log - should be very few entries. #En als dit is gebeurd, moeten we
mysqlherstarten:
bert@mail:~$ sudo systemctl restart mysql.service
mysql stop/waiting
mysql start/running, process 5186
- default : domeinen, gebruikers en aliassen
We moeten nu nog gegevens in onzemaildbdatabase plaatsen, we beginnen met default data, voor elke configuratie verplicht en hetzelfde:
bert@mail:~$ mysql -u mail -p
Enter password:
mysql> use maildb;
Database changed
verplichte domeinen voor lokale e-mail:mysql> INSERT INTO domains (domain) VALUES ('localhost'), ('localhost.localdomain'); Query OK, 2 rows affected (0.00 sec) // Records: 2 Duplicates: 0 Warnings: 0default aliassen ...
mysql> INSERT INTO aliases (mail,destination) VALUES ('postmaster@localhost','root@localhost'), ('sysadmin@localhost','root@localhost'), ('webmaster@localhost','root@localhost'), ('abuse@localhost','root@localhost'), ('root@localhost','root@localhost'), ('@localhost','root@localhost'), ('@localhost.localdomain','@localhost'); Query OK, 7 rows affected (0.00 sec) Records: 7 Duplicates: 0 Warnings: 0de root user
mysql> INSERT INTO users (id,name,maildir,crypt) VALUES -> ('root@localhost','root','root/',encrypt('een-paswoord', CONCAT('$5$', MD5(RAND()))) ); Query OK, 1 row affected (0.01 sec)Nota http://flurdy.com/docs/postfix/#data_add: this uses the encrypt function with a random salt per user and prefixed with $5$ which instructs it to use SHA-256 encryption hashing. You can alternatively use the plain encrypt('apassword') function, however it is then unsalted and only considers the first 8 character of the password. This may be required if you use other software that needs to interact with the users authentication. However most will support the SHA-256 crypt() call.
- specifiek voor bert.intra : domeinen, gebruikers en aliassen
bert@mail:~$ mysql -u mail -p
Enter password:
mysql> use maildb;
Database changed
domein bert.intramysql> INSERT INTO domains (domain) VALUES ('bert.intra'); Query OK, 1 row affected (0.00 sec)
de server mail.bert.intramysql> insert into aliases (mail,destination) values ('@mail.bert.intra','bert.intra'); Query OK, 1 row affected (0.00 sec)
twee belangrijk alias-adressenmysql> insert into aliases (mail,destination) values ('postmaster@bert.intra','postmaster@localhost');Query OK, 1 row affected (0.00 sec) Query OK, 1 row affected (0.00 sec) mysql> insert into aliases (mail,destination) values ('abuse@bert.intra','abuse@localhost'); Query OK, 1 row affected (0.00 sec)drie gebruikers en hun mailbox directory
mysql> INSERT INTO users (id,name,maildir,crypt) VALUES -> ('bert@bert.intra','bert','bert/',encrypt('sdf12345', CONCAT('$5$', MD5(RAND()))) ); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO users (id,name,maildir,crypt) VALUES ('jeanne@bert.intra','jeanne','jeanne/',encrypt('sdf12345', CONCAT('$5$', MD5(RAND()))) ); Query OK, 1 row affected (0.01 sec) mysql> INSERT INTO users (id,name,maildir,crypt) VALUES ('tito@bert.intra','tito','tito/',encrypt('sdf12345', CONCAT('$5$', MD5(RAND()))) ); Query OK, 1 row affected (0.00 sec) - en wat hebben we nu ...
mysql> select * from domains; +------+-----------------------+-----------+---------+ | pkid | domain | transport | enabled | +------+-----------------------+-----------+---------+ | 1 | localhost | virtual: | 1 | | 2 | localhost.localdomain | virtual: | 1 | | 3 | bert.intra | virtual: | 1 | +------+-----------------------+-----------+---------+ 3 rows in set (0.00 sec)mysql> select * from aliases; +------+------------------------+----------------------+---------+ | pkid | mail | destination | enabled | +------+------------------------+----------------------+---------+ | 1 | postmaster@localhost | root@localhost | 1 | | 2 | sysadmin@localhost | root@localhost | 1 | | 3 | webmaster@localhost | root@localhost | 1 | | 4 | abuse@localhost | root@localhost | 1 | | 5 | root@localhost | root@localhost | 1 | | 6 | @localhost | root@localhost | 1 | | 7 | @localhost.localdomain | @localhost | 1 | | 8 | @mail.bert.intra | bert.intra | 1 | | 9 | postmaster@bert.intra | postmaster@localhost | 1 | | 10 | abuse@bert.intra | abuse@localhost | 1 | +------+------------------------+----------------------+---------+ 10 rows in set (0.00 sec)mysql> select * from users; +-------------------+--------+------+------+-------------------------+---------+---------+-----------------+----------+-----------------------------------------------------------------+-------+ | id | name | uid | gid | home | maildir | enabled | change_password | clear | crypt | quota | +-------------------+--------+------+------+-------------------------+---------+---------+-----------------+----------+-----------------------------------------------------------------+-------+ | bert@bert.intra | bert | 5000 | 5000 | /var/spool/mail/virtual | bert/ | 1 | 1 | ChangeMe | $5$60562f6712a27bd7$DRvjbGL9gtmVzTEttqxfjKvRej5m..EJrsQ55gsWTC3 | | | jeanne@bert.intra | jeanne | 5000 | 5000 | /var/spool/mail/virtual | jeanne/ | 1 | 1 | ChangeMe | $5$6a3b537009ebac2e$5P0SenoLr5ECM02OXQtGIl/hcDXX5E.X9AhuVBIcyV7 | | | root@localhost | root | 5000 | 5000 | /var/spool/mail/virtual | root/ | 1 | 1 | ChangeMe | $5$62ab7e402e4f255b$aHv4qXJ8weUE7MtTRaQF/ZqB1amCdjxi6XZqNOwnf77 | | | tito@bert.intra | tito | 5000 | 5000 | /var/spool/mail/virtual | tito/ | 1 | 1 | ChangeMe | $5$68baf3db6781968b$fNlBZl/6hMoqI4K1mAp0n6XaqQ8QNk/JMawQGrj0wD8 | | +-------------------+--------+------+------+-------------------------+---------+---------+-----------------+----------+-----------------------------------------------------------------+-------+ 4 rows in set (0.00 sec)