Cleaning out the MySQL bits.

1 files changed, 47 insertions, 328 deletions

diff --git a/fripost-docs.org b/fripost-docs.org
index c6e928a..3211faf 100644
--- a/fripost-docs.org
+++ b/fripost-docs.org
@@ -270,11 +270,6 @@ access to all credentials. Of course the LDAP server should only be listening to
 the machines hosting these services and ideally, should not be directly facing the
 internet.
 
-[TODO: Replace the MySQL database by an LDAP tree.]
-
-The main server will also be responsible for keeping all users in an MySQL
-database that will be replicated using MySQL.
-
 **** Definitions
 
 IMAP server = the main storage server
@@ -360,267 +355,6 @@ host using something like:
 
 telnet localhost $ORIGIN_PORT
 
-*** Installing MySQL
-     - sudo apt-get install mysql-server
-     - generate a long (25 characters) password for the mysql root user
-     - /etc/mysql/my.cnf: skip-innodb
-*** MySQL on the main IMAP server
-**** Overview
-
-We will use four tables `alias', `domain', `log' and `mailbox'.
-
-***** mysql> show tables;
-+----------------+
-| Tables_in_mail |
-+----------------+
-| alias          |
-| domain         |
-| log            |
-| mailbox        |
-+----------------+
-4 rows in set (0.00 sec)
-
-***** mysql> describe alias;
-+-------------+--------------+------+-----+---------------------+-------+
-| Field       | Type         | Null | Key | Default             | Extra |
-+-------------+--------------+------+-----+---------------------+-------+
-| address     | varchar(255) | NO   | PRI |                     |       |
-| goto        | text         | NO   |     | NULL                |       |
-| domain      | varchar(255) | NO   |     |                     |       |
-| create_date | datetime     | NO   |     | 0000-00-00 00:00:00 |       |
-| change_date | timestamp    | NO   |     | CURRENT_TIMESTAMP   |       |
-| active      | tinyint(4)   | NO   |     | 1                   |       |
-+-------------+--------------+------+-----+---------------------+-------+
-6 rows in set (0.00 sec)
-
-***** mysql> describe domain;
-+-------------+--------------+------+-----+---------------------+-------+
-| Field       | Type         | Null | Key | Default             | Extra |
-+-------------+--------------+------+-----+---------------------+-------+
-| domain      | varchar(255) | NO   | PRI |                     |       |
-| description | varchar(255) | NO   |     |                     |       |
-| create_date | datetime     | NO   |     | 0000-00-00 00:00:00 |       |
-| change_date | timestamp    | NO   |     | CURRENT_TIMESTAMP   |       |
-| active      | tinyint(4)   | NO   |     | 1                   |       |
-+-------------+--------------+------+-----+---------------------+-------+
-5 rows in set (0.00 sec)
-
-***** mysql> describe log;
-+-------+-------------+------+-----+-------------------+----------------+
-| Field | Type        | Null | Key | Default           | Extra          |
-+-------+-------------+------+-----+-------------------+----------------+
-| id    | int(11)     | NO   | PRI | NULL              | auto_increment |
-| user  | varchar(20) | NO   |     |                   |                |
-| event | text        | NO   |     | NULL              |                |
-| date  | timestamp   | NO   |     | CURRENT_TIMESTAMP |                |
-+-------+-------------+------+-----+-------------------+----------------+
-4 rows in set (0.00 sec)
-
-***** mysql> describe mailbox;
-+-------------+--------------+------+-----+---------------------+-------+
-| Field       | Type         | Null | Key | Default             | Extra |
-+-------------+--------------+------+-----+---------------------+-------+
-| username    | varchar(255) | NO   | PRI |                     |       |
-| password    | varchar(255) | NO   |     |                     |       |
-| name        | varchar(255) | NO   |     |                     |       |
-| maildir     | varchar(255) | NO   |     |                     |       |
-| domain      | varchar(255) | NO   |     |                     |       |
-| create_date | datetime     | NO   |     | 0000-00-00 00:00:00 |       |
-| change_date | timestamp    | NO   |     | CURRENT_TIMESTAMP   |       |
-| active      | tinyint(4)   | NO   |     | 1                   |       |
-+-------------+--------------+------+-----+---------------------+-------+
-8 rows in set (0.00 sec)
-
-**** Steps to produce it
-mysql -u root -p
-
-   create database mail;
-
-sudo mysql -u root -p --database=mail
-FIXME: Not 100 % up to date
-       :HIDDEN:
-DROP TABLE IF EXISTS `alias`;
-SET @saved_cs_client     = @@character_set_client;
-SET character_set_client = utf8;
-CREATE TABLE `alias` (
-  `address` varchar(255) NOT NULL default '',
-  `goto` text NOT NULL,
-  `domain` varchar(255) NOT NULL default '',
-  `create_date` datetime NOT NULL default '0000-00-00 00:00:00',
-  `change_date` datetime NOT NULL default '0000-00-00 00:00:00',
-  `active` tinyint(4) NOT NULL default '1',
-  PRIMARY KEY  (`address`)
-) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Virtual Aliases - mysql_virtual_\nalias_maps';
-SET character_set_client = @saved_cs_client;
-
-DROP TABLE IF EXISTS `domain`;
-SET @saved_cs_client     = @@character_set_client;
-SET character_set_client = utf8;
-CREATE TABLE `domain` (
-  `domain` varchar(255) NOT NULL default '',
-  `description` varchar(255) NOT NULL default '',
-  `create_date` datetime NOT NULL default '0000-00-00 00:00:00',
-  `change_date` datetime NOT NULL default '0000-00-00 00:00:00',
-  `active` tinyint(4) NOT NULL default '1',
-  PRIMARY KEY  (`domain`)
-) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Virtual Domains - mysql_virtual_\ndomains_maps';
-SET character_set_client = @saved_cs_client;
-
-DROP TABLE IF EXISTS `log`;
-SET @saved_cs_client     = @@character_set_client;
-SET character_set_client = utf8;
-CREATE TABLE `log` (
-  `id` int(11) NOT NULL auto_increment,
-  `user` varchar(20) NOT NULL default '',
-  `event` text NOT NULL,
-  `date` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
-  PRIMARY KEY  (`id`)
-) ENGINE=MyISAM AUTO_INCREMENT=106 DEFAULT CHARSET=utf8 COMMENT='log table';
-SET character_set_client = @saved_cs_client;
-
-DROP TABLE IF EXISTS `mailbox`;
-SET @saved_cs_client     = @@character_set_client;
-SET character_set_client = utf8;
-CREATE TABLE `mailbox` (
-  `username` varchar(255) NOT NULL default '',
-  `password` varchar(255) NOT NULL default '',
-  `name` varchar(255) NOT NULL default '',
-  `maildir` varchar(255) NOT NULL default '',
-  `domain` varchar(255) NOT NULL default '',
-  `create_date` datetime NOT NULL default '0000-00-00 00:00:00',
-  `change_date` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
-  `active` tinyint(4) NOT NULL default '1',
-  PRIMARY KEY  (`username`)
-) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Virtual Mailboxes - mysql_virtua\nl_mailbox_maps';
-SET character_set_client = @saved_cs_client;
-        :END:
-
-mysql -u root -p
-
-# Create triggers
-
-       use mail;
-
-       DELIMITER $$
-       CREATE TRIGGER alias_set_created_on_insert before insert on alias
-         for each row begin set new.create_date = current_timestamp; end$$
-       CREATE TRIGGER domain_set_created_on_insert before insert on domain
-         for each row begin set new.create_date = current_timestamp; end$$
-       CREATE TRIGGER mailbox_set_created_on_insert before insert on mailbox
-         for each row begin set new.create_date = current_timestamp; end$$
-       DELIMITER ;
-
-# Create mail user
-
-       CREATE USER 'mail'@'localhost' IDENTIFIED BY '<password>';
-       GRANT SELECT ON mail.alias   TO 'mail'@'localhost';
-       GRANT SELECT ON mail.domain  TO 'mail'@'localhost';
-       GRANT SELECT ON mail.mailbox TO 'mail'@'localhost';
-
-*** Configuring the MySQL replication
-***** Overview
-[[http://dev.mysql.com/doc/refman/5.0/en/replication.html][MySQL 5.0 Reference Manual :: 16 Replication]]
-
-We will use MySQL replication to keep the MySQL user data on the smarthosts
-in sync with the data held on the main IMAP server.
-
-These instructions are mainly adapted from the MySQL manual.
-
-***** Configure the master
-
- :: /etc/mysql/my.cnf:
-
-    server-id		= 1
-    log_bin		= /var/log/mysql/mysql-bin.log
-    expire_logs_days	= 10
-    max_binlog_size	= 100M
-    binlog_do_db	= mail
-
-sudo service mysql restart
-
-# Enter MySQL shell and create a user with replication privileges.
-# NB: Use only ASCII for the <password>
-mysql -u root -p
-
-    GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'localhost' IDENTIFIED BY '<password>';
-    FLUSH PRIVILEGES;
-
-***** Configure the slave
-****** Set up an SSH tunnel
-
-We begin by setting up an SSH tunnel from the slave to the master, as described [[Configuring an SSH tunnel between two hosts][above]].
-
-****** Preparing steps to take on master
-
-# Make a database dump.
-
-mysql -u root -p
-
-    USE mail;
-    FLUSH TABLES WITH READ LOCK;
-    quit;
-
-mysqldump -u root -p --opt mail > mydump.sql
-
-# Now, transfer this file to the slave.  After you have transferred the file,
-# delete all copies except the one on the slave.
-
-# Save the output of the SHOW MASTER STATUS COMMAND.
-mysql -u root -p
-
-    SHOW MASTER STATUS;
-    unlock tables;
-    quit;
-
-****** Slave configuration
-
-# Create a new temporary directory.
-# NOTE: It has to be outside of /tmp so the replication is not screwed up on e.g. power outage.
-
-TMP_DIR=/var/lib/mysql/tmp
-sudo mkdir $TMP_DIR
-sudo chown mysql:mysql $TMP_DIR
-sudo chmod 0750 $TMP_DIR
-
- :: /etc/mysql/my.cnf
-
-    tmpdir		= /var/lib/mysql/tmp
-    # Note that the server-id must be different on all hosts
-    server-id		= 2
-    relay-log		= mysqld-relay-bin
-
-sudo service mysql restart
-
-# Enter the MySQL shell and create the database:
-
-mysql -u root -p
-
-    CREATE DATABASE mail;
-    quit;
-
-mysql -u root -p --database=mail < mydump.sql
-
-# [[http://dev.mysql.com/doc/refman/5.0/en/change-master-to.html][12.5.2.1. CHANGE MASTER TO Syntax]]
-# NOTE: fill in these values using output from SHOW MASTER STATUS; above
-# NOTE: filling this in my.cnf is deprecated
-
-mysql -u root -p
-
-    SLAVE STOP;
-
-    CHANGE MASTER TO
-    MASTER_HOST='127.0.0.1',
-    MASTER_PORT=1949,
-    MASTER_USER='slave_user',
-    MASTER_PASSWORD='<password>', MASTER_LOG_FILE='mysql-bin.000013', MASTER_LOG_POS=98;
-
-    START SLAVE;
-    show slave status\G
-
-# If it seems OK, just:
-
-    quit;
-
 *** Configuring the LDAP server
 
 On Debian Squeeze, OpenLDAP's configuration no longer uses `/etc/ldap/slapd.conf'
@@ -1371,14 +1105,23 @@ to be flooded.
 **** Test delivery
 
 sudo mkdir -p /home/mail/virtual/fripost.org/
-mysql -u root -p
 
-    INSERT INTO mailbox (username,password,name,maildir,domain)
-    VALUES ('exempel@fripost.org','test666','Exempelanvändare','fripost.org/exempel/Maildir/','fripost.org');
+ :: /tmp/example.ldif
+ 
+    dn: dc=fripost.org,ou=virtual,o=mailHosting,dc=fripost,dc=org
+    objectClass: virtualDomain
+    isActive: TRUE
+
+    dn: uid=example,dc=fripost.org,ou=virtual,o=mailHosting,dc=fripost,dc=org
+    objectClass: virtualMailbox
+    userPassword: test666
+    isActive: TRUE
+
+ldapadd -xWD cn=admin,dc=fripost,dc=org -f /tmp/example.ldif
 
 sudo /etc/init.d/postfix restart
 
-echo "test at `date`"|mail -s "test" exempel@fripostorg
+echo "test at `date`" | mail -s "test" exempel@fripost.org
 
 **** Configuring dovecot
 
@@ -1407,50 +1150,27 @@ mechanisms = plain cram-md5
 #passdb pam <--- comment this stuff out
 
 # uncomment this stuff
