[CentOS] mysql authentication in proftpd

Hello list,

I was able to get passive mode worked out. I'm really glad I was able to do this. I'm able to log into the ftp server, list directories, enter subdirectories and upload/download files. However my next task is to enable virtual users using mysql. I have installed proftpd-mysql and enabled the sql modules in the config.

I found a good article on how to do this here:

http://www.khoosys.net/single.htm?ipg=848

I set everything up according to this article, and authentication with the test user I have stored in the user table is failing.

[root@LCENT05:~] #/usr/bin/ftp -d mydomain.net Connected to snjh.net (xx.xx.xx.xx). 220 FTP Server ready. Name (snjh.net:root): jfuser ---> USER jfuser 331 Password required for jfuser Password: ---> PASS XXXX 530 Login incorrect. Login failed. ---> SYST 215 UNIX Type: L8 Remote system type is UNIX. Using binary mode to transfer files.

I setup a debug log session and this is what I've found:

192.168.1.30 (189.15.88.64[189.15.88.64]) - dispatching PRE_CMD command 'USER jfuser' to mod_tls 192.168.1.30 (189.15.88.64[189.15.88.64]) - dispatching PRE_CMD command 'USER jfuser' to mod_core 192.168.1.30 (189.15.88.64[189.15.88.64]) - dispatching PRE_CMD command 'USER jfuser' to mod_core 192.168.1.30 (189.15.88.64[189.15.88.64]) - dispatching PRE_CMD command 'USER jfuser' to mod_delay 192.168.1.30 (189.15.88.64[189.15.88.64]) - dispatching PRE_CMD command 'USER jfuser' to mod_auth 192.168.1.30 (189.15.88.64[189.15.88.64]) - dispatching CMD command 'USER jfuser' to mod_auth 192.168.1.30 (189.15.88.64[189.15.88.64]) - dispatching POST_CMD command 'USER jfuser' to mod_sql 192.168.1.30 (189.15.88.64[189.15.88.64]) - dispatching POST_CMD command 'USER jfuser' to mod_delay 192.168.1.30 (189.15.88.64[189.15.88.64]) - dispatching LOG_CMD command 'USER jfuser' to mod_sql 192.168.1.30 (189.15.88.64[189.15.88.64]) - dispatching LOG_CMD command 'USER jfuser' to mod_log 192.168.1.30 (189.15.88.64[189.15.88.64]) - dispatching PRE_CMD command 'PASS (hidden)' to mod_tls 192.168.1.30 (189.15.88.64[189.15.88.64]) - dispatching PRE_CMD command 'PASS (hidden)' to mod_core 192.168.1.30 (189.15.88.64[189.15.88.64]) - dispatching PRE_CMD command 'PASS (hidden)' to mod_core 192.168.1.30 (189.15.88.64[189.15.88.64]) - dispatching PRE_CMD command 'PASS (hidden)' to mod_sql_passwd 192.168.1.30 (189.15.88.64[189.15.88.64]) - dispatching PRE_CMD command 'PASS (hidden)' to mod_sql 192.168.1.30 (189.15.88.64[189.15.88.64]) - dispatching PRE_CMD command 'PASS (hidden)' to mod_vroot 192.168.1.30 (189.15.88.64[189.15.88.64]) - mod_vroot/0.8.5: vroot registered 192.168.1.30 (189.15.88.64[189.15.88.64]) - dispatching PRE_CMD command 'PASS (hidden)' to mod_delay 192.168.1.30 (189.15.88.64[189.15.88.64]) - dispatching PRE_CMD command 'PASS (hidden)' to mod_auth 192.168.1.30 (189.15.88.64[189.15.88.64]) - dispatching CMD command 'PASS (hidden)' to mod_auth 192.168.1.30 (189.15.88.64[189.15.88.64]) - USER jfuser (Login failed): No such user found.

It looks like authentication is bypassing mod_sql altogether and selecting mod_auth instead. The only authentication method I have enabled in the config is mod_sql so I'm not sure why this is occuring.

Here is the authentication section of my config:

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

This is my sql login section:

# SQL login SQLConnectInfo ftpdb@db1 proftpd secret

Which I have verified does work from the ftp server:

[root@VIRTCENT08:~] #mysql -uproftpd -psecret -h db1 Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3354 Server version: 5.5.15-log MySQL Community Server (GPL) by Remi

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> use ftpdb Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A

Database changed mysql>

I was hoping I could ask some advice as to why this doesn't work in it's present form.

Here's the full config. Thanks in advance!

# This is the ProFTPD configuration file # # See: http://www.proftpd.org/docs/directives/linked/by-name.html

# Server Config - config used for anything outside a <VirtualHost> or <Global> context # See: http://www.proftpd.org/docs/howto/Vhost.html

ServerName "ProFTPD server" ServerIdent on "FTP Server ready." ServerAdmin root@localhost DefaultServer on

