proftpd 1.3.5a-1build1 with SQLAuthTypes other than plaintext doesn't recognize passwords

[Guillaume]

On Ubuntu server 16.04.1 AMD64, i can't login on proftpd since no plaintext passwords aren't recognized anymore.
I'm usually running Backend SQLAuthTypes.

Version information :
~# proftpd -V
Compile-time Settings:
  Version: 1.3.5a (maint)
  Platform: LINUX [Linux 4.4.0-38-generic x86_64]
  Built: Tue Apr 5 2016 13:36:50 UTC
  Built With:
    configure 'CFLAGS=-g -O2 -fstack-protector-strong -Wformat -Werror=format-security' 'CPPFLAGS=-Wdate-time -D_FORTIFY_SOURCE=2' 'CXXFLAGS=-g -O2 -fstack-protector-strong -Wformat -Werror=format-security' 'FCFLAGS=-g -O2 -fstack-protector-strong' 'FFLAGS=-g -O2 -fstack-protector-strong' 'GCJFLAGS=-g -O2 -fstack-protector-strong' 'LDFLAGS=-Wl,-Bsymbolic-functions -Wl,-z,relro' 'OBJCFLAGS=-g -O2 -fstack-protector-strong -Wformat -Werror=format-security' 'OBJCXXFLAGS=-g -O2 -fstack-protector-strong -Wformat -Werror=format-security' '--prefix=/usr' '--with-includes=/usr/include/postgresql:/usr/include/mysql' '--mandir=/usr/share/man' '--sysconfdir=/etc/proftpd' '--localstatedir=/run' '--libexecdir=/usr/lib/proftpd' '--enable-sendfile' '--enable-facl' '--enable-dso' '--enable-autoshadow' '--enable-ctrls' '--with-modules=mod_readme' '--enable-ipv6' '--enable-nls' '--enable-memcache' '--with-lastlog=/var/log/lastlog' '--enable-pcre' '--build' 'x86_64-linux-gnu' '--with-shared=mod_unique_id:mod_site_misc:mod_load:mod_ban:mod_quotatab:mod_sql:mod_sql_mysql:mod_sql_postgres:mod_sql_sqlite:mod_sql_odbc:mod_dynmasq:mod_quotatab_sql:mod_ldap:mod_quotatab_ldap:mod_ratio:mod_tls:mod_rewrite:mod_radius:mod_wrap:mod_wrap2:mod_wrap2_file:mod_wrap2_sql:mod_quotatab_file:mod_quotatab_radius:mod_facl:mod_ctrls_admin:mod_copy:mod_deflate:mod_ifversion:mod_tls_memcache:mod_geoip:mod_exec:mod_sftp:mod_sftp_pam:mod_sftp_sql:mod_shaper:mod_sql_passwd:mod_ifsession' 'build_alias=x86_64-linux-gnu'

  CFLAGS: -g -O2 -fstack-protector-strong -Wformat -Werror=format-security -Wall
  LDFLAGS: -L$(top_srcdir)/lib -Wl,-Bsymbolic-functions -Wl,-z,relro -L/usr/lib/x86_64-linux-gnu -L/usr/lib/x86_64-linux-gnu
  LIBS: -lpcreposix -lpcre -lssl -lcrypto -lcap -lmemcached -lmemcachedutil -lpam -lsupp -lcrypt -ldl

  Files:
    Configuration File:
      /etc/proftpd/proftpd.conf
    Pid File:
      /run/proftpd.pid
    Scoreboard File:
      /run/proftpd.scoreboard
    Header Directory:
      /usr/include/proftpd
    Shared Module Directory:
      /usr/lib/proftpd

  Features:
    + Autoshadow support
    + Controls support
    + curses support
    - Developer support
    + DSO support
    + IPv6 support
    + Largefile support
    + Lastlog support
    + Memcache support
    + ncurses support
    + NLS support
    + OpenSSL support
    + PCRE support
    + POSIX ACL support
    + Shadow file support
    + Sendfile support
    + Trace support

  Tunable Options:
    PR_TUNABLE_BUFFER_SIZE = 1024
    PR_TUNABLE_DEFAULT_RCVBUFSZ = 8192
    PR_TUNABLE_DEFAULT_SNDBUFSZ = 8192
    PR_TUNABLE_GLOBBING_MAX_MATCHES = 100000
    PR_TUNABLE_GLOBBING_MAX_RECURSION = 8
    PR_TUNABLE_HASH_TABLE_SIZE = 40
    PR_TUNABLE_NEW_POOL_SIZE = 512
    PR_TUNABLE_SCOREBOARD_BUFFER_SIZE = 80
    PR_TUNABLE_SCOREBOARD_SCRUB_TIMER = 30
    PR_TUNABLE_SELECT_TIMEOUT = 30
    PR_TUNABLE_TIMEOUTIDENT = 10
    PR_TUNABLE_TIMEOUTIDLE = 600
    PR_TUNABLE_TIMEOUTLINGER = 30
    PR_TUNABLE_TIMEOUTLOGIN = 300
    PR_TUNABLE_TIMEOUTNOXFER = 300
    PR_TUNABLE_TIMEOUTSTALLED = 3600
    PR_TUNABLE_XFER_SCOREBOARD_UPDATES = 10

