1

I have installed prftpd and proftpd-mysql rpm on a linux machine. Intention is to have usermanagement of ftpusers via web.

So I have installed proftpadmin from Here

Now when I am adding the below lines in /etc/proftpd.conf file

SQLConnectInfo                  proftpd@localhost root root
 SQLAuthenticate                 users   groups
 SQLAuthTypes                    Crypt   Backend
 SQLUserInfo                     users userid passwd uid gid homedir shell
 SQLGroupInfo                    groups groupid gid members

 SQLLog                          PASS logincount
 SQLNamedQuery                   logincount UPDATE "login_count=login_count+1 WHERE userid='%u'" users
 SQLLog                          PASS lastlogin
 SQLNamedQuery                   lastlogin UPDATE "last_login=now() WHERE userid='%u'" users
 SQLLog RETR                     dlbytescount
 SQLNamedQuery                   dlbytescount UPDATE "dl_bytes=dl_bytes+%b WHERE userid='%u'" users
 SQLLog RETR                     dlcount
 SQLNamedQuery                   dlcount UPDATE "dl_count=dl_count+1 WHERE userid='%u'" users
 SQLLog STOR                     ulbytescount
 SQLNamedQuery                   ulbytescount UPDATE "ul_bytes=ul_bytes+%b WHERE userid='%u'" users
 SQLLog STOR                     ulcount
 SQLNamedQuery                   ulcount UPDATE "ul_count=ul_count+1 WHERE userid='%u'" users
 SQLUserWhereClause              "disabled!=1"

service proftpd start fails .

If I remove those lines service starts, I cannot manage users via mysql, as above lines are mendatory.

Can anyone please suggest where I am going wrong

Please find my below /etc/proftpd.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                     root@localhost
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>

SQLConnectInfo                  proftpd@localhost root root
 SQLAuthenticate                 users   groups
 SQLAuthTypes                    Crypt   Backend
 SQLUserInfo                     users userid passwd uid gid homedir shell
 SQLGroupInfo                    groups groupid gid members

 SQLLog                          PASS logincount
 SQLNamedQuery                   logincount UPDATE "login_count=login_count+1 WHERE 

userid='%u'" users
 SQLLog                          PASS lastlogin
 SQLNamedQuery                   lastlogin UPDATE "last_login=now() WHERE userid='%u'" 

users
 SQLLog RETR                     dlbytescount
 SQLNamedQuery                   dlbytescount UPDATE "dl_bytes=dl_bytes+%b WHERE 

userid='%u'" users
 SQLLog RETR                     dlcount
 SQLNamedQuery                   dlcount UPDATE "dl_count=dl_count+1 WHERE userid='%u'" 

users
 SQLLog STOR                     ulbytescount
 SQLNamedQuery                   ulbytescount UPDATE "ul_bytes=ul_bytes+%b WHERE 

userid='%u'" users
 SQLLog STOR                     ulcount
 SQLNamedQuery                   ulcount UPDATE "ul_count=ul_count+1 WHERE userid='%u'" 

users
 SQLUserWhereClause              "disabled!=1"

SQL Script looks as below which is updated on respective Database

#
# Table structure for table `groups`
#

CREATE TABLE `groups` (
  `groupid` varchar(10) NOT NULL default '',
  `gid` int(10) unsigned NOT NULL auto_increment,
  `members` varchar(255) NOT NULL default '',
  PRIMARY KEY  (`gid`)
) TYPE=InnoDB ;

#
# Table structure for table `users`
#

CREATE TABLE `users` (
  `id` smallint(2) NOT NULL auto_increment,
  `userid` varchar(10) NOT NULL default '',
  `uid` int(10) unsigned NOT NULL default '',
  `gid` int(10) unsigned NOT NULL default '',
  `passwd` varchar(255) NOT NULL default '',
  `homedir` varchar(255) NOT NULL default '',
  `comment` varchar(255) NOT NULL default '',
  `disabled` int(10) unsigned NOT NULL default '0',
  `shell` varchar(20) NOT NULL default '/sbin/nologin',
  `email` varchar(255) NOT NULL default '',
  `name` varchar(255) NOT NULL default '',
  `ul_bytes` bigint(20) NOT NULL default '0',
  `dl_bytes` bigint(20) NOT NULL default '0',
  `login_count` bigint(20) NOT NULL default '0',
  `dl_count` bigint(20) NOT NULL default '0',
  `ul_count` bigint(20) NOT NULL default '0',
  `last_login` datetime default NULL,
  PRIMARY KEY  (`id`)
) TYPE=InnoDB ;
3
  • I have worked on this recently pleased add your sql script. i will look at it tomorrow. – Kiwy Mar 19 '14 at 21:10
  • You should try to get actual error messages from it—e.g., by checking syslog, or running the daemon from the console with a no-detach or debug option (been a long time since I set this up, forget what the option is). – derobert Mar 19 '14 at 21:28
  • I checked /var/log/messages /var/log/proftpd/ & syslog. I think somehow its not coming up with the sql configurations on proftpd.conf file – Ashish Mar 19 '14 at 21:36
1

Here's several problem I can already identify:

#AuthOrder does not mention mod_sql.c so it will never use mysql to identify your users.
AuthOrder   mod_sql.c mod_auth_pam.c mod_auth_unix.c

#this code shouldn't be commented in your config file and should look like this or you will never enable sql_mod
<IfModule mod_dso.c>
   LoadModule mod_sql.c
   LoadModule mod_sql_mysql.c
    #   LoadModule mod_sql_postgres.c
</IfModule>

#Mysql is not used without a port, you should verify this parameter (also the password is weak but lets keep that for another moment)
SQLConnectInfo                  proftpd@localhost:PORT root root

#are you sure to use the correct password encryption  (but that would be a problem to deal with later)
SQLAuthTypes  Crypt Backend

Also keep in mind that this virtual user needs to be bind to a real one that has the correct permission.
Finally if you need to debug the daemon, you can just add those 2 directives at the very top of your configuration file :

Trace DEFAULT:10
TraceLog /var/ftpd/trace.log
5
  • Thanks & upvote for you answer. I will try your suggested configuration & verify. I will accept answer since, I feel its helpful – Ashish Mar 20 '14 at 13:09
  • @Ashish hope this will help you. – Kiwy Mar 20 '14 at 13:13
  • Wonderfull, I was looking this for making a ftp accounts for college students where there can store their Projects, ebooks and Practical codes.. & solution is working fully fine.. I also added online FTP client which helps in online Editing of text files. – Ashish Mar 27 '14 at 10:37
  • @Ashish I'm glad it help you solved your issue – Kiwy Mar 27 '14 at 10:38
  • I made a php script that will fill users and paths in mysql, and respective path will be created on system if not present.. Tada.. everything is well. I am getting good apreciation from friends – Ashish Mar 27 '14 at 10:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.