pure-ftpd + mysql

Discussion in 'Installation/Configuration' started by cemmo, Aug 18, 2010.

  1. cemmo

    cemmo New Member

    Hello,
    I have been reading other post about similar situations but i have not found the solution yet. So, this is my question...
    I followed http://www.howtoforge.com/perfect-server-debian-lenny-ispconfig3 to configure a server... All seems to work well except ftp server, that doesn't connect to mysql to authenticate ftp users with the information of the ftp_user table.
    I have tried to reinstall pure-ftpd, but the problem remains.

    That is what I get...

    Aug 18 14:50:01 puntal pure-ftpd: (?@localhost.localdomain) [INFO] New connection from localhost.localdomain
    Aug 18 14:50:01 puntal pure-ftpd: (?@localhost.localdomain) [INFO] Logout.
    Aug 18 14:52:33 puntal pure-ftpd: (?@--------) [INFO] New connection from --------
    Aug 18 14:52:33 puntal pure-ftpd: (?@--------) [INFO] PAM_RHOST enabled. Getting the peer address
    Aug 18 14:52:36 puntal pure-ftpd: (?@--------) [WARNING] Authentication failed for user [lacasaftp]
    Aug 18 14:52:39 puntal pure-ftpd: (?@--------) [INFO] Logout.
    Aug 18 14:55:01 puntal pure-ftpd: (?@localhost.localdomain) [INFO] New connection from localhost.localdomain
    Aug 18 14:55:01 puntal pure-ftpd: (?@localhost.localdomain) [INFO] Logout.

    I have tried also with 127.0.0.1 and localhost in mysql.conf, and all the data seems to be OK. I have used it to log in mysql and it works.
    ##############################################
    # #
    # Sample Pure-FTPd Mysql configuration file. #
    # See README.MySQL for explanations. #
    # #
    ##############################################


    # Optional : MySQL server name or IP. Don't define this for unix sockets.

    MYSQLServer 127.0.0.1


    # Optional : MySQL port. Don't define this if a local unix socket is used.

    # MYSQLPort 3306


    # Optional : define the location of mysql.sock if the server runs on this host.

    # MYSQLSocket /var/run/mysqld/mysqld.sock


    # Mandatory : user to bind the server as.

    MYSQLUser ispconfig


    # Mandatory : user password. You must have a password.

    MYSQLPassword --


    # Mandatory : database to open.

    MYSQLDatabase dbispconfig


    # Mandatory : how passwords are stored
    # Valid values are : "cleartext", "crypt", "md5" and "password"
    # ("password" = MySQL password() function)
    # You can also use "any" to try "crypt", "md5" *and* "password"

    MYSQLCrypt crypt


    # In the following directives, parts of the strings are replaced at
    # run-time before performing queries :
    #
    # \L is replaced by the login of the user trying to authenticate.
    # \I is replaced by the IP address the user connected to.
    # \P is replaced by the port number the user connected to.
    # \R is replaced by the IP address the user connected from.
    # \D is replaced by the remote IP address, as a long decimal number.
    #
    # Very complex queries can be performed using these substitution strings,
    # especially for virtual hosting.


    # Query to execute in order to fetch the password

    MYSQLGetPW SELECT password FROM ftp_user WHERE active = 'y' AND server_id = '1' AND username="\L"


    # Query to execute in order to fetch the system user name or uid

    MYSQLGetUID SELECT uid FROM ftp_user WHERE active = 'y' AND server_id = '1' AND username="\L"


    # Optional : default UID - if set this overrides MYSQLGetUID

    #MYSQLDefaultUID 1000


    # Query to execute in order to fetch the system user group or gid

    MYSQLGetGID SELECT gid FROM ftp_user WHERE active = 'y' AND server_id = '1' AND username="\L"


    # Optional : default GID - if set this overrides MYSQLGetGID

    #MYSQLDefaultGID 1000


    # Query to execute in order to fetch the home directory

    MYSQLGetDir SELECT dir FROM ftp_user WHERE active = 'y' AND server_id = '1' AND username="\L"


    # Optional : query to get the maximal number of files
    # Pure-FTPd must have been compiled with virtual quotas support.

    MySQLGetQTAFS SELECT quota_files FROM ftp_user WHERE active = 'y' AND server_id = '1' AND quota_files != '-1' AND username="\L"


    # Optional : query to get the maximal disk usage (virtual quotas)
    # The number should be in Megabytes.
    # Pure-FTPd must have been compiled with virtual quotas support.

    MySQLGetQTASZ SELECT quota_size FROM ftp_user WHERE active = 'y' AND server_id = '1' AND quota_size != '-1' AND username="\L"


    # Optional : ratios. The server has to be compiled with ratio support.

    MySQLGetRatioUL SELECT ul_ratio FROM ftp_user WHERE active = 'y' AND server_id = '1' AND ul_ratio != '-1' AND username="\L"
    MySQLGetRatioDL SELECT dl_ratio FROM ftp_user WHERE active = 'y' AND server_id = '1' AND dl_ratio != '-1' AND username="\L"


    # Optional : bandwidth throttling.
    # The server has to be compiled with throttling support.
    # Values are in KB/s .

    MySQLGetBandwidthUL SELECT ul_bandwidth FROM ftp_user WHERE active = 'y' AND server_id = '1' AND ul_bandwidth != '-1' AND username="\L"
    MySQLGetBandwidthDL SELECT dl_bandwidth FROM ftp_user WHERE active = 'y' AND server_id = '1' AND dl_bandwidth != '-1' AND username="\L"

    # Enable ~ expansion. NEVER ENABLE THIS BLINDLY UNLESS :
    # 1) You know what you are doing.
    # 2) Real and virtual users match.

    # MySQLForceTildeExpansion 1


    # If you upgraded your tables to transactionnal tables (Gemini,
    # BerkeleyDB, Innobase...), you can enable SQL transactions to
    # avoid races. Leave this commented if you are using the
    # traditionnal MyIsam databases or old (< 3.23.x) MySQL versions.

    MySQLTransactions On


    This is the auth.log

    Aug 18 14:52:01 puntal CRON[18867]: pam_unix(cron:session): session opened for user root by (uid=0)
    Aug 18 14:52:01 puntal CRON[18867]: pam_unix(cron:session): session closed for user root
    Aug 18 14:52:34 puntal pure-ftpd: pam_unix_auth(pure-ftpd:auth): check pass; user unknown
    Aug 18 14:52:34 puntal pure-ftpd: pam_unix_auth(pure-ftpd:auth): authentication failure; logname= uid=0 euid=0 tty=pure-ftpd ruser=lacasaftp rhost=151.Red-80-38-180.staticIP.rima-tde.net
    Aug 18 14:53:01 puntal CRON[18875]: pam_unix(cron:session): session opened for user root by (uid=0)
    Aug 18 14:53:01 puntal CRON[18875]: pam_unix(cron:session): session closed for user root


    And I have allowed mysql log, and it doesn't recive any query.



    Well... that's all, I don't know what i can do!
    Thank you in advance!
     
  2. falko

    falko Super Moderator ISPConfig Developer

    Did you try both active and passive transfers in your FTP client?

    Is this a physical server or a virtual machine?
     
  3. cemmo

    cemmo New Member

    Yes, I tried both and it doesn't work with the users created in ISPConfig. But it works with the OS users (except with root).

    It is a physical server.

    Thank you!
     
  4. falko

    falko Super Moderator ISPConfig Developer

    Can you log in on the shell using
    Code:
    mysql -h 127.0.0.1 -u ispconfig -p
    (using the same MySQL password as in your pureftpd configuration)?
     
  5. cemmo

    cemmo New Member

    Yes, in this way, I can log in.
     
  6. falko

    falko Super Moderator ISPConfig Developer

    Hm... Just a guess, but does your MySQL password contain any special characters?
     
  7. cemmo

    cemmo New Member

    No, only hexadecimal characters.
     

Share This Page