There are some errors in the mod_sql
This is correct when running SQLUserInfo ftpuser username passwd uid gid homedir shell SQLUserWhereClause "Active = 1 and disabled = 0 and username<>'-' and (failmax = 0 or failcount < failmax) and (loginafter is null or (loginafter < now() and faillock = 1) or faillock = 0) and (login> This is not correct when running When i use "ftpuser" in the statements the reference in the sql.log is users also for groups! When i change it to users and groups it works. The rest of te tables begin with ftp! SQLNamedQuery userinfo FREEFORM "SELECT username,passwd,uid,gid,homedir,shell FROM ftpusers WHERE username = '%U'" proftpd SQLUserInfo custom:/userinfo I have a solution for 1. monthy up-/download see "delete_oke1, delete_oke2" Date_format can not be used because some "%" overwrite with the defaults of proftpd sql var's 2. fail amounts 3. block a period of time for login 4. block always after a date 5. SQLUserInfo custom:/userinfo is for a timeclock to login only am or pm etc.etc. Do you have a solution for MaxStoreFileSize is proftpd sql. I mis uid and gid in quota.conf, because i want the user name to replace the uid. This metadata technic is better. Proftpd sql var for gid Hope to hear from you soon Best Regast Frank Bellen Dutch ############################################################################ #################################### # # Proftpd sample configuration for SQL-based authentication. # # (This is not to be used if you prefer a PAM-based SQL authentication) # #http://www.proftpd.org/docs/contrib/mod_quotatab_sql.html <IfModule mod_sql.c> QuotaEngine on Include /etc/proftpd/sql_quota.conf AuthPAMConfig proftpd #AllowStoreRestart on #AllowRetrieveRestart on #Update count every time a user logs in # server_ip='%V' #SQLUserWhereClause "not disabled = 1" #SQLGroupWhereClause "not disabled = 1" #Logging of MySql SqlLogFile /var/log/proftpd/sql.log #create a user's home directory on demand if it doesn't exist #CreateHome on SQLBackend mysql #Type of Passwords in MySQL are OpenSSL Crypt Plaintext Empty Backend SQLAuthTypes Plaintext #SQLAuthenticate users groups SQLEngine on SQLAuthenticate on #SQLAuthenticate user* group* #used to connect to the database #databasename@host database_user user_password SQLConnectInfo proftpd@localhost proftpd (*Bn185961.*) SQLNamedConnectInfo proftpd mysql proftpd@localhost:3306 proftpd (*Bn185961.*) #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 username passwd uid gid homedir shell SQLUserWhereClause "Active = 1 and disabled = 0 and username<>'-' and (failmax = 0 or failcount < failmax) and (loginafter is null or (loginafter < now() and faillock = 1) or faillock = 0) and (loginexpired is null or loginexpired < now())" # 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 SQLGroupWhereClause "disabled = 0" # set min UID and GID - otherwise these are 999 each SQLMinID 500 #display last login time when PASS command is given SQLNamedQuery login_time SELECT "modified from ftpuser where username='%U' and disabled = 0 and Active = 1" SQLShowInfo PASS "230" "Last login was: %{login_time}" # Update count every time user logs in SQLLog PASS updatecount SQLNamedQuery updatecount UPDATE "failcount = 0,count=count+1, accessed=now() WHERE username='%U'" ftpuser #logout log SQLLog EXIT time_logout SQLNamedQuery time_logout UPDATE "modified=now() WHERE username='%U' and disabled = 0 and active = 1" ftpuser # Update modified everytime user uploads or deletes a file SQLLog STOR,DELE modified SQLNamedQuery modified UPDATE "modified=now() WHERE username='%U'" ftpuser #login attempts SQLLog ERR_PASS log_fails0 IGNORE_ERRORS SQLNamedQuery log_fails0 FREEFORM "UPDATE ftpuser set failcount = failcount + 1 WHERE username = '%U' and disabled = 0 and active = 1 and failmax > 0" proftpd SQLLog ERR_PASS log_fails1 IGNORE_ERRORS SQLNamedQuery log_fails1 FREEFORM "UPDATE ftpuser set disabled = 1 WHERE username = '%U' and failcount > failmax - 1 and active = 1 and failmax > 0" proftpd SQLLog ERR_PASS log_fails2 IGNORE_ERRORS SQLNamedQuery log_fails2 UPDATE "loginafter = DATE_ADD(NOW(), INTERVAL loginafterhour HOUR) WHERE username='%U' and disabled = 1 and active = 1 and faillock = 1" ftpuser SQLLog ERR_PASS log_fails3 IGNORE_ERRORS SQLNamedQuery log_fails3 FREEFORM "UPDATE ftpuser set disabled = 0,failcount = 0 WHERE username = '%U' and active = 0" proftpd #regester user in database SQLLog PASS login_oke SQLNamedQuery login_oke FREEFORM "INSERT INTO ftplogin(gid,uid, client_ip, server_ip, protocol, insertdate,failed,Active) SELECT t2.gid,t1.uid, '%a', '%V', '%{protocol}', NOW(),0,t1.Active FROM ftpuser t1 INNER JOIN ftpgroup t2 ON t1.gid = t2.gid WHERE t1.username='%U' and t1.disabled = 0 and t1.active = 1" proftpd SQLLog ERR_PASS login_error IGNORE_ERRORS SQLNamedQuery login_error FREEFORM "INSERT INTO ftplogin(gid,uid, client_ip, server_ip, protocol, insertdate,failcount,failmax,failed,Active) SELECT t2.gid,t1.uid, '%a', '%V', '%{protocol}', NOW(),t1.failcount,failmax,1,t1.active FROM ftpuser t1 INNER JOIN ftpgroup t2 ON t1.gid = t2.gid WHERE t1.username='%U' and '%U' <> '%u' and t1.gid <> 0" proftpd SQLLog ERR_PASS present_error IGNORE_ERRORS SQLNamedQuery present_error FREEFORM "INSERT INTO ftplogin(gid,uid,username, client_ip, server_ip, protocol, insertdate,failcount,failmax,failed,active) SELECT 0,0,'%U','%a', '%V', '%{protocol}', NOW(),0,0,1,0 FROM (SELECT count(username) as count,'-' as username FROM ftpuser WHERE username='%U') t1 WHERE count = 0" proftpd SQLLog PASS delete_oke1 SQLNamedQuery delete_oke1 FREEFORM "DELETE t1 FROM ftpquotatallies t1 INNER JOIN ftpuser t2 ON t1.name = t2.username WHERE t2.username='%U' and t2.clearmonth < CAST(replace(left(TO_CHAR(now()),7),'-','') as integer)" proftpd SQLLog PASS delete_oke2 SQLNamedQuery delete_oke2 UPDATE "clearmonth = CAST(replace(left(TO_CHAR(now()),7),'-','') as integer) WHERE username='%U' and clearmonth < CAST(replace(left(TO_CHAR(now()),7),'-','') as integer)" ftpuser #Record downloads SQLLog RETR recorddownload SQLNamedQuery recorddownload FREEFORM "INSERT INTO ftpfiles(gid,uid, client_ip, server_ip, protocol, insertdate,FileType,File,Bytes) SELECT t2.gid,t1.uid, '%a', '%V', '%{protocol}', NOW(), 'download','%f', '%b' FROM ftpuser t1 INNER JOIN ftpgroup t2 ON t1.gid = t2.gid WHERE t1.username = '%U' and t1.disabled = 0 and t1.active = 1" proftpd SQLLog RETR download1 SQLNamedQuery download1 UPDATE "downloadcount = downloadcount + 1,downloadbytes=downloadbytes+%b WHERE username='%U' and disabled = 0 and active = 1" ftpuser #Record upload SQLLog STOR recordupload SQLNamedQuery recordupload FREEFORM "INSERT INTO ftpfiles(gid,uid, client_ip, server_ip, protocol, insertdate,FileType,File,Bytes) SELECT t2.gid,t1.uid, '%a', '%V', '%{protocol}', NOW(), 'upload','%f', '%b' FROM ftpuser t1 INNER JOIN ftpgroup t2 ON t1.gid = t2.gid WHERE t1.username = '%U' and t1.disabled = 0 and t1.active = 1" proftpd SQLLog STOR upload1 SQLNamedQuery upload1 UPDATE "uploadcount = uploadcount + 1,uploadbytes=uploadbytes+%b WHERE username='%U' and disabled = 0 and active = 1" ftpuser #logging in sql database SQLLog DELE,MKD,RETR,RMD,RNFR,RNTO,STOR,APPE extendedlog SQLNamedQuery extendedlog FREEFORM "INSERT INTO ftplog (client_ip,server_ip,protocol,username,operation,insertdate) VALUES ('%a','%V','%{protocol}','%u','%r',now())" proftpd SQLLog DELE recorddelete SQLNamedQuery recorddelete FREEFORM "INSERT INTO ftpfiles(gid,uid, client_ip, server_ip, protocol, insertdate,FileType,File,Bytes) SELECT t2.gid,t1.uid, '%a', '%V', '%{protocol}', NOW(), 'delete','%f', '%b' FROM ftpuser t1 INNER JOIN ftpgroup t2 ON t1.gid = t2.gid WHERE t1.username = '%U'" proftpd </IfModule>
_______________________________________________ ProFTPD Developers List <[email protected]> https://lists.sourceforge.net/lists/listinfo/proftp-devel
