proftpd+mysql虚拟用户的配置

以前使用的proftpd,但是没有和mysql结合起来。导致用户管理上不方便。
这2天研究了一下proftpd的配置,做到了使ProFTPD 支持MySQL数据库,添加虚拟用户认证及Quotas(磁盘限额)。具体的如下:

1、安装proftpd带mysql支持
 

apt-get install proftpd-mysql

 
2、创建proftpd使用的mysql数据库
 

[[email protected] ~]# mysql -uroot -p
mysql>create database proftpd;
mysql>Grant select,insert,update,delete,create,drop,index,alter,create temporary tables,lock tables on proftpd.* to [email protected] Identified by "password";
mysql>quit

 
3、导入数据库。这一步,可以使用phpmyaqmin进行

-- 数据库: `proftpd`
--
-- --------------------------------------------------------
--
-- 表的结构 `ftpgroups`
--
CREATE TABLE `ftpgroups` (
  `groupname` varchar(30) NOT NULL default '',
  `gid` int(11) NOT NULL default '1000',
  `members` varchar(255) NOT NULL default ''
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
-- --------------------------------------------------------
--
-- 表的结构 `ftpusers`
--
CREATE TABLE `ftpusers` (
  `userid` varchar(30) NOT NULL default '',
  `passwd` varchar(80) NOT NULL default '',
  `uid` int(10) unsigned NOT NULL default '1000',
  `gid` int(10) unsigned NOT NULL default '1000',
  `homedir` varchar(255) NOT NULL default '',
  `shell` varchar(255) NOT NULL default '/sbin/nologin',
  `count` int(10) unsigned NOT NULL default '0',
  `host` varchar(30) NOT NULL default '',
  `lastlogin` varchar(30) NOT NULL default '',
  UNIQUE KEY `userid` (`userid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
--
-- 导出表中的数据 `ftpusers`
--
INSERT INTO `ftpusers` VALUES ('test', 'test', 1000, 1000, '/home/test', '/sbin/nologin',0,'','');
-- --------------------------------------------------------
--
-- 表的结构 `quotalimits`
--
CREATE TABLE `quotalimits` (
  `name` varchar(30) default NULL,
  `quota_type` enum('user','group','class','all') NOT NULL default 'user',
  `per_session` enum('false','true') NOT NULL default 'false',
  `limit_type` enum('soft','hard') NOT NULL default 'soft',
  `bytes_in_avail` float NOT NULL default '0',
  `bytes_out_avail` float NOT NULL default '0',
  `bytes_xfer_avail` float NOT NULL default '0',
  `files_in_avail` int(10) unsigned NOT NULL default '0',
  `files_out_avail` int(10) unsigned NOT NULL default '0',
  `files_xfer_avail` int(10) unsigned NOT NULL default '0'
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
-- --------------------------------------------------------
--
-- 表的结构 `quotatallies`
--
CREATE TABLE `quotatallies` (
  `name` varchar(30) NOT NULL default '',
  `quota_type` enum('user','group','class','all') NOT NULL default 'user',
  `bytes_in_used` float NOT NULL default '0',
  `bytes_out_used` float NOT NULL default '0',
  `bytes_xfer_used` float NOT NULL default '0',
  `files_in_used` int(10) unsigned NOT NULL default '0',
  `files_out_used` int(10) unsigned NOT NULL default '0',
  `files_xfer_used` int(10) unsigned NOT NULL default '0'
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

4、修改proftpd的配置文件

nano proftpd.conf

在其中增加下面几行(注意,下面的部分仅仅是mysql连接部分,关于proftpd的配置文件写法,参照我以前的文章把)

#——– load sql.mod for mysql authoritative ——–#
SQLConnectInfo [email protected] proftpd proftpd
#注:上面这行是MySQL连接服务器部份,自己根据情况来改一改;
SQLAuthTypes Plaintext
SQLUserInfo ftpusers userid passwd uid gid homedir shell
SQLGroupInfo ftpgroups groupname gid members
SQLAuthenticate users groups
SQLNegativeCache on
SQLHomedirOnDemand on
SQLLogFile /var/log/proftpd.sql.log
SQLNamedQuery getcount SELECT "count from ftpusers where userid=’%u’"
SQLNamedQuery getlastlogin SELECT "lastlogin from ftpusers where userid=’%u’"
SQLNamedQuery updatelogininfo UPDATE "count=count+1,host=’%h’,lastlogin=current_timestamp() WHERE userid=’%u’" ftpusers
SQLShowInfo PASS "230" "You’ve logged on %{getcount} times, last login at %{getlastlogin}"
SQLLog PASS updatelogininfo
#——– load sql.mod for mysql authoritative ——–#
#——— load qudes.mod for Quota limit ——–#
QuotaDirectoryTally on
QuotaDisplayUnits "Mb"
QuotaEngine on
#QuotaLog /var/log/proftpd.quota.log
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 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
#——— load qudes.mod for Quota limit ——–#
# Logging options
# Debug Level
# emerg, alert, crit (empfohlen), error, warn. notice, info, debug
#
SyslogLevel emerg
SystemLog /var/log/proftpd.system.log
TransferLog /var/log/proftpd.xferlog
# Some logging formats
#
LogFormat default "%h %l %u %t "%r" %s %b"
LogFormat auth "%v [%P] %h %t "%r" %s"
LogFormat write "%h %l %u %t "%r" %s %b"
# Log file/dir access
# ExtendedLog /var/log/proftpd.access_log WRITE,READ write
# Record all logins
ExtendedLog /var/log/proftpd.auth_log AUTH auth
# Paranoia logging level….
ExtendedLog /var/log/proftpd.paranoid_log ALL default

5、启动prfoftpd,测试

/etc/init.d/proftpd restart
 
需要注意的事情有:
注意检查添加的虚拟用户的主目录和shell设置是否正常,这2个是不能登陆的最常见的原因
另外,似乎proftpd启用了虚拟用户之后,和vsftpd一样,正常用户无法登陆。而且,虚拟用户的用户名,不能和系统用户的用户名相同,否则,不能正常登陆
 
 

Leave a Reply

Your email address will not be published.