[SOLVED] pure-ftp problem - with mysql connection

Discussion in 'Server Operation' started by brk, Mar 7, 2020.

  1. brk

    brk New Member

    Hello,
    After upgrade of system (ubuntu 16) and ispconfig (to version 3.1.15p3) , mysql integration with pure-ftpd stopped working.
    I can log by local (shell) user account to ftp, I turned on logging of mysql general log, and debug in pure-ftp, I can see that there are correct SELECTs (and if I run them into mysql console - I can see proper values):

    Code:
    Mar  7 11:08:14 hosting pure-ftpd: ([email protected]) [INFO] New connection from 87.x.x.x
    Mar  7 11:08:19 hosting pure-ftpd: ([email protected]) [DEBUG] Command [user] [user]
    Mar  7 11:08:22 hosting pure-ftpd: ([email protected]) [DEBUG] Command [pass] [<*>]
                     1105 Query     SELECT password FROM ftp_user WHERE active = 'y' AND server_id = '1' AND username="user" AND (expires IS NULL OR expires="0000-00-00 00:00:00" OR expires > NOW())
                     1105 Query     SELECT uid FROM ftp_user WHERE active = 'y' AND server_id = '1' AND username="user" AND (expires IS NULL OR expires="0000-00-00 00:00:00" OR expires > NOW())
                     1105 Query     SELECT gid FROM ftp_user WHERE active = 'y' AND server_id = '1' AND username="user" AND (expires IS NULL OR expires="0000-00-00 00:00:00" OR expires > NOW())
                     1105 Query     SELECT dir FROM ftp_user WHERE active = 'y' AND server_id = '1' AND username="user" AND (expires IS NULL OR expires="0000-00-00 00:00:00" OR expires > NOW())
    Mar  7 11:08:27 hosting pure-ftpd: ([email protected]) [WARNING] Authentication failed for user [user]
    Mar  7 11:08:27 hosting pure-ftpd: ([email protected]) [DEBUG] Command [syst] []
    
    (IP and username is changed in above output)
    I have 'crypt' setting in /etc/pure-ftpd/db/mysql.conf, I tried to use mkpasswd --method=md5 --stdin and putted output into the database (as I can see - there is now sha512 hash, older accounts are in format:
    the freshly created account has following hash:
    I even updated pure-ftp to more recent one from: launchpad.net/~corpusops/+archive/ubuntu/pure-ftpd/+index?field.series_filter=xenial, but it didn't help.

    Can You give me some advice what can be wrong ? And where search more info ?
     
  2. till

    till Super Moderator Staff Member ISPConfig Developer

    You installed pure-ftpd without mysql support. Ensure that you have the package pure-ftpd-mysql installed and not the package pure-ftpd. The package pure-ftpd has no mysql support builtin, so it's important that you install pure-ftpd-mysql package
     
  3. brk

    brk New Member

    I forget to write - of course I installed proper versions:

    Code:
    [[email protected] ~]# dpkg -l | grep pure
    ii  pure-ftpd-common                  1.0.49-xenial-44ubuntu36                   all          Pure-FTPd FTP server (Common Files)
    ii  pure-ftpd-mysql                   1.0.49-xenial-44ubuntu36                   amd64        Secure and efficient FTP server with MySQL user authentication
    
     
  4. brk

    brk New Member

    It looks like some port of system (glibc ?) can't handle sha512 - but even if I used md5 password - it still didn't work.
     
  5. till

    till Super Moderator Staff Member ISPConfig Developer

    Ok, that's good :) Do the accounts with old passwords still work? If not, then it's unlikely that it's caused by the hashing algorithm update and I have not heard that any Ubuntu 16.04 or 18.04 users had issues with sha512.
     
  6. brk

    brk New Member

    OK, I checked this in another way. Created account for another subdomain (was previously added) in the panel, and ... it worked ok... :/ It looks like the particular domain have blocked ftp access - is it posible ? In ispconfig is opportunity to block some (sub)domains from accessing ftp ?
     
  7. till

    till Super Moderator Staff Member ISPConfig Developer

    no, but an ftp user can be set inactive and it can have an expire date. compare the woring and non-working user in the database. And there is one more important thing, take care that you do not have a shell user with the exact username, check in /etc/passwd.
     
  8. brk

    brk New Member

    I don't have such user locally (in /etc/passwd):
    Code:
    [[email protected] /etc]# grep grenton /etc/passwd
    [[email protected] /etc]#
    
    here is output from database, can't see the difference (salts are changed to letters 'd'):
    Code:
    MariaDB [dbispconfig]> select * from ftp_user where ftp_user_id in (12,13);
    +-------------+------------+-------------+---------------+----------------+----------------+-----------+------------------+-------------------+-----------------+------------------------------------------------------------------------------------------------------------------------+------------+--------+-------+---------+--------------------------------+-------------+----------+----------+--------------+--------------+---------+-----------+-------------+
    | ftp_user_id | sys_userid | sys_groupid | sys_perm_user | sys_perm_group | sys_perm_other | server_id | parent_domain_id | username          | username_prefix | password                                                                                                               | quota_size | active | uid   | gid     | dir                            | quota_files | ul_ratio | dl_ratio | ul_bandwidth | dl_bandwidth | expires | user_type | user_config |
    +-------------+------------+-------------+---------------+----------------+----------------+-----------+------------------+-------------------+-----------------+------------------------------------------------------------------------------------------------------------------------+------------+--------+-------+---------+--------------------------------+-------------+----------+----------+--------------+--------------+---------+-----------+-------------+
    |          12 |          1 |           2 | riud          | riud           |                |         1 |               32 | grenton_adminftp  | grenton         | $6$rounds=5000$ddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddd |         -1 | y      | web32 | client1 | /var/www/clients/client1/web32 |          -1 |       -1 |       -1 |           -1 |           -1 | NULL    | user      | NULL        |
    |          13 |          2 |           2 | riud          | riud           |                |         1 |                3 | grenton_testgrftp | grenton         | $6$rounds=5000$ddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddd |         -1 | y      | web3  | client1 | /var/www/clients/client1/web3  |          -1 |       -1 |       -1 |           -1 |           -1 | NULL    | user      | NULL        |
    +-------------+------------+-------------+---------------+----------------+----------------+-----------+------------------+-------------------+-----------------+------------------------------------------------------------------------------------------------------------------------+------------+--------+-------+---------+--------------------------------+-------------+----------+----------+--------------+--------------+---------+-----------+-------------+
    2 rows in set (0.00 sec)
    
    MariaDB [dbispconfig]>
    
     
  9. till

    till Super Moderator Staff Member ISPConfig Developer

    Yes, looks fine. I'm getting a bit out of ideas now :)
     
  10. brk

    brk New Member

    OK, I deleted this subdomain and created it from scratch - now it is working OK. I think it was related to problems with upgrade - I need to put two sql upgrade files from command line:
    Code:
    mysql -h localhost -u ispconfig dbispconfig -p < upd_0087.sql
    mysql -h localhost -u ispconfig dbispconfig -p < upd_0088.sql
    
    Now everything works OK :) - and I have very fresh pure-ftpd :p
     
    till likes this.
  11. till

    till Super Moderator Staff Member ISPConfig Developer

    Just to be sure, check that the mysql root password in /usr/local/ispconfig/server/lib/mysql_clientdb.conf is correct, as this is used for the upgrades as well.
     
  12. brk

    brk New Member

    It is OK (pasword for mysql root), and I need to figure out what happend during upgrade... I think I lost connection to server (ssh droped connection) during upgrade - I can't see any reason for this partially upgraded ispeconfig now.

    Till - thanks for supporting and ideas :) !
     
    biforme likes this.
  13. biforme

    biforme Member HowtoForge Supporter

    Hi Till, I have the same problem.
    The new passwords look something like this: $6$rounds=5000$25e7681bf9965fa4$yBRKOt3D2GXqni0SA9lgWhNVKRn1mPnQ
    The old accounts continue to work as it should.
    I can't do as brk because the domains, where there are problems, are in production.

    ##### SERVER #####
    [INFO] OS version is Ubuntu 18.04.4 LTS
    [INFO] ISPConfig is installed.
    ##### ISPCONFIG #####
    ISPConfig version is 3.1dev
     
  14. till

    till Super Moderator Staff Member ISPConfig Developer

    The new password is fine, nothing wrong with that. Its just a more recent encryption mechanism which is fully supported by Ubuntu 18.04.
     
  15. biforme

    biforme Member HowtoForge Supporter

    I can't log in with new accounts though, the password I enter is correct.
    I have an error: pure-ftpd: (? @ *****) [WARNING] Authentication failed for user [*******]
     
  16. till

    till Super Moderator Staff Member ISPConfig Developer

    The thread you posted to is about an issue with a failed upgrade and not about a different hash type, so unless you have a failed upgrade as well, then it might be better if you make a new thread about your problem. I have a Ubuntu 18.04 system here and the sha512 password hashes work fine there and the thread starter also confirmed that they work fine on his system, so it's very unlikely that your Ubuntu does not support recent hashes for passwords.
     
    biforme likes this.

Share This Page