Re: [Proftpd-user] SQLUserInfo using custom:/

[Who K. <qui...@me...>]

TJ Saunders wrote:
> OK.  Getting warmer now. =)  Next thing: would it be possible to see all
> of your mod_sql configuration directives (barring sensitive info, of
> course)?
> 
> TJ
> 
# This is the ProFTPD configuration file
# $Id: proftpd.conf,v 1.6 2003/09/24 10:51:11 dude Exp $

ServerName			"ProFTPD server"
ServerIdent			on "FTP Server ready."
ServerAdmin			web...@me...
#Note: when going thru this to remove secret info before posting
# i noticed that the next line should have been commented out
# but I don't think it affects my issue i will be running
# under inetd
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 by default
AuthPAMAuthoritative		off

# 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

# 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>

   # Specify our connection information.  Both mod_sql_mysql and
   # mod_sql_postgres use the same format, other backends may specify a
   # different format for the first argument to SQLConnectInfo.  By not
   # specifying a fourth argument, we're defaulting to 'PERSESSION'
   # connections -- a connection is made to the database at the start of
   # the session and closed at the end.  This should be fine for most
   # situations.
   SQLConnectInfo dbname@localhost:port dbuser secret

   # Specify our authentication schemes.  Assuming we're using
   # mod_sql_mysql, here we're saying 'first try to authenticate using
   # mysql's password scheme, then try to authenticate the user's
   # password as plaintext'.  Note that 'Plaintext' isn't a smart way to
   # store passwords unless you've got your database well secured.
   # THIS DIRECTIVE IS REQUIRED FOR MOD_SQL TO WORK.
   SQLAuthTypes Crypt

   # Specify the table and fields for user information.  If you've
   # created the database as it specifies in 'README.mod_sql', you don't
   # need to have this directive at all UNLESS you've elected not to
   # create some fields.  In this case we're telling mod_sql to look in
   # table 'users' for the fields 'username','password','uid', and
   # 'gid'.  The 'homedir' and 'shell' fields are specified as 'NULL' --
   # this will be explained below.

   #Note: I have been flip-flopping between the next two lines and
   # the third line for testing. Using the next two lines works
   # but forces me to have the full home path in my database
   #SQLUserInfo v_members username cryptpw uid_ftp gid_ftp home_ftp NULL
   #SQLUserWhereClause   "id >= 500 AND active = 'yes'"
   SQLUserInfo custom:/getuserinfo

   # NOTE: This query works fine, but the custom:/ isn't working yet
   SQLNamedQuery getuserinfo FREEFORM "SELECT vm.username, vm.cryptpw, 
vm.uid_ftp, vm.gid_ftp, CONCAT(vc.value, '/', vm.home_ftp), '' FROM 
v_members vm, v_config vc WHERE vm.username = '%U' AND vm.id >= 500 AND 
vm.active = 'Yes' AND vc.key = 'WEBROOT'"


   SQLLogFile /var/log/proftpd/sql.log

   # Here we tell mod_sql that every user it authenticates should have
   # the same home directory.  A much more common option would be to
   # specify a homedir in the database and leave this directive out. Note
   # that this directive is necessary in this case because we specified
   # the homedir field as 'NULL', above.  mod_sql needs to get homedir
   # information from *somewhere*, otherwise it will not allow access.
   # SQLDefaultHomedir "/tmp"

   # This is not a mod_sql specific directive, but it's here because of
   # the way we specified 'SQLUserInfo', above.  By setting this to
   # 'off', we're telling ProFTPD to allow users to connect even if we
   # have no (or bad) shell information for them.  Since we specified the
   # shell field as 'NULL', above, we need to tell ProFTPD to allow the
   # users in even though their shell doesn't exist.
   RequireValidShell off

   # Here we tell mod_sql how to get out group information.  By leaving
   # this commented out, we're telling mod_sql to go ahead and use the
   # defaults for the tablename and all the field names.
   SQLGroupInfo v_groups groupname gid members

   # For small sites, the following directive will speed up queries at
   # the cost of some memory.  Larger sites should read the complete
   # description of the 'SQLAuthenticate' directive; there are options
   # here that control the use of potentially expensive database
   # queries. NOTE: these arguments to 'SQLAuthoritative' limit the way
   # you can structure your group table.  Check the README for more
   # information.
   SQLAuthenticate users groups usersetfast groupsetfast

   # Finally, some example logging directives.  If you have an integer
   # field named 'count' in your users table, these directives will
   # automatically update the field each time a user logs in and display
   # their current login count to them.
   # SQLNamedQuery getcount SELECT "count, userid from users where 
userid='%u'"
   # SQLNamedQuery updatecount INSERT "count=count+1 WHERE userid='%u'"
   # SQLShowInfo PASS "230" "You've logged on %{getcount} times, %u"
   # SQLLog PASS updatecount

   # NOTE: removing the vu.service = 3 part of the where below
   # will count ALL tracked logins not just ftp logins fwitw
   SQLNamedQuery getcount SELECT "COUNT(*) FROM v_usage vu, v_members vm 
WHERE vm.username = '%u' AND vu.service = 3 AND vu.mid = vm.id"
   SQLNamedQuery usagelog FREEFORM "INSERT INTO v_usage(mid, ip, 
service) SELECT v_members.id, '%a', 3 FROM v_members WHERE 
v_members.username = '%u';"
   SQLLog PASS usagelog
   SQLShowInfo PASS "230" "You've logged on %{getcount} times, %u"

	
</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		/usr/share/ssl/certs/proftpd.pem
#TLSRSACertificateKeyFile	/usr/share/ssl/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