Mandrake 9.2 + Proftpd + MySQL authentication + Quotas Howto

Author: Stephen Khoo (stephen@khoosys.net)

Acknowledgements: This howto borrows almost entirely from Proftpd auth/quota/(simple)log <-> postgresql (mysql) mini howto by Martijn van Buytene (martijn@sigterm.nl).

Updated: 6 April 2004

Summary

This Howto describes how to build and configure proftpd (v 1.2.9) using a MySQL database for authentication and quota management. The base machine is setup using Mandrake 9.2, so we assume you have installed the base set of MySQL rpms including the development libraries and headers. In our case we use:

libmysql12-4.0.16-1mdk
libmysql12-devel-4.0.16-1mdk
MySQL-Max-4.0.16-1mdk
MySQL-common-4.0.16-1mdk

Which places the headers in /usr/include/mysql and the libraries in /usr/lib.

Installation

Make sure you do not have the RPM version of proftpd installed. Remove if necessary using drakconf or rpm -e proftpd. Log in as your user account to do the installation. Instructions in bold should be done as root.

You should be able to copy and paste the text in the yellow boxes straight into your Mandrake PC. We have tested this on a clean server and it all seems to work fine. If you have any problems use the Contact form or Guestbook on this site to let us know. You can always let us know if this is useful anyway!

Get the sources

# as user
cd
mkdir src
cd src

# Proftpd source - note that this contains mod_sql
wget ftp://ftp.proftpd.org/distrib/source/proftpd-1.2.9.tar.bz2

# Module: quotatab source
wget http://www.castaglia.org/proftpd/modules/proftpd-mod-quotatab-1.2.11.tar.gz

Note: make sure that your path to the installation directory does not contain spaces in any of the directory names as this confuses the ./configure script totally!

Unpack sources

# as user
cd
mkdir proftpd
cd proftpd

# Unpack sources
tar -xjf ../src/proftpd-1.2.9.tar.bz2
tar -xzf ../src/proftpd-mod-quotatab-1.2.11.tar.gz

# copy quotatab sources to proftpd contrib folder
cp mod_quotatab/mod_quotatab*.c proftpd-1.2.9/contrib
cp mod_quotatab/mod_quotatab*.h proftpd-1.2.9/contrib

Compile and install

This assumes that the MySQL libraries and headers are placed where Mandrake puts them (i.e. /usr/lib and /usr/include/mysql respectively). If you have used a different MySQL installation you just need to check that you get the path to these MySQL files correct.

cd proftpd-1.2.9

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

make
su
make install

# make a symbolic link to the installed binary in /usr/sbin
# so that the startup script works OK

ln -s /usr/local/sbin/proftpd /usr/sbin/proftpd

Unix Configuration

As far as UNIX is concerned we use one real group account ftpgroup (id 5500) as every user will be jailed to their home directory using the 'DefaultRoot ~/' directive. We set up one main ftpuser account (id: 5500) with this group and a guest account ftpguest (id 5501).

groupadd -g 5500 ftpgroup
adduser -u 5500 -s /bin/false -d /bin/null -c "proftpd user" -g ftpgroup ftpuser

Note: I also add a guest user so that your client sites can have a place for their guest users to transfer files. Just run:

adduser -u 5501 -s /bin/false -d /bin/null -c "proftpd guest" -g ftpgroup ftpguest

add a password and then remember to add them to your database as members of the ftpgroup by adding a new record there.

To add a guest user, add a record for a user with userid of 5501 and set the permissions on the folders you want to share like this:
chmod 755 From_folder
chmod 731 To_folder
leaving the user and group as ftpuser.ftpgroup

Their clients will now only be able to download from the From_folder and drop files, but not list files in the To_folder.

MySQL Configuration

Create the database tables in MySQL for authentication and logging.

We use a database called ftpdb which we access using a MySQL user proftpd with password 'password'. (Clearly substitute your own password in here).

You need to log into MySQL using your root account and password.

mysql -uroot -ppassword
create database ftpdb;
grant select, insert, update on ftpdb.* to proftpd@localhost identified by 'password';

use ftpdb;

#
# Table structure for table `ftpgroup`
#

CREATE TABLE `ftpgroup` (
`groupname` varchar(16) NOT NULL default '',
`gid` smallint(6) NOT NULL default '5500',
`members` varchar(16) NOT NULL default '',
KEY `groupname` (`groupname`)
) TYPE=MyISAM COMMENT='ProFTP group table';

INSERT INTO `ftpgroup` VALUES ('ftpgroup', 5500, 'ftpuser');

# --------------------------------------------------------

#
# Table structure for table `ftpquotalimits`
#

CREATE TABLE `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` 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'
) TYPE=MyISAM;

# --------------------------------------------------------

#
# Table structure for table `ftpquotatallies`
#

