Proftp Problem - Proftp, MySQL /quota HOWTO

Discussion in 'Server Operation' started by wcr3d, Dec 1, 2006.

  1. wcr3d

    wcr3d New Member

    Hello,
    Here is a link to a previous thread that I am having the same problem with.
    http://www.howtoforge.com/forums/showthread.php?t=7350&highlight=proftpd mysql
    The differences are....I am using Mandriva 2007 and the only change in my proftp.conf is the lines from the HOWTO that needed to be added. The service doesn't show its starting and shows FAILED when doing a restart or shutdown. If you try to connect to the server, I have only tried using command line, it shows "Failed to connect".
    I would appreciate any assistance. Plus, fairly new to linux so if you need a log file output, please tell me what one and how to do it.
    Thanks
     
  2. till

    till Super Moderator Staff Member ISPConfig Developer

    Please have a look at your logfiles (syslog and xferlog) for errors.
     
  3. QuikSoft

    QuikSoft New Member

    Same problem as you.
    Installed Proftp on Fedora Cora 5 as shown in the how to.
    After editing the conf file proft deamon failed to start.
    Can't find any log entry's
    Need some help please....

    This is my conf file
    ----
    # This is the ProFTPD configuration file
    # $Id: proftpd.conf,v 1.1 2004/02/26 17:54:30 thias Exp $

    ServerName "ProFTPD server"
    ServerIdent on "FTP Server ready."
    ServerAdmin [email protected]
    ServerType standalone
    #ServerType inetd
    DefaultServer on
    AccessGrantMsg "User %u logged in."
    #DisplayConnect /etc/ftpissue
    #DisplayLogin /etc/ftpmotd
    #DisplayGoAway /etc/ftpgoaway
    DeferWelcome off

    # Use this to excude users from the chroot
    DefaultRoot ~ !adm

    # Use pam to authenticate (default) and be authoritative
    AuthPAMConfig proftpd
    AuthOrder mod_auth_pam.c* mod_auth_unix.c

    # Do not perform ident nor DNS lookups (hangs when the port is filtered)
    IdentLookups off
    UseReverseDNS off

    # Port 21 is the standard FTP port.
    Port 21

    # Umask 022 is a good standard umask to prevent new dirs and files
    # from being group and world writable.
    Umask 022

    # Default to show dot files in directory listings
    ListOptions "-a"

    # See Configuration.html for these (here are the default values)
    #MultilineRFC2228 off
    #RootLogin off
    #LoginPasswordPrompt on
    #MaxLoginAttempts 3
    #MaxClientsPerHost none
    #AllowForeignAddress off # For FXP

    # Allow to resume not only the downloads but the uploads too
    AllowRetrieveRestart on
    AllowStoreRestart on

    # To prevent DoS attacks, set the maximum number of child processes
    # to 30. If you need to allow more than 30 concurrent connections
    # at once, simply increase this value. Note that this ONLY works
    # in standalone mode, in inetd mode you should use an inetd server
    # that allows you to limit maximum number of processes per service
    # (such as xinetd)
    MaxInstances 20

    # Set the user and group that the server normally runs at.
    User nobody
    Group nobody

    # Disable sendfile by default since it breaks displaying the download speeds in
    # ftptop and ftpwho
    UseSendfile no

    # This is where we want to put the pid file
    ScoreboardFile /var/run/proftpd.score

    # Normally, we want users to do a few things.
    <Global>
    AllowOverwrite yes
    <Limit ALL SITE_CHMOD>
    AllowAll
    </Limit>
    </Global>

    # Define the log formats
    LogFormat default "%h %l %u %t \"%r\" %s %b"
    LogFormat auth "%v [%P] %h %t \"%r\" %s"

    # TLS
    # Explained at http://www.castaglia.org/proftpd/modules/mod_tls.html
    #TLSEngine on
    #TLSRequired on
    #TLSRSACertificateFile /etc/pki/tls/certs/proftpd.pem
    #TLSRSACertificateKeyFile /etc/pki/tls/certs/proftpd.pem
    #TLSCipherSuite ALL:!ADH:!DES
    #TLSOptions NoCertRequest
    #TLSVerifyClient off
    ##TLSRenegotiate ctrl 3600 data 512000 required off timeout 300
    #TLSLog /var/log/proftpd/tls.log

    # SQL authentication Dynamic Shared Object (DSO) loading
    # See README.DSO and howto/DSO.html for more details.
    #<IfModule mod_dso.c>
    # LoadModule mod_sql.c
    # LoadModule mod_sql_mysql.c
    # LoadModule mod_sql_postgres.c
    #</IfModule>

    # A basic anonymous configuration, with an upload directory.
    #<Anonymous ~ftp>
    # User ftp
    # Group ftp
    # AccessGrantMsg "Anonymous login ok, restrictions apply."
    #
    # # We want clients to be able to login with "anonymous" as well as "ftp"
    # UserAlias anonymous ftp
    #
    # # Limit the maximum number of anonymous logins
    # MaxClients 10 "Sorry, max %m users -- try again later"
    #
    # # Put the user into /pub right after login
    # #DefaultChdir /pub
    #
    # # We want 'welcome.msg' displayed at login, '.message' displayed in
    # # each newly chdired directory and tell users to read README* files.
    # DisplayLogin /welcome.msg
    # DisplayFirstChdir .message
    # DisplayReadme README*
    #
    # # Some more cosmetic and not vital stuff
    # DirFakeUser on ftp
    # DirFakeGroup on ftp
    #
    # # Limit WRITE everywhere in the anonymous chroot
    # <Limit WRITE SITE_CHMOD>
    # DenyAll
    # </Limit>
    #
    # # An upload directory that allows storing files but not retrieving
    # # or creating directories.
    # <Directory uploads/*>
    # AllowOverwrite no
    # <Limit READ>
    # DenyAll
    # </Limit>
    #
    # <Limit STOR>
    # AllowAll
    # </Limit>
    # </Directory>
    #
    # # Don't write anonymous accesses to the system wtmp file (good idea!)
    # WtmpLog off
    #
    # # Logging for the anonymous transfers
    # ExtendedLog /var/log/proftpd/access.log WRITE,READ default
    # ExtendedLog /var/log/proftpd/auth.log AUTH auth
    #
    #</Anonymous>

    ####CUSTOM####
    DefaultRoot ~


    # The passwords in MySQL are encrypted using CRYPT
    SQLAuthTypes Plaintext Crypt
    SQLAuthenticate users* groups*


    # used to connect to the database
    # [email protected] database_user user_password
    SQLConnectInfo [email protected] proftpd MYPASSWORDHERE


    # Here we tell ProFTPd the names of the database columns in the "usertable"
    # we want it to interact with. Match the names with those in the db
    SQLUserInfo ftpuser userid passwd uid gid homedir shell

    # Here we tell ProFTPd the names of the database columns in the "grouptable"
    # we want it to interact with. Again the names match with those in the db
    SQLGroupInfo ftpgroup groupname gid members

    # set min UID and GID - otherwise these are 999 each
    SQLMinID 500

    # create a user's home directory on demand if it doesn't exist
    SQLHomedirOnDemand 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

    RootLogin off
    RequireValidShell off

    SQLNamedQuery gettally SELECT "ROUND((bytes_in_used/1048576),2) FROM ftpquotatallies WHERE name='%u'"
    SQLNamedQuery getlimit SELECT "ROUND((bytes_in_avail/1048576),2) FROM ftpquotalimits WHERE name='%u'"
    SQLNamedQuery getfree SELECT "ROUND(((ftpquotalimits.bytes_in_avail-ftpquotatallies.bytes_in_used)/1048576),2) FROM ftpquotalimits,ftpquotatallies WHERE ftpquotalimits.name = '%u' AND ftpquotatallies.name = '%u'"

    SQLShowInfo LIST "226" "Used %{gettally}MB from %{getlimit}MB. You have %{getfree}MB available space."

    ####END####

    -----
     
    Last edited: Dec 4, 2006
  4. falko

    falko Super Moderator ISPConfig Developer

    The tutorial is for Debian Sarge, so it most likely doesn't work on Fedora.
    Did you install a proftpd-mysql package (not sure how it is named on Fedora)?

    Do you get any errors when you start ProFTPd?
     
  5. QuikSoft

    QuikSoft New Member

    I installed the proftpd-mysql with the command " yum install proftpd-mysql "
    And yum installed proftpd-mysql.
    I only get the error " [[email protected] config]# service proftpd start
    Starting proftpd: [FAILED] "
    No idea where the logging takes place of proftp.
    The proftp in the Var/Log/Proftp dir stay's empty.
    When i remove the custom lines in the conf file.
    Proftp starts fine.
    Any suggestions?

    Thanks
     
  6. QuikSoft

    QuikSoft New Member

    Found something in the syslog, maybe it helps.
    Dec 5 22:18:56 mx2 proftpd[22773]: mx2.quiksoft.nl - error setting IPV6_V6ONLY: Protocol not available
    Dec 5 22:18:56 mx2 proftpd[22773]: mx2.quiksoft.nl - ProFTPD 1.3.0a (stable) (built Wed Nov 29 07:55:07 EST 2006) standalone mode STARTUP
    Dec 5 22:19:00 mx2 proftpd[22773]: mx2.quiksoft.nl - ProFTPD killed (signal 15)
    Dec 5 22:19:00 mx2 proftpd[22773]: mx2.quiksoft.nl - ProFTPD 1.3.0a standalone mode SHUTDOWN
     
  7. falko

    falko Super Moderator ISPConfig Developer

    What's the output of
    Code:
    /etc/init.d/proftpd stop
    /etc/init.d/proftpd start
    ? What's the output of
    Code:
    netstat -tap
    afterwards?
     
  8. wcr3d

    wcr3d New Member

    I got it fixed. Seems that even though when you ask for proftpd-mysql it doesn't support mysql. I went into package manager and upgraded adding in proftpd_sql_mysql, then it would work.
    HTH and thank you all for your help.
     

Share This Page