# Cause every FTP user except adm to be chrooted into their home directory # Aliasing /etc/security/pam_env.conf into the chroot allows pam_env to # work at session-end time (http://bugzilla.redhat.com/477120) VRootEngine on DefaultRoot /var/www/html/jokefire.com VRootAlias etc/security/pam_env.conf /etc/security/pam_env.conf

# Use pam to authenticate (default) and be authoritative #AuthPAMConfig proftpd #AuthOrder mod_auth_pam.c* mod_auth_unix.c AuthOrder mod_sql.c # If you use NIS/YP/LDAP you may need to disable PersistentPasswd #PersistentPasswd off

# Don't do reverse DNS lookups (hangs on DNS problems) UseReverseDNS off

# Set the user and group that the server runs as User nobody Group nobody

# Specify a scoreboard file ScoreboardFile /var/proftpd/proftpd.scoreboard

AllowStoreRestart on AllowRetrieveRestart on RequireValidShell off PathDenyFilter "\.ftp)|\.ht)[a-z]+$" AllowStoreRestart on AllowRetrieveRestart on RequireValidShell off PathDenyFilter "\.ftp)|\.ht)[a-z]+$" DenyFilter *.*/

# To prevent DoS attacks, set the maximum number of child processes # to 20. If you need to allow more than 20 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

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

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

# Define log-files to use TransferLog /var/log/proftpd/proftpd.xferlog ExtendedLog /var/log/proftpd/proftpd.access_log WRITE,READ write ExtendedLog /var/log/proftpd/proftpd.auth_log AUTH auth ExtendedLog /var/log/proftpd/proftpd.paranoid_log ALL default #SQLLogFile /var/log/proftpd/proftpd.mysql

# Dynamic Shared Object (DSO) loading # See README.DSO and howto/DSO.html for more details # # General database support (http://www.proftpd.org/docs/contrib/mod_sql.html) LoadModule mod_sql.c # # Support for base-64 or hex encoded MD5 and SHA1 passwords from SQL tables # (contrib/mod_sql_passwd.html) LoadModule mod_sql_passwd.c # # Mysql support (requires proftpd-mysql package) # (http://www.proftpd.org/docs/contrib/mod_sql.html) LoadModule mod_sql_mysql.c # # Postgresql support (requires proftpd-postgresql package) # (http://www.proftpd.org/docs/contrib/mod_sql.html) # LoadModule mod_sql_postgres.c # # Quota support (http://www.proftpd.org/docs/contrib/mod_quotatab.html) LoadModule mod_quotatab.c # # File-specific "driver" for storing quota table information in files # (http://www.proftpd.org/docs/contrib/mod_quotatab_file.html) # LoadModule mod_quotatab_file.c # # SQL database "driver" for storing quota table information in SQL tables # (http://www.proftpd.org/docs/contrib/mod_quotatab_sql.html) # LoadModule mod_quotatab_sql.c # # LDAP support (requires proftpd-ldap package) # (http://www.proftpd.org/docs/directives/linked/config_ref_mod_ldap.html) # LoadModule mod_ldap.c # # LDAP quota support (requires proftpd-ldap package) # (http://www.proftpd.org/docs/contrib/mod_quotatab_ldap.html) # LoadModule mod_quotatab_ldap.c # # Support for authenticating users using the RADIUS protocol # (http://www.proftpd.org/docs/contrib/mod_radius.html) # LoadModule mod_radius.c # # Retrieve quota limit table information from a RADIUS server # (http://www.proftpd.org/docs/contrib/mod_quotatab_radius.html) # LoadModule mod_quotatab_radius.c # # Administrative control actions for the ftpdctl program # (http://www.proftpd.org/docs/contrib/mod_ctrls_admin.html) # LoadModule mod_ctrls_admin.c # # Execute external programs or scripts at various points in the process # of handling FTP commands # (http://www.castaglia.org/proftpd/modules/mod_exec.html) # LoadModule mod_exec.c # # Support for POSIX ACLs # (http://www.proftpd.org/docs/modules/mod_facl.html) # LoadModule mod_facl.c # # Support for using the GeoIP library to look up geographical information on # the connecting client and using that to set access controls for the server # (http://www.castaglia.org/proftpd/modules/mod_geoip.html) # LoadModule mod_geoip.c # # Configure server availability based on system load # (http://www.proftpd.org/docs/contrib/mod_load.html) # LoadModule mod_load.c # # Limit downloads to a multiple of upload volume (see README.ratio) # LoadModule mod_ratio.c # # Rewrite FTP commands sent by clients on-the-fly, # using regular expression matching and substitution # (http://www.proftpd.org/docs/contrib/mod_rewrite.html) # LoadModule mod_rewrite.c # # Support for the SSH2, SFTP, and SCP protocols, for secure file transfer over # an SSH2 connection (http://www.castaglia.org/proftpd/modules/mod_sftp.html) # LoadModule mod_sftp.c # # Use PAM to provide a 'keyboard-interactive' SSH2 authentication method for # mod_sftp (http://www.castaglia.org/proftpd/modules/mod_sftp_pam.html) # LoadModule mod_sftp_pam.c # # Use SQL (via mod_sql) for looking up authorized SSH2 public keys for user # and host based authentication # (http://www.castaglia.org/proftpd/modules/mod_sftp_sql.html) # LoadModule mod_sftp_sql.c # # Provide data transfer rate "shaping" across the entire server # (http://www.castaglia.org/proftpd/modules/mod_shaper.html) # LoadModule mod_shaper.c # # Support for miscellaneous SITE commands such as SITE MKDIR, SITE SYMLINK, # and SITE UTIME (http://www.proftpd.org/docs/contrib/mod_site_misc.html) # LoadModule mod_site_misc.c # # Provide an external SSL session cache using shared memory # (contrib/mod_tls_shmcache.html) # LoadModule mod_tls_shmcache.c # # Use the /etc/hosts.allow and /etc/hosts.deny files, or other allow/deny # files, for IP-based access control # (http://www.proftpd.org/docs/contrib/mod_wrap.html) # LoadModule mod_wrap.c # # Use the /etc/hosts.allow and /etc/hosts.deny files, or other allow/deny # files, as well as SQL-based access rules, for IP-based access control # (http://www.proftpd.org/docs/contrib/mod_wrap2.html) # LoadModule mod_wrap2.c # # Support module for mod_wrap2 that handles access rules stored in specially # formatted files on disk # (http://www.proftpd.org/docs/contrib/mod_wrap2_file.html) # LoadModule mod_wrap2_file.c # # Support module for mod_wrap2 that handles access rules stored in SQL # database tables (http://www.proftpd.org/docs/contrib/mod_wrap2_sql.html) # LoadModule mod_wrap2_sql.c # # Provide a flexible way of specifying that certain configuration directives # only apply to certain sessions, based on credentials such as connection # class, user, or group membership # (http://www.proftpd.org/docs/contrib/mod_ifsession.html) # LoadModule mod_ifsession.c

