Proftpd MySQL Authentication

Description
Building FTP server based on ProFTPD and support by MySQL authentication.
User separate by 2 groups, user full access and user readonly.

Download
ftp://ftp.proftpd.org/distrib/source/proftpd-1.3.0a.tar.gz
http://dev.mysql.com/downloads/

Configure [Configure ProFTPD]
# ./configure --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

[Login To MySQL]
# mysql -u root -p
mysql> create database ftp;
mysql> use mysql;
mysql> GRANT SELECT, INSERT, UPDATE, DELETE ON ftp.* TO 'proftp'@'localhost'
IDENTIFIED BY 'kunam';

mysql> GRANT SELECT, INSERT, UPDATE, DELETE ON ftp.* TO 'proftp'@'localhost.localdomain'
IDENTIFIED BY 'kunam';

mysql> FLUSH PRIVILEGES;
mysql> USE ftp;

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';

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;

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;

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';

##### Create Group Full Access #####
INSERT INTO `ftpgroup` (`groupname`, `gid`, `members`) VALUES ('ftpgroup', 2001,
'ftpuser');

##### Create Group Read Only #####
INSERT INTO `ftpgroup` (`groupname`, `gid`, `members`) VALUES ('readftpgroup',
2002, 'readftpuser');

##### Create Test User Full Access #####
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 ('demouser', 'user', 'true', 'hard', 157286400, 0,
0, 0, 0, 0);

INSERT INTO `ftpuser` (`id`, `userid`, `passwd`, `uid`, `gid`, `homedir`, `shell`,
`count`, `accessed`, `modified`) VALUES (1, 'demouser', 'secret', 2001, 2001,
'/data/ftp/demouser', '/sbin/nologin', 0, '', '');

##### Create Test User Readonly #####

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 ('userdemo', 'user', 'true', 'hard', 157286400, 0,
0, 0, 0, 0);

INSERT INTO `ftpuser` (`id`, `userid`, `passwd`, `uid`, `gid`, `homedir`, `shell`,
`count`, `accessed`, `modified`) VALUES (2, 'userdemo', 'wedhus', 2002, 2002,
'/data/ftp/demouser', '/sbin/nologin', 0, '', '');

mysql> quit

##### Linux Shell ###############
##### Add Group Full Access #####
groupadd -g 2001 ftpgroup

##### Add User Full Access #####
useradd -u 2001 -s /bin/false -d /bin/null -c "proftpd user" -g ftpgroup
ftpuser

##### Add Group Read Only #####
groupadd -g 2002 readftpgroup

Exec Path:
/usr/local/sbin/proftpd

Config File:
/usr/local/etc/proftpd.conf

Data:
/data/ftp/

# Basic ProFTPD Configuration
ServerName "FTP Server"
ServerType standalone
DefaultServer on
ServerAdmin admin@localhost.com

# 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

MaxInstances 30

# Set the user and group under
which the server will run.

User ftpuser
Group ftpgroup

# 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

#####################

TransferLog /var/log/proftpd.xferlog
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

#####################

# 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 ftp@localhost proftpd kunam

# 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 = '%{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 f

tpquotatallies 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 of proftpd.conf #########

[Start proftpd]
/usr/local/sbin/proftpd