Setup Proftpd 1.3.2b to support mysql database account

I want to setup Proftpd 1.3.2b to support mysql database account.
There are many error during make and I try and try. Finally I sucess to install.

Environment
Slackware 13.0
mysql 4.0.26 (Binary install)
proftpd-1.3.2b

get proftpd-1.3.2b.tar.gz from proftpd.org the put on /usr/local/src/ and begin installation.
mysql installed on /usr/local/mysql/ its binary install that lib and include for mysql stay here.
But if you install by make default your lib may be place at /usr/lib and include at /usr/include you must find location of this files
mysql.h , mysqlclient.a.

$ tar -zxvf proftpd-1.3.2b.tar.gz
$ ./configure --with-modules=mod_sql:mod_sql_mysql:mod_quotatab:mod_quotatab_sql \
--with-includes=/usr/local/mysql/include/ --with-libraries=/usr/lib:/usr/local/mysql/lib/lib --with-zilb
$ make

### this state found error I can't remember in detail but may be like this ###
modules/mod_cap.o: In function `lp_add_cap':
mod_cap.c:(.text+0x1a): undefined reference to `cap_set_flag'
modules/mod_cap.o: In function `lp_set_cap':
mod_cap.c:(.text+0x6d): undefined reference to `cap_set_proc'
modules/mod_cap.o: In function `lp_free_cap':
mod_cap.c:(.text+0xda): undefined reference to `cap_free'
modules/mod_cap.o: In function `cap_post_pass':
mod_cap.c:(.text+0x150): undefined reference to `cap_init'
mod_cap.c:(.text+0x226): undefined reference to `cap_get_proc'
mod_cap.c:(.text+0x23d): undefined reference to `cap_to_text'
mod_cap.c:(.text+0x260): undefined reference to `cap_free'
mod_cap.c:(.text+0x268): undefined reference to `cap_free'
mod_cap.c:(.text+0x496): undefined reference to `cap_free'
modules/mod_cap.o: In function `cap_module_init':
mod_cap.c:(.text+0x507): undefined reference to `cap_get_proc'
mod_cap.c:(.text+0x514): undefined reference to `cap_free'
collect2: ld returned 1 exit status
make: *** [proftpd] Error 1

************************************
: undefined reference to `compress'
/usr/local/mysql/lib/libmysqlclient.a(my_compress.o)(.text+0x116): In function `my_compress_alloc':
: undefined reference to `compress'
/usr/local/mysql/lib/libmysqlclient.a(my_compress.o)(.text+0x18d): In function `my_uncompress':
: undefined reference to `uncompress'
collect2: ld returned 1 exit status
make: *** [proftpd] Error 1
************************************

Solve:

$ vi Make.rules

****************
edit the line that include:
LIBS=-lsupp -ldl -lcrypt -lm -lmysqlclient -lpam

replace with:
LIBS= -lsupp -ldl -lcrypt -lm -lz -lcap /usr/local/mysql/lib/libmysqlclient.a

Note: if you run ./configure again you must edit Make.rules again.
***************

$ vi contrib/mod_sql_mysql.c

Search to found the line that include:
#include

Delete the line and insert 2 line below:

#include
#include

OK, Make again:
$ make
$ make install

It should be no error.

Modify some thing.

$ ln -s /usr/local/sbin/proftpd /usr/sbin/proftpd
$ ln -s /usr/local/var/proftpd.pid /var/run/proftpd.pid
$ ln -s /usr/local/etc/proftpd.conf /etc/proftpd.conf

Create database and provide data to test.

$ mysql -u root -p
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';

#
# Dumping data for table `ftpgroup`
#

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

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

#
# 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 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'
) 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 int(10) unsigned NOT NULL default '0',
bytes_out_used int(10) unsigned NOT NULL default '0',
bytes_xfer_used int(10) unsigned 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 `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),
UNIQUE KEY userid (userid)
) TYPE=MyISAM COMMENT='ProFTP user table';

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

exit;

************************

Create user and group for proftpd:
$ groupadd -g 5500 ftpgroup
$ useradd ftpuser -u 5500 -s /bin/false -d /bin/null -c "proftpd user" -g ftpgroup

Config proftpd file /etc/proftpd.conf

$ vi /etc/proftpd.conf

## Begin proftpd.conf ##

# This is a basic ProFTPD configuration file (rename it to
# 'proftpd.conf' for actual use. It establishes a single server
# and a single anonymous login. It assumes that you have a user/group
# "nobody" and "ftp" for normal operation and anon.

ServerName "ProFTPD Default Installation"
#.ServerType standalone
#.Set Proftpd to run with /etc/inetd.conf
ServerType inetd
DefaultServer on

# Port 21 is the standard FTP port.
Port 21

# Don't use IPv6 support by default.
UseIPv6 off

# 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
#User ftpuser
#Group ftpgroup

SystemLog /var/log/proftpd.log
TransferLog /var/log/xferlog

# 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

# Bar use of SITE CHMOD by default

DenyAll


# A basic anonymous configuration, no upload directories. If you do not
# want anonymous users, simply delete this entire section.
#.
#. User ftp
#. Group ftp

# We want clients to be able to login with "anonymous" as well as "ftp"
#. UserAlias anonymous ftp

# Limit the maximum number of anonymous logins
#. MaxClients 10

# We want 'welcome.msg' displayed at login, and '.message' displayed
# in each newly chdired directory.
#. DisplayLogin welcome.msg
#. DisplayChdir .message

# Limit WRITE everywhere in the anonymous chroot
#.
#. DenyAll
#.
#.

DefaultRoot ~
# 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 yourpassword


# 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
#error 2009-11-20
#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 = '%{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 ftpquotatallies 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}'" ftpquotatallies

#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

## End proftpd.conf ###
***********************************
++++++++++++++++++++++++++++++++++

Chang permission for ftp directory.

$ chown -R ftpuser:ftpgroup /home/testdomain.com

Couse we run proftpd with inetd then will edit /etc/inetd.conf
$vi /etc/inetd.conf

Uncomment the line that include:
ftp stream tcp nowait root /usr/sbin/tcpd proftpd

And now all look like good its the best to restart. Then test ftp tranfer with local linux account and mysql account.
I pray it should work. |o|

11-2009