# Define a SQL log SQLLogFile /var/log/proftpd/proftpd.mysql

# SQL Auth type SQLAuthTypes Backend

# SQL login SQLConnectInfo ftpdb@db1 proftpd secret

# 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

# TLS (http://www.castaglia.org/proftpd/modules/mod_tls.html) <IfDefine TLS> 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 <IfModule mod_tls_shmcache.c> TLSSessionCache shm:/file=/var/run/proftpd/sesscache </IfModule> </IfDefine>

# Dynamic ban lists (http://www.proftpd.org/docs/contrib/mod_ban.html) # Enable this with PROFTPD_OPTIONS=-DDYNAMIC_BAN_LISTS in /etc/sysconfig/proftpd <IfDefine DYNAMIC_BAN_LISTS> LoadModule mod_ban.c BanEngine on BanLog /var/log/proftpd/ban.log BanTable /var/run/proftpd/ban.tab

# If the same client reaches the MaxLoginAttempts limit 2 times # within 10 minutes, automatically add a ban for that client that # will expire after one hour. BanOnEvent MaxLoginAttempts 2/00:10:00 01:00:00

# Allow the FTP admin to manually add/remove bans BanControlsACLs all allow user ftpadm </IfDefine>

# Global Config - config common to Server Config and all virtual hosts # See: http://www.proftpd.org/docs/howto/Vhost.html <Global>

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

# Allow users to overwrite files and change permissions AllowOverwrite yes <Limit ALL SITE_CHMOD> AllowAll </Limit>

</Global>

# A basic anonymous configuration, with an upload directory # Enable this with PROFTPD_OPTIONS=-DANONYMOUS_FTP in /etc/sysconfig/proftpd <IfDefine ANONYMOUS_FTP> <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 DisplayChdir .message DisplayReadme README*

# Cosmetic option to make all files appear to be owned by user "ftp" 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> </IfDefine>

<IfModule mod_facts.c> FactsAdvertise off </IfModule>

# ---------------------------------------------------- # ftp.snjh.net # external server # <VirtualHost snjh.net> ServerAdmin bluethundr@ftp-data ServerName "JF FTP Server" TransferLog /var/log/proftpd/jokefire.com MaxLoginAttempts 3 RequireValidShell no DefaultRoot /var/www/html/jokefire.com User nobody Group nobody AllowOverwrite yes

<Directory /var/www/html/jokefire.com> <Limit ALL> AllowUser bluethundr AllowUser rerekson AllowUser jfuser DenyAll </Limit> </Directory>

</VirtualHost> # # ---------------------------------------------------- # ftp.example.net # internalftp server # <VirtualHost 192.168.1.30> ServerAdmin bluethundr@jokefire.com ServerName "JF FTP Server" TransferLog /var/log/proftpd/ftp.snjh.net MaxLoginAttempts 3 RequireValidShell no DefaultRoot /var/www/html/jokefire.com User nobody Group nobody AllowOverwrite yes MasqueradeAddress xx.xx.xx.xx PassivePorts 60000 65535

<Directory /var/www/html/jokefire.com> <Limit ALL> AllowUser bluethundr AllowUser rerekson AllowUser jfuser DenyAll </Limit> </Directory> </VirtualHost>