NetBrix - “Where your web gets Done!”

Proftpd with Mysql backend -command list

This is a quick and dirty proftpd+mysql setup Howto. It lets you setup a ProFTPd server with a MySQL backend so you can manage ftp users on a server from a database.

• wget ftp://ftp.proftpd.org/distrib/source/proftpd-1.3.0a.tar.gz
• tar -zxf proftpd-1.3.0a.tar.gz
• cd proftpd-1.3.0a
• make dist clean
• ./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
• ln -s /usr/local/sbin/proftpd /usr/sbin/proftpd
• groupadd nogroup
• groupadd -g 5500 ftpgroup
• adduser -u 5500 -s /bin/false -d /bin/null -c “proftpd user” -g ftpgroup ftpuser
• 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` 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;

DefaultRoot ~

# Normally, we want files to be overwriteable.

AllowOverwrite on

# The passwords in MySQL are encrypted using CRYPT
SQLAuthTypes Plaintext Crypt
#SQLAuthenticate users* groups*
AuthOrder mod_sql.c mod_auth_unix.c

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

Now if you add users to the ftpusers table you should be able to login with those credentials without having to do anything else.

Please feel free to contact me if you run into a problem while setting this up.

Recently I came up with the need to test multiple ftp accounts on a server. The server has been setup using proftpd, with mysql as the backend for the authentication and qoutas.

Anyways so here is the small script which I put together. It does not use the db but can be easily modified to use the db and run tests on multiple accounts. Place this script on the host where you want to test and call it through an http call in a webbrowser.

< ?php
//initialize the variables which we’ll use for the login test
$userid=$_GET[’uid’];
$password=$_GET[’pass’];

//this too can be a query string variable if you want to test the ftp accounts on a different server
$ftp_server = “localhost”;
$conn_id = ftp_connect($ftp_server) or die(”Couldn’t connect to $ftp_server”);

// Open a session to an external ftp site
$login_result = ftp_login ($conn_id, $userid, $password);

// Check open
if ((!$conn_id) || (!$login_result)) {
echo “Ftp-connect failed!”; die;
} else {
echo “Connected.”;
}

//most important clean up after you’re done!
ftp_close()

?>

The utility of this extremely simple script is more evident when you consider a PHP hosting setup where you dont really want to use a ftp client to test. There just calling the script with the variables pulled from a db is enough to test the validity of credentials.

This is a part of the proftpd+mysql writeup which I’ll post pretty soon