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

On proftpd.conf , changing this part "solved" the problem.

AuthOrder mod_auth_pam.c* mod_auth_unix.c mod_sql.c -> AuthOrder 
mod_sql.c mod_auth_unix.c

On 2022/12/06 12:03, Emmanuel Chanel wrote:
> 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] )



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