Proftpd Server with mod-mysql Installation and Configuration
Proftpd is definitely the best alternative if you need to have a powerful, secure, easy to configure and manage FTP Server. We had a situation where our client needed to have a FTP server and manage users, groups and quotas from a friendly and easy to use web interface. We choose Proftpd. In case you have the same situation this blog post covers it all.
I`m taking for granted that you have a clean Ubuntu Server Installation and you have root access on it. Below I`ll include all the commands you`ll need to execute.
Installing proftpd:
We will will use proftpd`s mysql integration so we need to intall the below package:
$ sudo apt-get install proftpd-mod-mysql
MySql needs to be installed also in the target system:
$ sudo apt-get install mysql-server php5-mysql
Creating the operational MySql database:
After this we have to create a MySql database (or use an existing one) and add to it the tables needed for proftp-mod-sql proper operation.
Below follows detailed instruction on how to build those tables and they are used for:
— Table structure for table `ftpuser`
CREATE TABLE IF NOT EXISTS `ftpuser` (
`id` int(10) unsigned NOT NULL auto_increment,
`userid` varchar(32) NOT NULL default ”,
`passwd` varchar(32) NOT NULL default ”,
`uid` smallint(6) NOT NULL default ‘5500’,
`gid` smallint(6) NOT NULL default ‘5500’,
`homedir` varchar(255) NOT NULL default ”,
`shell` varchar(16) NOT NULL default ‘/sbin/nologin’,
`count` int(11) NOT NULL default ‘0’,
`accessed` datetime NOT NULL default ‘0000-00-00 00:00:00’,
`modified` datetime NOT NULL default ‘0000-00-00 00:00:00’,
PRIMARY KEY (`id`),
UNIQUE KEY `userid` (`userid`),
KEY `id` (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT=’ProFTP user table’ AUTO_INCREMENT=23 ;
The ftpuser table holds information for a ftp user. Every row in this table represents a ftp user. We can specify username and password, users home directory etc.
To create a user we`d just insert a row like below:
INSERT INTO `ftpuser` ( `userid`, `passwd`, `uid`, `gid`, `homedir`, `shell`, `count`, `accessed`, `modified`) VALUES
(‘usr3’, ‘usr3’, 5600, 5600, ‘/home/usr3’, ‘/sbin/nologin’, 0, ‘2009-11-24 13:04:09’, ‘2009-11-24 12:53:02’)
The home directory will be created the first time that the uses accesses his account. Users created like this are not real system users.
— Table structure for table `ftpgroup`
CREATE TABLE IF NOT EXISTS `ftpgroup` (
`groupname` varchar(16) NOT NULL default ”,
`gid` smallint(6) NOT NULL default ‘5500’,
`members` varchar(16) NOT NULL default ”,
KEY `groupname` (`groupname`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT=’ProFTP group table’;
The ftpgroup table holds information about the ftp user groups. Groups are useful to us because we can specify different user groups based on access privilege schemas.
If we want to create a group of readonly users we`d insert a row like below:
INSERT INTO `ftpgroup` (`groupname`, `gid`, `members`) VALUES
(‘noread’, 5600, ‘usr3’);
— Table structure for table `ftpquotalimits`
CREATE TABLE IF NOT EXISTS `ftpquotalimits` (
`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` int(10) unsigned NOT NULL default ‘0’,
`bytes_out_avail` int(10) unsigned NOT NULL default ‘0’,
`bytes_xfer_avail` int(10) unsigned 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=latin1;
The ftpquotalimits table lets us control the bandwidth that a user or group can use. If we were to define a quota of max space available of 15MB for the user we created above we`d insert a row like below:
INSERT INTO `ftpquotalimits` (`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
(usr3, ‘user’, ‘false’, ‘hard’, 15728640, 0, 0, 0, 0, 0)
The ftpquotatallies table holds the actual usage of the user quota we define in ftpquotalimits table.
Database interaction user:
After we have created the above tables we need to create a user (or use an existing one) that proftpd will use to connect to the database. This user needs to have all permissions enabled on the database.
groupadd -g 2001 ftpgroup
useradd -u 2001 -s /bin/false -d /bin/null -c “proftpd user” -g ftpgroup xkreatxx
GRANT SELECT, INSERT, UPDATE, DELETE ON ftp.* TO ‘ xkreatxx ‘ @’localhost’ IDENTIFIED BY ‘password’;
GRANT SELECT, INSERT, UPDATE, DELETE ON ftp.* TO ‘ xkreatxx ‘@’localhost.localdomain’ IDENTIFIED BY ‘password’;
FLUSH PRIVILEGES;
Proftpd configuration files:
We have to configure proftpd so that it uses MySql mode.
First we need to enable the required modules in /etc/proftpd/modules.conf
Add those lines to the above file:
LoadModule mod_sql.c
LoadModule mod_sql_mysql.c
LoadModule mod_quotatab.c
LoadModule mod_quotatab_file.c
LoadModule mod_quotatab_sql.c
After that we need to modify /etc/proftpd/proftpd.conf
#users will be ‘jailed’ in their home dirs
DefaultRoot ~
#be sure that the below line includes mod_sql.c
AuthOrder mod_sql.c mod_ldap.c mod_auth_pam.c mod_auth_unix.c
<IfModule mod_sql.c>
SQLBackend mysql
# The passwords in MySQL are encrypted using CRYPT
SQLAuthTypes Plaintext Crypt
SQLAuthenticate users groups
# used to connect to the database – the user credentials we created above
SQLConnectInfo ftpd@localhost xkreatxx root
SQLNamedQuery gettally SELECT “ROUND((bytes_in_used/1048576),2) FROM ftpquotatallies WHERE name=’%u’”
SQLNamedQuery getlimit SELECT “ROUND((bytes_in_avail/1048576),2) FROM ftpquotalimits WHERE name=’%u’”
SQLNamedQuery getfree SELECT “ROUND(((ftpquotalimits.bytes_in_avail-ftpquotatallies.bytes_in_used)/1048576),2) FROM ftp$
SQLNamedQuery userquota SELECT “IF ((SELECT (@availmbytes:=ROUND((`bytes_in_avail`/1048576),2)) FROM `ftpquotalimits` WHE$
SQLShowInfo LIST “226” “%{userquota}”
# 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_x$
SQLNamedQuery get-quota-tally SELECT “name, quota_type, bytes_in_used, bytes_out_used, bytes_xfer_used, files_in_used, fi$
SQLNamedQuery update-quota-tally UPDATE “bytes_in_used = bytes_in_used + %{0}, bytes_out_used = bytes_out_used + %{1}, by$
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
</IfModule>
If we want to give different permissions to different ftp user groups we need to use the Limit configuration directive.
Below we create 2 user groups. Group nowrite wich cannot upload or delete/rename files and the noread group which cannot retrieve files from his home directory.
#deny all users the chmod command
<Directory ~>
<Limit SITE_CHMOD>
DenyAll
</Limit>
#this group will have no write access
<Limit SITE SIZE STAT WRITE>
DenyGroup nowrite
</Limit>
#this group will have no read access
<Limit RETR >
DenyGroup noread
</Limit>
</Directory>
After saving the configuration file we need to restart the proftpd daemon so that the changes take effect:
$ sudo /etc/init.d/proftpd restart
Well that`s it! Now you got your beast up and running!
Enjoy.