CREATE TABLE `ftpquotatallies` (
`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'
) TYPE=MyISAM;

# --------------------------------------------------------

#
# Table structure for table `ftpuser`
#

CREATE TABLE `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`)
) TYPE=MyISAM COMMENT='ProFTP user table' ;

INSERT INTO `ftpuser` VALUES (1, 'testaccount', 'ftppasswd', 5500, 5500, '/home/testdomain.com', '/sbin/nologin',0,'','');

exit;

Notes:

The group table lists the members of each group. This does not need any records for a simple setup.
The user table lists each of your ftp users and records their stats and is used for authentication and pointing proftpd to the correct home directory. Using the directive 'asdfasf' we make proftpd create the home directory if one does not already exist.
The quotalimits table is used to se the current quota for the user matching its entry.
The quotatallies table is used by proftpd to keep track of the user's quotas and will be filled if there is a matching userfield 'name' when a user logs in.
We have added a user test account just called "testaccount" to check things are working ok.

Field Explanations:

quotalimits

name - username

quota_type - user, group, class, all (we use user)

per_session - true or false (we use true)

limit_type - quota limit type - hard or soft (we use hard)

bytes_in_avail - upload limit in bytes - allowed bytes on disk (eg diskquota)

bytes_out_avail - download limit in bytes - allowed bytes a user can download

bytes_xfer_avail - allowed bytes a user can transfer in/out

files_in_avail - upload limit in files - allowed number of uploaded files

files_out_avail - allowed number of downloaded files

files_xfer_avail - allowed number of files a user can transfer in/out

quotatallies

name

quota_type

bytes_in_used - upload tally in bytes

bytes_out_used - download tally in bytes

bytes_xfer_used - transfer tally in bytes

files_in_used - upload tally in files

files_out_used - download tally in files

files_xfer_used - transfer tally in files

If a value of any limit field is set to '0' it is unlimited.

You can read the documentation that comes with mod_quotatab for mysql here.

Author's Note:

You can test a 15MB quota limit on 'testaccount' by inserting this record into 'quotalimits':
INSERT INTO quotalimits VALUES('testaccount','user','true','hard','15728640','0','0','0','0','0');

I think you have to log in with this record in place before it works. It also will allow you to upload, but then deletes the file if you are over the limit. This is a bit irritating because on normal user disk quotas, you get a message as you try to upload, not after. So you might get some unhappy users who don't know why they can't upload. Also the message that you have exceeded the limit appears in the ftp client transcript and your users will miss this if they don't show transcripts.

On a related note, I can seem to get any joy out of the quotatallies stuff. When I get more time, I may investigate this. In the meantime, if any of you find out what is going on there, it would be cool if you leave a note in the forum here - then everyone can benefit.

Proftpd Config File

Here is our complete configuration file setup with MySQL and quota support. Keep a copy of the installation one in /usr/local/etc/ and replace with one like this - change to suit your ServerName and ServerAdmin.

ServerName                      "Khoosys Proftpd Server"
ServerType                      Standalone
ServerAdmin                     stephen@khoosys.net

# Hide as much as possible to outside users
ServerIdent                     on "Welcome to the Khoosys FTP server. Please login..."
DeferWelcome                  on

DefaultServer                   on

# Allow FTP resuming.
# Remember to set to off if you have an incoming ftp for upload.
AllowStoreRestart               on

# 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

# 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

# Set the user and group under which the server will run.
User                            nobody
Group                           nogroup

# To cause every FTP user to be "jailed" (chrooted) into their home
# directory, uncomment this line.
DefaultRoot ~

# Normally, we want files to be overwriteable.

AllowOverwrite                on

# The passwords in MySQL are encrypted using CRYPT
SQLAuthTypes            Plaintext Crypt
SQLAuthenticate         users* groups*

# used to connect to the database
# databasename@host database_user user_password
SQLConnectInfo ftpdb@localhost proftpd password

# 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_xfer_avail, files_in_avail, files_out_avail, files_xfer_avail FROM ftpquotalimits WHERE name = '%' AND quota_type = '%'"

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 ftpquotatallies WHERE name = '%' AND quota_type = '%'"

SQLNamedQuery update-quota-tally UPDATE "bytes_in_used = bytes_in_used + %, bytes_out_used = bytes_out_used + %, bytes_xfer_used = bytes_xfer_used + %, files_in_used = files_in_used + %, files_out_used = files_out_used + %, files_xfer_used = files_xfer_used + % WHERE name = '%' AND quota_type = '%'" ftpquotatallies

SQLNamedQuery insert-quota-tally INSERT "%, %, %, %, %, %, %, %" ftpquotatallies

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

RootLogin off
RequireValidShell off

Proftpd startup file

As you can see we start proftpd as a standalone process and not as an xinetd service. We use the proftpd startup script provided by the rpm distribution. You can download it here.

Testing

Start the service using /etc/rc.d/init.d/proftpd and login to the ftp server using 'testaccount' and 'ftppasswd'.
If the home directory '/home/testdomain.com' does not already exist it will be created.

Have fun!!

A few useful tips

If you want to check that queries are coming through to MySQL, turn on logging by adding a line to your MySQL configuration file such as: log=/var/lib/mysql/mysql.log

Remember to turn off logging when you are done as it will slow things down with it on!

(Note that the MySQL config file is usually '/etc/my.cnf'. If you don't have one MySQL usually provides a set which you can copy according to the size of server you want to run. For example, a huge server can use : '/usr/share/mysql/my-huge.cnf')