AuthenticatedSmtpUsingMysql

How to authenticate users using MySQL.

We use two types of login data user can use: user@domain or just simple uid. : From the first one we extract the local_part and the domain.

Make sure you have all the requried fields in sql table, such as: domain_name, local_part, user_id.

To prevent bug (auth when user supply empty password) we use a condition to check if it's not empty.

SQL Injection

It's also strongly advised to use quote_mysql function, which is not used in example below—documented at end of § 22 in the manual.

    plain:
     driver = plaintext
     public_name = PLAIN
     server_prompts = :

     server_condition = "${if and { \
                          {!eq{$2}{}} \
                          {!eq{$3}{}} \
                          {crypteq{$3}{${lookup mysql{SELECT password FROM customers WHERE ( domain_name = \
                    '${domain:$2}' \
                    AND local_part = '${local_part:$2}') OR user_id='$2' }{$value}fail}} }} {yes}{no}}"
     server_set_id = $2

    login:
      driver = plaintext
      public_name = LOGIN
      server_prompts = "Username:: : Password::"
      server_condition = "${if and { \
                          {!eq{$1}{}} \
                          {!eq{$2}{}} \
                          {crypteq{$2}{${lookup mysql{SELECT password FROM customers WHERE  (domain_name =\
                          '${domain:$1}'
                    AND local_part = '${local_part:$1}') OR user_id='$1'}{$value}fail}} }} {yes}{no}}"

      server_set_id = $1

* * * * *

> To use Mysql and PAM Auth based on "@" in name use: If the username
> not include @, use PAM. If the username include @, use Mysql.

The example run if MYSQL\_SERVER has set or not... If MYSQL\_SERVER not
set, only PAM run (only users without @ in name)...

The full configuration files has in
[ConfigurationFile](ConfigurationFile) session

Create SQL commands

    CREATE TABLE `domain` (
      `domain_name` varchar(128) NOT NULL default '',
      `alias` varchar(64) default NULL,
      `home` varchar(128) NOT NULL default '/var/spool/virtual/DomainDir',
      `uid` smallint(5) unsigned NOT NULL default '8',
      `gid` smallint(5) unsigned NOT NULL default '12',
      `password_hash` varchar(128) NOT NULL default '',
      `password_md5` varchar(128) NOT NULL default '',
      `password_clear` varchar(128) default NULL,
      `max_popbox` int(11) NOT NULL default '10',
      `max_alias` int(11) NOT NULL default '10',
      `quotas` int(12) unsigned NOT NULL default '10',
      `quotac` int(12) unsigned NOT NULL default '500',
      `ativo` tinyint(3) unsigned NOT NULL default '1',
      PRIMARY KEY  (`domain_name`)
    ) TYPE=MyISAM CHECKSUM=1 COMMENT='Virtual Domains';
    CREATE TABLE `popbox` (
      `local_part` varchar(64) NOT NULL default '',
      `domain_name` varchar(128) NOT NULL default '',
      `ativo` tinyint(3) unsigned NOT NULL default '1',
      `password_hash` varchar(128) NOT NULL default '',
      `password_md5` varchar(128) NOT NULL default '',
      `quotas` int(12) unsigned NOT NULL default '0',
      `quotac` int(12) unsigned NOT NULL default '0',
      PRIMARY KEY  (`local_part`,`domain_name`)
    ) TYPE=MyISAM CHECKSUM=1 COMMENT='Popbox Table';

    MYSQL_PASSWD_MD5_PLAIN = SELECT popbox.password_md5 \
                                FROM popbox \
                                LEFT JOIN domain ON ( popbox.domain_name = domain.domain_name ) \
                                WHERE popbox.local_part='${quote_mysql:${extract {1}{@%!}{$2} }}' \
                                    AND popbox.domain_name='${quote_mysql:${extract {2}{@%!}{$2} }}' \
                                    AND domain.ativo='1' \
                                    AND popbox.ativo='1' \
                                LIMIT 1

    # PLAIN: base64-coded - Netscape
    plain:
      driver                = plaintext
      public_name           = PLAIN
    .ifdef MYSQL_SERVER
      server_condition      = "${if eq {${if match{$2}{@}{yes}{no} }}{yes}\
        {${if and{\
            {!eq {$2}{}} {!eq {$3}{}} \
            {eq {${lookup mysql {MYSQL_PASSWD_MD5_PLAIN}{$value}{*:*} }}{${md5:$3} }} \
          }{yes}{no} }}\
          {${if pam{$2:${sg{$3}{:}{::} }}{yes}{no}} }}"
    .else
      server_condition      = ${if pam{$2:${sg{$3}{:}{::} }}{yes}{no}}
    .endif
      server_set_id = $2

    # LOGIN: md5-encoded Outlook Express
    login:
      driver                = plaintext
      public_name           = LOGIN
      server_prompts        = "Username:: : Password::"
    .ifdef MYSQL_SERVER
      server_condition      = "${if eq {${if match{$1}{@}{yes}{no} }}{yes}\
        {${if and{\
            {!eq {$1}{}} {!eq {$2}{}} \
            {eq {${lookup mysql {MYSQL_PASSWD_MD5_LOGIN}{$value}{*:*} }}{${md5:$2} }} \
          }{yes}{no} }}\
          {${if pam{$1:${sg{$2}{:}{::} }}{yes}{no}} }}"
    .else
      server_condition      = ${if pam{$1:${sg{$2}{:}{::} }}{yes}{no}}
    .endif
      server_set_id = $1