-passdb sql {
-  args = /etc/dovecot/dovecot-sql.conf
+passdb ldap {
+    args = /etc/dovecot/dovecot-ldap.conf
 }
 
 #userdb passwd  <--- comment this stuff out
 
 # uncomment this stuff
-userdb sql {
-   args = /etc/dovecot/dovecot-sql.conf
+userdb static {
+    args = uid=115 gid=8 home=/home/mail/virtual/%d/%n/ allow_all_users=yes
 }
+# We are not making use of the User Database (to ensure that Dovecot's `deliver' checks
+# that the recipient exists) here, since `deliver' should only be called by Postfix which
+# takes care of that (cf. `ldap_virtual_mailbox_maps.cf'). Hence the `allow_all_users=yes'
+# above.
 
 # Do not needlessly run as root
 user = nobody
 :END:
 
-:: /etc/dovecot/dovecot-sql.conf
-
-:HIDDEN:
-driver = mysql
-connect = host=127.0.0.1 port=3306 user=XXX password=XXX dbname=mail
-
-# Salted MD5
-default_pass_scheme = SMD5
-
-password_query = SELECT username AS user, password FROM mailbox WHERE username = '%u' AND domain = '%d'
-
-# replace XXX with relevant numbers for the system
-user_query = SELECT concat('/home/mail/virtual/',maildir) AS mail, XXX AS uid, XXX AS gid FROM mailbox WHERE username = '%u' AND domain = '%d'
-:END:
-
-sudo /etc/init.d/dovecot restart
-
-# Provided there is a user, you should now be able to login using any IMAP
-# client.
-
-**** Making sure the services are not started at boot [might not be needed]
-sudo update-rc.d -n dovecot stop 2 3 4 5 .
-sudo update-rc.d -n postfix stop 2 3 4 5 .
-
 **** Use LDAP authenticate binds, and LDAP user queries.
 
-[TODO: The following handle the dialog the LDAP server. It should replace
-the MySQL bits above.]
-
 Instead of making a SQL query to fetch the (hashed) passwords, which implies to
 expose all credentials to Dovecot, an other approach is to forward the login
 information to our LDAP server, that will match it against the hashed copy contained
@@ -1462,49 +1182,48 @@ http://wiki2.dovecot.org/HowTo/DovecotOpenLdap
 http://wiki2.dovecot.org/AuthDatabase/LDAP/AuthBinds
 
 Debian provides a squeleton configuration in /usr/share/dovecot/dovecot-ldap.conf .
-Copy this file in /etc/dovecot, and chmod 600 it. Uncomment the following lines:
-
-    hosts = localhost
-    ldap_version = 3
-    auth_bind = yes
-    auth_bind_userdn = uid=%n,dc=%d,ou=virtual,o=mailHosting,dc=fripost,dc=org
-    base = uid=%n,dc=%d,ou=virtual,o=mailHosting,dc=fripost,dc=org
-    deref = never
-    scope = base
-    pass_attrs = uid=user
-    pass_filter = (&(objectClass=virtualMailbox)(uid=%n)(isActive=TRUE))
+Copy this file in /etc/dovecot, and chmod 600 it.
 
-(And the TLS-related lines in case we are not using a tunnel.)
+:: /etc/dovecot/dovecot-ldap.conf
 
-We can now amend the `dovecot.conf': Comment the "passwd sql {...}" and "userdb sql {...}"
-blocks, and uncomment
+:HIDDEN:
+hosts = localhost
+ldap_version = 3
+auth_bind = yes
+auth_bind_userdn = uid=%n,dc=%d,ou=virtual,o=mailHosting,dc=fripost,dc=org
+base = uid=%n,dc=%d,ou=virtual,o=mailHosting,dc=fripost,dc=org
+deref = never
+scope = base
+pass_attrs = uid=user
+pass_filter = (&(objectClass=virtualMailbox)(uid=%n)(isActive=TRUE))
+:END:
 
-      passdb ldap {
-        args = /etc/dovecot/dovecot-ldap.conf
-      }
-      # and
-      userdb static {
-        args = uid=115 gid=8 home=/home/mail/virtual/%d/%n/ allow_all_users=yes
-      }
+sudo /etc/init.d/dovecot restart
 
-We are not making use of the User Database (to ensure that Dovecot's `deliver' checks
-that the recipient exists) here, since `deliver' should only be called by Postfix which
-takes care of that (cf. `ldap_virtual_mailbox_maps.cf'). Hence the `allow_all_users=yes'
-above.
+# Provided there is a user, you should now be able to login using any IMAP
+# client.
+#
+#   $~ openssl s_client -connect localhost:993
+#   [...]
+#   * OK [CAPABILITY IMAP4rev1 LITERAL+ SASL-IR LOGIN-REFERRALS ID ENABLE AUTH=PLAIN AUTH=LOGIN] Dovecot ready.
+
+**** Making sure the services are not started at boot [might not be needed]
+sudo update-rc.d -n dovecot stop 2 3 4 5 .
+sudo update-rc.d -n postfix stop 2 3 4 5 .
 
 *** Configuring a new smarthost to relay e-mail to the main IMAP server
 **** Overview
 
 We relay mail from our smarthosts to the main IMAP server.
 
-This is to avoid having a single poin of failure and to separate concerns. The
+This is to avoid having a single point of failure and to separate concerns. The
 IMAP server then only needs to deal with authenticated clients and the
 smarthosts.
 
 **** Prerequisites
 
 Before this can work we must make sure that:
-- the MySQL replication is working
+- the RDAP replication is working
 - there is an SSH tunnel for the smtp
 
 If they are both setup, we can configure postfix on the smarthost to relay