Logs from running proftpd -nd10 :
2016-10-02 15:49:21,579 ftp proftpd[11000] : retrieved UID 33 for user 'test'
2016-10-02 15:49:21,579 ftp proftpd[11000] : no supplemental groups found for user 'test'
2016-10-02 15:49:21,580 ftp proftpd[11000] : USER test (Login failed): No such user found
2016-10-02 15:49:21,580 ftp proftpd[11000] : dispatching POST_CMD_ERR command 'PASS (hidden)' to mod_sql
2016-10-02 15:49:21,580 ftp proftpd[11000] : dispatching POST_CMD_ERR command 'PASS (hidden)' to mod_vroot
2016-10-02 15:49:21,580 ftp proftpd[11000] : dispatching POST_CMD_ERR command 'PASS (hidden)' to mod_wrap2
2016-10-02 15:49:21,580 ftp proftpd[11000] : dispatching POST_CMD_ERR command 'PASS (hidden)' to mod_delay
2016-10-02 15:49:21,583 ftp proftpd[11000] : dispatching LOG_CMD_ERR command 'PASS (hidden)' to mod_sql
2016-10-02 15:49:21,583 ftp proftpd[11000] : dispatching LOG_CMD_ERR command 'PASS (hidden)' to mod_log
2016-10-02 15:49:21,584 ftp proftpd[11000] : dispatching LOG_CMD_ERR command 'PASS (hidden)' to mod_auth
2016-10-02 15:49:21,597 ftp proftpd[11000] : mod_tls/2.6: scrubbing 1 passphrase from memory

