RH8+proftp1.2.8+mod_sql+mod_Quota的问题

npk

经过测试mod_mysql使用proftpd1.2.8自己的modules就可以，不需要使用mod_sql-4.08.tar.gz，使用的话在1.2.8编译的时候会报错，只要修改contrib/mod_sql_mysql.c文件#include <mysql/mysql.h>; 把他该为你实际路径

./configure --prefix=/usr/local/proftpd --with-modules=mod_sql:mod_sql_mysql:mod_quotatab:mod_quotatab_sql  --with-includes=/usr/include/mysql --with-libraries=/usr/lib/mysql

进行编译：
make
安装：
make install

按照精华贴中见表修改proftpd.conf文件auth_mod_mysql是有效的

use ftp;
create table ftpusers (
userid TEXT NOT NULL,
passwd TEXT NOT NULL,
uid INT NOT NULL,
gid INT NOT NULL,
home TEXT,
shell TEXT
);


create table ftpgrps (
grpname TEXT NOT NULL,
gid   INT NOT NULL,
members TEXT NOT NULL
);


create table quotalimits (
name VARCHAR(30),
quota_type ENUM("user", "group", "class", "all" NOT NULL,
per_session ENUM("false", "true" NOT NULL,
limit_type ENUM("soft", "hard" NOT NULL,
bytes_in_avail FLOAT NOT NULL,
bytes_out_avail FLOAT NOT NULL,
bytes_xfer_avail FLOAT NOT NULL,
files_in_avail INT UNSIGNED NOT NULL,
files_out_avail INT UNSIGNED NOT NULL,
files_xfer_avail INT UNSIGNED NOT NULL
);

INSERT INTO ftpusers (userid, passwd, uid, gid, home, shell) values ('user1', '999999', '1000', '1000', '/home/ftp/user1', '');

INSERT INTO quotalimits (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) values ('user1', 'user', 'false', 'soft', '10240000', '0', '2048000', '500', '0', '10');

建立好的表如下

mysql>; desc ftpusers;
+--------+---------+------+-----+---------+-------+
| Field  | Type    | Null | Key | Default | Extra |
+--------+---------+------+-----+---------+-------+
| userid | text    |      |     |         |       |
| passwd | text    |      |     |         |       |
| uid    | int(11) |      |     | 0       |       |
| gid    | int(11) |      |     | 0       |       |
| home   | text    | YES  |     | NULL    |       |
| shell  | text    | YES  |     | NULL    |       |
+--------+---------+------+-----+---------+-------+


mysql>; desc ftpgrps;
+---------+---------+------+-----+---------+-------+
| Field   | Type    | Null | Key | Default | Extra |
+---------+---------+------+-----+---------+-------+
| grpname | text    |      |     |         |       |
| gid     | int(11) |      |     | 0       |       |
| members | text    |      |     |         |       |
+---------+---------+------+-----+---------+-------+


mysql>; desc quotalimits;
+------------------+------------------------------------+------+-----+---------+-------+
| Field            | Type                               | Null | Key | Default | Extra |
+------------------+------------------------------------+------+-----+---------+-------+
| name             | varchar(30)                        | YES  |     | NULL    |       |
| quota_type       | enum('user','group','class','all') |      |     | user    |       |
| per_session      | enum('false','true')               |      |     | false   |       |
| limit_type       | enum('soft','hard')                |      |     | soft    |       |
| bytes_in_avail   | float                              |      |     | 0       |       |
| bytes_out_avail  | float                              |      |     | 0       |       |
| bytes_xfer_avail | float                              |      |     | 0       |       |
| files_in_avail   | int(10) unsigned                   |      |     | 0       |       |
| files_out_avail  | int(10) unsigned                   |      |     | 0       |       |
| files_xfer_avail | int(10) unsigned                   |      |     | 0       |       |
+------------------+------------------------------------+------+-----+---------+-------+

使用-n -d5 来看ftpd服务有如下输出：

[root@m1 sbin]# ./proftpd -n -d5
- parsing '/usr/local/proftpd/etc/proftpd.conf' configuration
- <Directory />;: adding section for resolved path '/'
m1.xxxx.com.cn -
m1.xxxx.com.cn - Config for ProFTPD Mysql+Quota TEST:
m1.xxxx.com.cn- ~ftp/
m1.xxxx.com.cn -  Limit
m1.xxxx.com.cn -   DenyAll
m1.xxxx.com.cn -  UserName
m1.xxxx.com.cn -  GroupName
m1.xxxx.com.cn -  UserAlias
m1.xxxx.com.cn-  MaxClients
m1.xxxx.com.cn -  DisplayLogin
m1.xxxx.com.cn -  DisplayFirstChdir
m1.xxxx.com.cn -  Umask
m1.xxxx.com.cn -  SQLConnectInfo
m1.xxxx.com.cn -  SQLAuthTypes
m1.xxxx.com.cn -  RequireValidShell
m1.xxxx.com.cn -  SQLAuthenticate
m1.xxxx.com.cn -  SQLHomedirOnDemand
m1.xxxx.com.cn -  SQLNamedQuery_get-quota-limit
m1.xxxx.com.cn -  SQLNamedQuery_get-quota-tally
m1.xxxx.com.cn -  SQLNamedQuery_update-quota-tally
m1.xxxx.com.cn -  SQLNamedQuery_insert-quota-tally
m1.xxxx.com.cn - /
m1.xxxx.com.cn -  AllowOverwrite
m1.xxxx.com.cn -  Umask
m1.xxxx.com.cn -  SQLConnectInfo
m1.xxxx.com.cn -  SQLAuthTypes
m1.xxxx.com.cn -  RequireValidShell
m1.xxxx.com.cn -  SQLAuthenticate
m1.xxxx.com.cn -  SQLHomedirOnDemand
m1.xxxx.com.cn -  SQLNamedQuery_get-quota-limit
m1.xxxx.com.cn -  SQLNamedQuery_get-quota-tally
m1.xxxx.com.cn -  SQLNamedQuery_update-quota-tally
m1.xxxx.com.cn -  SQLNamedQuery_insert-quota-tally
m1.xxxx.com.cn - DefaultServer
m1.xxxx.com.cn - Umask
m1.xxxx.com.cn - QuotaDirectoryTally
m1.xxxx.com.cn - QuotaDisplayUnits
m1.xxxx.com.cn - QuotaEngine
m1.xxxx.com.cn - QuotaShowQuotas
m1.xxxx.com.cn - UserID
m1.xxxx.com.cn - UserName
m1.xxxx.com.cn - GroupID
m1.xxxx.com.cn - GroupName
m1.xxxx.com.cn - SQLConnectInfo
m1.xxxx.com.cn - SQLAuthTypes
m1.xxxx.com.cn - SQLUserTable
m1.xxxx.com.cn - SQLUsernameField
m1.xxxx.com.cn - SQLPasswordField
m1.xxxx.com.cn - SQLUidField
m1.xxxx.com.cn - SQLGidField
m1.xxxx.com.cn - SQLHomedirField
m1.xxxx.com.cn - SQLShellField
m1.xxxx.com.cn - RequireValidShell
m1.xxxx.com.cn - SQLAuthenticate
m1.xxxx.com.cn - SQLHomedirOnDemand
m1.xxxx.com.cn - SQLNamedQuery_get-quota-limit
m1.xxxx.com.cn - SQLNamedQuery_get-quota-tally
m1.xxxx.com.cn - SQLNamedQuery_update-quota-tally
m1.xxxx.com.cn - SQLNamedQuery_insert-quota-tally
m1.xxxx.com.cn - QuotaLimitTable
m1.xxxx.com.cn - QuotaTallyTable
m1.xxxx.com.cn - ProFTPD 1.2.8 (stable) (built 日 3月 30 14:34:22 CST 2003) standalone mode STARTUP

npk

proftpd.conf

Umask                                022

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


QuotaDirectoryTally on
QuotaDisplayUnits Mb
QuotaEngine on
QuotaShowQuotas on

<Global>;

SQLConnectInfo ftp@localhost:3306 root s_search
SQLAuthTypes Backend Plaintext
SQLUserInfo ftpusers userid passwd uid gid home shell
#SQLGroupInfo ftpgrps groupname gid members

RequireValidShell off

#SQLAuthenticate users groups usersetfast groupsetfast
SQLAuthenticate users
SQLHomedirOnDemand on

#..........SQL..........................

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 quotalimits 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 quotatallies 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}'" quotatallies

SQLNamedQuery insert-quota-tally INSERT "%{0}, %{1}, %{2}, %{3}, %{4}, %{5}, %{6}, %{7}" quotatallies

QuotaLimitTable sql:/get-quota-limit
QuotaTallyTable sql:/get-quota-tally/update-quota-tally/insert-quota-tally


</Global>;

# Set the user and group under which the server will run.
User                                ftpusr
Group                                ftpgrp

npk

只用时mod_sql_mysql是没有问题的，但是一旦使用quota就出错，客户端输出如下
Connected to 202.xxx.xxx.xxx (202.xxx.xxx.xxx)
220 ProFTPD 1.2.8 Server (ProFTPD Mysql+Quota TEST) [m1.xxx.xxx.xxx]
Name (202.xxx.xxx.xxx:root): user1
331 Password required for user1.
Password:
421 Service not available, remote server has closed connection
Login failed.
No control connection for command: No such file or directory
ftp>;

服务器端输出：

m1.xxx.xxx.xxx - ProFTPD 1.2.8 (stable) (built 日 3月 30 14:34:22 CST 2003) standalone mode STARTUP
m1.xxx.xxx.xxx (m1.xxx.xxx.xxx[202.xxx.xxx.xxx]) - connected - local  : 201.26.16.8:21
m1.xxx.xxx.xxx (m1.xxx.xxx.xxx[202.xxx.xxx.xxx]) - connected - remote : 202.xxx.xxx.xxx:4118
m1.xxx.xxx.xxx (m1.xxx.xxx.xxx[202.xxx.xxx.xxx]) - FTP session opened.
m1.xxx.xxx.xxx (m1.xxx.xxx.xxx[202.xxx.xxx.xxx]) - dispatching PRE_CMD command 'USER user1' to mod_core
m1.xxx.xxx.xxx (m1.xxx.xxx.xxx[202.xxx.xxx.xxx]) - dispatching PRE_CMD command 'USER user1' to mod_core
m1.xxx.xxx.xxx (m1.xxx.xxx.xxx[202.xxx.xxx.xxx]) - dispatching PRE_CMD command 'USER user1' to mod_auth
m1.xxx.xxx.xxx (m1.xxx.xxx.xxx[202.xxx.xxx.xxx]) - dispatching CMD command 'USER user1' to mod_auth
m1.xxx.xxx.xxx (m1.xxx.xxx.xxx[202.xxx.xxx.xxx]) - no supplemental groups found for user 'user1'
m1.xxx.xxx.xxx (m1.xxx.xxx.xxx[202.xxx.xxx.xxx]) - dispatching POST_CMD command 'USER user1' to mod_sql
m1.xxx.xxx.xxx (m1.xxx.xxx.xxx[202.xxx.xxx.xxx]) - dispatching LOG_CMD command 'USER user1' to mod_sql
m1.xxx.xxx.xxx (m1.xxx.xxx.xxx[202.xxx.xxx.xxx]) - dispatching LOG_CMD command 'USER user1' to mod_log
m1.xxx.xxx.xxx (m1.xxx.xxx.xxx[202.xxx.xxx.xxx]) - dispatching PRE_CMD command 'PASS (hidden)' to mod_core
m1.xxx.xxx.xxx (m1.xxx.xxx.xxx[202.xxx.xxx.xxx]) - dispatching PRE_CMD command 'PASS (hidden)' to mod_core
m1.xxx.xxx.xxx (m1.xxx.xxx.xxx[202.xxx.xxx.xxx]) - dispatching PRE_CMD command 'PASS (hidden)' to mod_auth
m1.xxx.xxx.xxx (m1.xxx.xxx.xxx[202.xxx.xxx.xxx]) - dispatching CMD command 'PASS (hidden)' to mod_auth
m1.xxx.xxx.xxx (m1.xxx.xxx.xxx[202.xxx.xxx.xxx]) - no supplemental groups found for user 'user1'
m1.xxx.xxx.xxx (m1.xxx.xxx.xxx[202.xxx.xxx.xxx]) - PAM(user1): Authentication failure.
m1.xxx.xxx.xxx (m1.xxx.xxx.xxx[202.xxx.xxx.xxx]) - USER user1 (Login failed): No such user found.
m1.xxx.xxx.xxx (m1.xxx.xxx.xxx[202.xxx.xxx.xxx]) - dispatching POST_CMD_ERR command 'PASS (hidden)' to mod_sql
m1.xxx.xxx.xxx (m1.xxx.xxx.xxx[202.xxx.xxx.xxx]) - dispatching LOG_CMD_ERR command 'PASS (hidden)' to mod_sql
m1.xxx.xxx.xxx (m1.xxx.xxx.xxx[202.xxx.xxx.xxx]) - dispatching LOG_CMD_ERR command 'PASS (hidden)' to mod_log
m1.xxx.xxx.xxx (m1.xxx.xxx.xxx[202.xxx.xxx.xxx]) - dispatching PRE_CMD command 'SYST' to mod_core
m1.xxx.xxx.xxx (m1.xxx.xxx.xxx[202.xxx.xxx.xxx]) - dispatching PRE_CMD command 'SYST' to mod_core
m1.xxx.xxx.xxx (m1.xxx.xxx.xxx[202.xxx.xxx.xxx]) - dispatching CMD command 'SYST' to mod_core
m1.xxx.xxx.xxx (m1.xxx.xxx.xxx[202.xxx.xxx.xxx]) - dispatching POST_CMD command 'SYST' to mod_sql
m1.xxx.xxx.xxx (m1.xxx.xxx.xxx[202.xxx.xxx.xxx]) - dispatching LOG_CMD command 'SYST' to mod_sql
m1.xxx.xxx.xxx (m1.xxx.xxx.xxx[202.xxx.xxx.xxx]) - dispatching LOG_CMD command 'SYST' to mod_log

npk

不知道那里出问题了，请大家看看，谢谢～

wind521

是不是他自己的这个模块本身工作起来与quota会有问题呢？

现在没有太多的时间去测试了
真是对不住

npk

原帖由 "wind521" 发表：
是不是他自己的这个模块本身工作起来与quota会有问题呢？

现在没有太多的时间去测试了
真是对不住

FTP的文件系统没有使用quota来设置，使用mod_sql-4.08.tar.gz来做sql认证，在1.2.8编译的时候会报错，使用自己的mod_sql_mysql.c文件就可以正常编译，单使用mysql认证也正常。

npk

原帖由 "wind521" 发表：
是不是他自己的这个模块本身工作起来与quota会有问题呢？

现在没有太多的时间去测试了
真是对不住

在1.2.8中使用mod_sql-4.08.tar.gz，在编译的时候会出错，但使用1.2.8自己的mod_sql文件就可以编译过去，并且在只使用mysql验证身份是成功的。

npk

quota在mysql中缺了一个表，现在全部正常了