[Proftpd-user] MySQL Authentication doesn't work on ProFTPD 1.3.8

Hello! I've installed ProFTPd 1.3.8 from the source on Ubuntu Server 
22.04 LTS. I have mod_vroot up-to-date, too. I'm trying to set up to 
enable both UNIX shell account and MySQL. And its MySQL function doesn't 
work. It says that Login Incorrect. (Even setting SQLAuthTypes to Empty 
doesn't let me login...) UNIX shell account works. How can I enable the 
MySQL authentication?

For this post, I set the account ID to 'example' and the passwod to 
'password'.

--- ./configure ----

./configure 
--with-includes=/usr/include/mariadb:/usr/include/mariadb/mysql:/usr/include/openssl:/usr/include/gnutls 
--sysconfdir=/usr/local/etc/proftpd --libexecdir=/usr/local/lib/proftpd 
--localstatedir=/run/proftpd --enable-sendfile --enable-openssl 
--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 
--with-shared=mod_unique_id:mod_site_misc:mod_load:mod_ban:mod_quotatab:mod_sql:mod_sql_mysql:mod_sql_sqlite:mod_sql_odbc:mod_dynmasq:mod_quotatab_sql: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:mod_vroot 
--enable-devel=yes

--- sql.conf ---

#
# Proftpd sample configuration for SQL-based authentication.
#
# (This is not to be used if you prefer a PAM-based SQL authentication)
#

<IfModule mod_sql.c>
#
# Choose a SQL backend among MySQL or PostgreSQL.
# Both modules are loaded in default configuration, so you have to 
specify the backend
# or comment out the unused module in /usr/local/etc/proftpd/modules.conf.
# Use 'mysql' or 'postgres' as possible values.
#
SQLBackend    mysql
#
SQLEngine on
SQLAuthenticate on

#
# Use both a crypted or plaintext password
# SQLAuthTypes Crypt Plaintext
# Use a backend-crypted or a crypted password
# SQLAuthTypes Backend Crypt
#
SQLAuthTypes MD5
SQLPasswordEngine on
SQLPasswordEncoding hex
# Connection
SQLConnectInfo proftpd proftpd password
#
# Describes both users/groups tables
#
SQLUserInfo ftpuser userid passwd uid gid homedir shell
SQLGroupInfo ftpgroup groupname gid members

SQLMinUserUID 60001
SQLMinUserGID 60001


# create a user's home directory on demand if it doesn't exist
#CreateHome on

# Update count every time user logs in
SQLLog PASS updatecount
SQLNamedQuery updatecount UPDATE "count=count+1, accessed=now() WHERE 
userid='%u'" ftpuser

# Update modified everytime user uploads or deletes a file
SQLLog  STOR,DELE modified
SQLNamedQuery modified UPDATE "modified=now() WHERE userid='%u'" ftpuser

# User quotas
# ===========
QuotaEngine on
QuotaDirectoryTally on
QuotaDisplayUnits Mb
QuotaShowQuotas on

SQLNamedQuery get-quota-limit SELECT "name, quota_type, per_session, 
limit_type, bytes_in_avail, bytes_out_avail, bytes_xfer_avail, 
files_in_avail, files_out_avail, files_xfer_avail FROM ftpquotalimits 
WHERE name = '%{0}' AND quota_type = '%{1}'"

SQLNamedQuery get-quota-tally SELECT "name, quota_type, bytes_in_used, 
bytes_out_used, bytes_xfer_used, files_in_used, files_out_used, 
files_xfer_used FROM ftpquotatallies WHERE name = '%{0}' AND quota_type 
= '%{1}'"

SQLNamedQuery update-quota-tally UPDATE "bytes_in_used = bytes_in_used + 
%{0}, bytes_out_used = bytes_out_used + %{1}, bytes_xfer_used = 
bytes_xfer_used + %{2}, files_in_used = files_in_used + %{3}, 
files_out_used = files_out_used + %{4}, files_xfer_used = 
files_xfer_used + %{5} WHERE name = '%{6}' AND quota_type = '%{7}'" 
ftpquotatallies

SQLNamedQuery insert-quota-tally INSERT "%{0}, %{1}, %{2}, %{3}, %{4}, 
%{5}, %{6}, %{7}" ftpquotatallies

QuotaLimitTable sql:/get-quota-limit
QuotaTallyTable sql:/get-quota-tally/update-quota-tally/insert-quota-tally

SQLLogFile /home/ftpuser/sql.log
#
</IfModule>

--- ftpuser table of proftpd database ---

MariaDB [proftpd]> SELECT * FROM ftpuser;
+----+---------+----------------------------------+-------+-------+----------------------+---------------+-------+---------------------+---------------------+
| id | userid  | passwd                           | uid   | gid | 
homedir              | shell         | count | accessed            | 
modified            |
+----+---------+----------------------------------+-------+-------+----------------------+---------------+-------+---------------------+---------------------+
|  1 | example | 5f4dcc3b5aa765d61d8327deb882cf99 | 60001 | 60001 | 
/home/ftpuser/public | /sbin/nologin |    19 | 2018-12-07 05:40:28 | 
2018-08-16 06:22:00 |
+----+---------+----------------------------------+-------+-------+----------------------+---------------+-------+---------------------+---------------------+
1 row in set (0.001 sec)

--- ftpgroup table of proftpd database ---

MariaDB [proftpd]> SELECT * FROM ftpgroup;
+-----------+-------+---------+
| groupname | gid   | members |
+-----------+-------+---------+
| ftpgroup  | 60001 | example |
+-----------+-------+---------+
1 row in set (0.000 sec)

MariaDB [proftpd]> SELECT * FROM ftpquotalimits;
+---------+------------+-------------+------------+----------------+-----------------+------------------+----------------+-----------------+------------------+
| name    | quota_type | per_session | limit_type | bytes_in_avail | 
bytes_out_avail | bytes_xfer_avail | files_in_avail | files_out_avail | 
files_xfer_avail |
+---------+------------+-------------+------------+----------------+-----------------+------------------+----------------+-----------------+------------------+
| example | user       | false       | soft       |              0 
|               0 |                0 |              0 |               0 
|                0 |
+---------+------------+-------------+------------+----------------+-----------------+------------------+----------------+-----------------+------------------+
1 row in set (0.000 sec)

MariaDB [proftpd]> SELECT * FROM ftpquotatallies;
+---------+------------+---------------+----------------+-----------------+---------------+----------------+-----------------+
| name    | quota_type | bytes_in_used | bytes_out_used | 
bytes_xfer_used | files_in_used | files_out_used | files_xfer_used |
+---------+------------+---------------+----------------+-----------------+---------------+----------------+-----------------+
| example | user       |             0 |              0 |               
0 |             0 |              0 | 0 |
+---------+------------+---------------+----------------+-----------------+---------------+----------------+-----------------+
1 row in set (0.001 sec)

-- 
Emmanuel Chanel <[email protected]>

-- 
Emmanuel Chanel( [email protected] )



_______________________________________________
ProFTPD Users List   <[email protected]>
Unsubscribe problems?
http://www.proftpd.org/list-unsub.html