mgr's weblog

Changing Courier and Postfix to use crypted passwords stored in a mysql database (without saslauthd and PAM)

January 16, 2006, Miscellaneous

You've probably noticed that I recently moved my weblog to a new host. Well, I also want to use the emailing system of the new host but the current installation has stored the passwords for smtp[s] and imap[s]/pop3[s] in plaintext in a mysql database. I do not like that, so I changed it. (Not that I like to do such system administratior's stuff.) Here is my log of the necessary changes.

English version of the Changelog

  • Built a custom version of libsasl2 in /usr/src/libsasl2:

    • "apt-get source libsasl2"
    • "apt-get build-dep libsasl2" (The packets will be deleted automatically on the next run of Aptitude after the building.)
    • Downloded the Patch of
      http://frost.ath.cx/software/cyrus-sasl-patches/ for crypt(3) hashed password from various backends, corrected and adopted it for Debian (configure.in instead of makefile.in). Result:
      cyrus-sasl-2.1.19-checkpw.c.patch_mgr
    • Copied the patch as 25_cyrus-sasl-2.1.19-checkpw.c.patch to /usr/src/libsasl2/cyrus-sasl2-2.1.19/debian/patches/.
    • Deb packets built via "fakeroot debian/rules binary".
    • Resulting Debs installed:
      • libsasl2_2.1.19-1.7_i386.deb
      • libsasl2-modules_2.1.19-1.7_i386.deb
      • libsasl2-modules-sql_2.1.19-1.7_i386.deb
      and set to HOLD. DO NOT CHANGE THIS! (OR BUILD NEW VERSIONS.)

  • Database 'postfix' modified:

    • New column 'crypted_password', contains the password that is crypted via crypt(3). In phpMyAdmin you can just enter the password in the input field, as you would normally do it, and then select "ENCRYPT" in the column named 'Function'.
    • The existing passwords are copied, encryped via encrypt(), from 'password' to 'crypt_password' with the following quit'n'dirty hack:
      • Log in with "mysql -u postfix -p postfix" and enter: 
        select concat(
       'update users set crypted_password = encrypt("',
       password, '") where password="',password,'";')
       from users;
      • Copy the resulting lines that look like: 
        update users set crypted_password = encrypt("secret")
             where password="secret";
        via the mouse and execute them.
    • (The old column 'password' is now actually superfluous, but it has not been deleted until the whole thing has been tested thoroughly.)

  • Courier modified, file /etc/courier/authmysqlrc:

    • Added the line "MYSQL_CRYPT_PWFIELD crypted_password".
    • Commented out the line "MYSQL_CLEAR_PWFIELD password" (via #).
    • (Retaining the old version as
      authmysqlrc_before-crypted-passwords.)

  • Postfix converted, file /etc/postfix/sasl/smtpd.conf:

    • Modifications (in the Unified DIFF Format): 
      - sql_select: select password from users where email='%u@%r'
+ sql_select: select crypted_password from users where email='%u@%r'
+ password_format: crypt
    • (Retaining the old version as
      smtpd.conf_before-crypted-passwords.)

  • Fetching and sending mails tested with two test accounts.

    ⇒ Yeah, done. :-)


German version of the Changelog

  • Eigene Version von libsasl2 in /usr/src/libsasl2 gebaut:

    • "apt-get source libsasl2"
    • "apt-get build-dep libsasl2" (Die Pakete werden nach dem Bauen beim nächste Aptitude-Lauf automatisch wieder gelöscht).
    • Patch von http://frost.ath.cx/software/cyrus-sasl-patches/ für crypt(3) hashed password from various backends heruntergeladen, korrigiert und für Debian angepaßt (configure.in statt makefile.in). Ergebnis: cyrus-sasl-2.1.19-checkpw.c.patch_mgr
    • Patch als 25_cyrus-sasl-2.1.19-checkpw.c.patch nach
      /usr/src/libsasl2/cyrus-sasl2-2.1.19/debian/patches/ kopiert.
    • Deb-Pakete mit "fakeroot debian/rules binary" gebaut.
    • Entstandene Debs installiert:
      • libsasl2_2.1.19-1.7_i386.deb
      • libsasl2-modules_2.1.19-1.7_i386.deb
      • libsasl2-modules-sql_2.1.19-1.7_i386.deb
      und auf HOLD gestellt. BEIBEHALTEN! (ODER NEUE VERSIONEN BAUEN.)

  • Datenbank 'postfix' modifiziert:

    • Neue Spalte 'crypted_password', enthält das mit crypt(3) verschlüsselte Paßwort. Mit phpMyAdmin gibt man das Paßwort ganz normal in dem Eingabefeld an und wählt dann "ENCRYPT" in der Spalte 'Function' aus.
    • Bisherige Paßwörter mittels nachfolgendem Quick'n'Dirty-Hack von 'password' mit encrypt() verschlüsselt nach 'crypt_password' kopiert:
      • Mit "mysql -u postfix -p postfix" eingeloggt und folgendes getippt: 
        select concat(
       'update users set crypted_password = encrypt("',
       password, '") where password="',password,'";')
       from users;
      • Die entstandenen Zeilen mit dem Schema: 
        update users set crypted_password = encrypt("geheim")
             where password="geheim";
        per Maus kopiert und ausgeführt.
    • (Die alte Spalte 'password' ist nun eigentlich überflüssig, wurde aber zunächst beibehalten.)

  • Courier umgestellt, Datei /etc/courier/authmysqlrc:

    • Zeile "MYSQL_CRYPT_PWFIELD crypted_password" hinzugefügt.
    • Zeile "MYSQL_CLEAR_PWFIELD password" auskommentiert (mit #).
    • (Alte Version als authmysqlrc_before-crypted-passwords
      beibehalten.)

  • Postfix umgestellt, Datei /etc/postfix/sasl/smtpd.conf:

    • Änderung (im Unified DIFF Format): 
      - sql_select: select password from users where email='%u@%r'
+ sql_select: select crypted_password from users where email='%u@%r'
+ password_format: crypt
    • (Alte Version als smtpd.conf_before-crypted-passwords
      beibehalten.)

  • Mails abholen und versenden mit zwei Testaccounts gestestet.

    ⇒ Yeah, fertig. :-)

Permanent Link | Edit | Delete
Trackback pings for this entry are listed below. The URL to ping for this entry is: https://blog.matroid.org:443/trackback/26

Select a Theme:

Basilique du Sacré-Cœur de Montmartre (Paris) Parc Floral de Paris Castillo de Santa Barbara (Alicante) About the photos

Entries: