mysql authentication in proftpd

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]



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@xxxxxxxxxxxx                    
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>
_______________________________________________
CentOS mailing list
CentOS@xxxxxxxxxx
http://lists.centos.org/mailman/listinfo/centos


[Index of Archives]     [CentOS]     [CentOS Announce]     [CentOS Development]     [CentOS ARM Devel]     [CentOS Docs]     [CentOS Virtualization]     [Carrier Grade Linux]     [Linux Media]     [Asterisk]     [DCCP]     [Netdev]     [Xorg]     [Linux USB]
  Powered by Linux