Log from sql module :
2016-10-02 15:35:24,628 mod_sql/4.3[10669]: query "SELECT userid, passwd, uid, gid, homedir, shell FROM users WHERE (userid='test') AND (((LoginAllowed = 'true'))) LIMIT 1"
2016-10-02 15:35:24,629 mod_sql/4.3[10669]: entering mysql cmd_close
2016-10-02 15:35:24,629 mod_sql/4.3[10669]: connection 'default' count is now 1
2016-10-02 15:35:24,629 mod_sql/4.3[10669]: exiting mysql cmd_close
2016-10-02 15:35:24,629 mod_sql/4.3[10669]: exiting mysql cmd_select
2016-10-02 15:35:24,629 mod_sql/4.3[10669]: cache miss for user 'test'
2016-10-02 15:35:24,629 mod_sql/4.3[10669]: user 'test' cached
2016-10-02 15:35:24,629 mod_sql/4.3[10669]: + pwd.pw_name : test
2016-10-02 15:35:24,629 mod_sql/4.3[10669]: + pwd.pw_uid : 33
2016-10-02 15:35:24,629 mod_sql/4.3[10669]: + pwd.pw_gid : 33
2016-10-02 15:35:24,629 mod_sql/4.3[10669]: + pwd.pw_dir : /var/www
2016-10-02 15:35:24,629 mod_sql/4.3[10669]: + pwd.pw_shell : /bin/false
2016-10-02 15:35:24,629 mod_sql/4.3[10669]: <<< cmd_getpwnam
2016-10-02 15:35:24,629 mod_sql/4.3[10669]: >>> cmd_getgrgid
2016-10-02 15:35:24,629 mod_sql/4.3[10669]: cache miss for GID '33'
2016-10-02 15:35:24,629 mod_sql/4.3[10669]: entering mysql cmd_select
2016-10-02 15:35:24,629 mod_sql/4.3[10669]: entering mysql cmd_open
2016-10-02 15:35:24,629 mod_sql/4.3[10669]: connection 'default' count is now 2
2016-10-02 15:35:24,629 mod_sql/4.3[10669]: exiting mysql cmd_open
2016-10-02 15:35:24,629 mod_sql/4.3[10669]: query "SELECT groupname FROM groups WHERE (gid = 33) LIMIT 1"
2016-10-02 15:35:24,630 mod_sql/4.3[10669]: entering mysql cmd_close
2016-10-02 15:35:24,630 mod_sql/4.3[10669]: connection 'default' count is now 1
2016-10-02 15:35:24,630 mod_sql/4.3[10669]: exiting mysql cmd_close
2016-10-02 15:35:24,630 mod_sql/4.3[10669]: exiting mysql cmd_select
2016-10-02 15:35:24,630 mod_sql/4.3[10669]: <<< cmd_getgrgid
2016-10-02 15:35:24,630 mod_sql/4.3[10669]: >>> cmd_getgroups

...

2016-10-02 15:38:20,605 mod_sql/4.3[10728]: query "SELECT groupname, gid, members FROM groups WHERE (members = 'test' OR members LIKE 'test,%' OR members LIKE '%,test' OR members LIKE '%,test,%')"
2016-10-02 15:38:20,605 mod_sql/4.3[10728]: entering mysql cmd_close
2016-10-02 15:38:20,605 mod_sql/4.3[10728]: connection 'default' count is now 1
2016-10-02 15:38:20,605 mod_sql/4.3[10728]: exiting mysql cmd_close
2016-10-02 15:38:20,605 mod_sql/4.3[10728]: exiting mysql cmd_select
2016-10-02 15:38:20,605 mod_sql/4.3[10728]: <<< cmd_getgroups
2016-10-02 15:38:20,606 mod_sql/4.3[10728]: >>> cmd_auth
2016-10-02 15:38:20,606 mod_sql/4.3[10728]: entering mysql cmd_escapestring
2016-10-02 15:38:20,606 mod_sql/4.3[10728]: entering mysql cmd_open
2016-10-02 15:38:20,606 mod_sql/4.3[10728]: connection 'default' count is now 2
2016-10-02 15:38:20,606 mod_sql/4.3[10728]: exiting mysql cmd_open
2016-10-02 15:38:20,606 mod_sql/4.3[10728]: entering mysql cmd_close
2016-10-02 15:38:20,606 mod_sql/4.3[10728]: connection 'default' count is now 1
2016-10-02 15:38:20,606 mod_sql/4.3[10728]: exiting mysql cmd_close
2016-10-02 15:38:20,606 mod_sql/4.3[10728]: exiting mysql cmd_escapestring
2016-10-02 15:38:20,606 mod_sql/4.3[10728]: cache hit for user 'test'
2016-10-02 15:38:20,606 mod_sql/4.3[10728]: >>> cmd_check
2016-10-02 15:38:20,606 mod_sql/4.3[10728]: checking password using SQLAuthType 'Backend'
2016-10-02 15:38:20,606 mod_sql/4.3[10728]: entering mysql cmd_checkauth
2016-10-02 15:38:20,606 mod_sql/4.3[10728]: password mismatch
2016-10-02 15:38:20,606 mod_sql/4.3[10728]: exiting mysql cmd_checkauth
2016-10-02 15:38:20,606 mod_sql/4.3[10728]: 'Backend' SQLAuthType handler reports failure

proftpd.conf :
Include /etc/proftpd/modules.conf

DefaultAddress 178.33.254.58
SocketBindTight on
UseIPv6 on
IdentLookups off
ServerName "ftp"
ServerIdent off
ServerType standalone
DeferWelcome on
MultilineRFC2228 on
#DefaultServer on
ShowSymlinks on
TimeoutNoTransfer 600
TimeoutStalled 600
TimeoutIdle 1200
ListOptions "-l"
DenyFilter \*.*/
DefaultRoot ~
RequireValidShell off
Port 21
AllowForeignAddress on
MaxInstances 30
User proftpd
Group nogroup
Umask 022 022
AllowOverwrite on

TransferLog /var/log/proftpd/xferlog
SystemLog /var/log/proftpd/proftpd.log

<IfModule mod_quotatab.c>
QuotaEngine off
</IfModule>

<IfModule mod_ratio.c>
Ratios off
</IfModule>

<IfModule mod_delay.c>
DelayEngine on
</IfModule>

<IfModule mod_ctrls.c>
ControlsEngine off
ControlsMaxClients 2
ControlsLog /var/log/proftpd/controls.log
ControlsInterval 5
ControlsSocket /var/run/proftpd/proftpd.sock
</IfModule>

<IfModule mod_ctrls_admin.c>
AdminControlsEngine off
</IfModule>

Include /etc/proftpd/sql.conf
Include /etc/proftpd/tls.conf

* sql.conf
<IfModule mod_sql.c>
SQLBackend mysql
SQLEngine on
SQLAuthenticate on
SQLAuthTypes Backend
SQLConnectInfo proftpd@localhost proftpd XXXXXXXXXXXXX
SQLUserInfo users userid passwd uid gid homedir shell
SQLUserWhereClause "LoginAllowed = 'true'"
SQLGroupInfo groups groupname gid members
SQLAuthenticate users* groups*
SQLLogFile /var/log/proftpd/sql.log
SQLLog PASS updatecount
SQLNamedQuery updatecount UPDATE "count=count+1, accessed=now() WHERE userid='%u'" users
SQLMinID 33
SQLMinUserGID 33
SQLMinUserUID 33
SQLDefaultUID 33
SQLDefaultGID 33

<IfModule mod_auth_pam.c>
AuthPAM off
</IfModule>

</IfModule>

If i change SQLAuthTypes to PlainText and set plaintext password in users table, it works.
I tried with sha-512 :
    LoadModule mod_sql_passwd.c
    SQLAuthTypes SHA512

Generated a password and put it to an user in my mysql database :
    mkpasswd -m sha-512

Then, tried to connect :
    2016-10-05 18:11:05,859 mod_sql/4.3[5030]: checking password using SQLAuthType 'sha512'
    2016-10-05 18:11:05,859 mod_sql/4.3[5030]: 'sha512' SQLAuthType handler reports failure

[Ste-Phan (stephan-skusa-5)]

Anything new to this ... i have got the same problem!!

[Guillaume (e1msih)]

Answer from castaglia (main proFTPD developer) :
Now, starting with MySQL 5.7, the "Backend" SQLAuthType will no longer work. The alternative is to start using the MD5/SHA1/SHA256 (or other) SQLAuthTypes provided by mod_sql_passwd.

Complete answer here :
https://forums.proftpd.org/smf/index.php?topic=12043.15

Basically, Backend SQLAuthType cannot work with MySQL >= 5.7.

[Amr Ibrahim (amribrahim1987)]

In the future, please use 'ubuntu-bug package-name' to report Ubuntu bugs.
https://help.ubuntu.com/community/ReportingBugs

You have reported a bug in a non-existent package in Ubuntu archives. No developer will see this bug report because simply the package does not exist any more.