Pureftpd+MySQL

From FoppaWiki

Jump to: navigation, search

This will help install and setup Pure-ftpd with virtual users in MySQL and tls/ssl. Quota and bandwidth are enabled in this setup and the guide covers both Centos (5.5) and FreeBSD (8.2). I use portmaster for installing ports on FreeBSD. It should be possible to follow the guide 1:1 and make it work, but i suggest checking out all the conf files, especially pure-ftpd.conf may have some additional options you want to change.

Contents

Installing MySQL

Centos

Install mysql-server and make it start: 

yum install mysql mysql-server
chkconfig --levels 235 mysqld on
/etc/init.d/mysqld start

FreeBSD

Install mysql-server and make it start: 

portmaster /usr/ports/databases/mysql51-server
echo 'mysql_enable="YES"' >> /etc/rc.conf
/usr/local/etc/rc.d/mysql-server start

Common

Remember to change the root password of the mysql: 

mysqladmin -u root password 'yourrootsqlpassword'
/usr/bin/mysqladmin -u root -h localhost.localdomain password 'yourrootsqlpassword'

Installing Pure-ftpd

Centos

Pure-ftpd is not in the standard centos repository, I use rpmforge and the following below is for 64bit. Pure-ftpd comes compiled with tls support from RPMForge: 

wget http://packages.sw.be/rpmforge-release/rpmforge-release-0.5.2-2.el5.rf.x86_64.rpm
rpm -Uvh rpmforge-release-0.5.2-2.el5.rf.x86_64.rpm 
nano -w /etc/yum.repos.d/rpmforge.repo
set enable til 0
yum install pure-ftpd --enablerepo=rpmforge

After that we make the user (ftpuser) and group (ftpgroup) which our virtual users will be mapped to. Replace the id with any available id on your system: 

groupadd -g 2001 ftpgroup
useradd -u 2001 -s /bin/false -d /bin/null -c "pureftpd user" -g ftpgroup ftpuser

FreeBSD

Remember to check MYSQL, TLS and LARGEFILE: 

portmaster /usr/ports/ftp/pure-ftpd

After that we make the user (ftpuser) and group (ftpgroup) which our virtual users will be mapped to. Replace the id with any available id on your system: 

pw groupadd -g 2001 -n ftpgroup
pw useradd -u 2001 -s /usr/sbin/nologin -d /nonexistent -c "pureftpd user" -g ftpgroup -n ftpuser

Create The MySQL Database For Pure-ftpd

Common

Creating the database for pureftpd is the same for both Centos and FreeBSD, atleast the layout i chose to use: 

mysql -u root -p

Replace ftpdpass with your password: 

CREATE DATABASE pureftpd;
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP ON pureftpd.* TO 'pureftpd'@'localhost' IDENTIFIED BY 'ftpdpass';
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP ON pureftpd.* TO 'pureftpd'@'localhost.localdomain' IDENTIFIED BY 'ftpdpass';
FLUSH PRIVILEGES;

We also create the table we will use for the virtual users: 

USE pureftpd;
CREATE TABLE ftpd (
User varchar(16) NOT NULL default '',
status enum('0','1') NOT NULL default '0',
Password varchar(64) NOT NULL default '',
Uid varchar(11) NOT NULL default '-1',
Gid varchar(11) NOT NULL default '-1',
Dir varchar(128) NOT NULL default '',
ULBandwidth smallint(5) NOT NULL default '0',
DLBandwidth smallint(5) NOT NULL default '0',
comment tinytext NOT NULL,
ipaccess varchar(15) NOT NULL default '*',
QuotaSize smallint(5) NOT NULL default '0',
QuotaFiles int(11) NOT NULL default 0,
PRIMARY KEY (User),
UNIQUE KEY User (User)
) TYPE=MyISAM;
quit;

Installing openssl

TLS/SSL support is experimental in Pure-ftpd, I found it to work without problems and you can read more here. First we install openssl:

Centos 

yum -y install openssl

FreeBSD 

portmaster /usr/ports/security/openssl

Common

And then we make the certificate that pure-ftpd will use, if you already have one for your webserver or whatever, you can use that. This is a selfsigned free certificate, which will satisfy my need: 

mkdir -p /etc/ssl/private

openssl req -x509 -nodes -newkey rsa:4096 -keyout \
  /etc/ssl/private/pure-ftpd.pem \
  -out /etc/ssl/private/pure-ftpd.pem

chmod 600 /etc/ssl/private/*.pem

Configure Pure-ftpd

We need to configure Pure-ftpd abit, mostly to make use of the MySQL. The ChrootEveryone setting will make PureFTPd chroot every virtual user in his home directory so he will not be able to browse directories and files outside his home directory. The CreateHomeDir line will make PureFTPd create a user's home directory when the user logs in and the home directory does not exist yet.

Centos

Only 5 options in the pure-ftpd.conf are important for now: 

cp /etc/pure-ftpd/pureftpd-mysql.conf /etc/pure-ftpd/pureftpd-mysql.conf.org
nano /etc/pure-ftpd/pure-ftpd.conf

<snip>
ChrootEveryone              yes
<snip>
NoAnonymous                 yes
<snip>
MySQLConfigFile             /etc/pure-ftpd/pureftpd-mysql.conf
<snip>
CreateHomeDir               yes
<snip>
TLS                         1

FreeBSD 

cp /usr/local/etc/pure-ftpd.conf.sample /usr/local/etc/pure-ftpd.conf
cp /usr/local/etc/pureftpd-mysql.conf.sample /usr/local/etc/pureftpd-mysql.conf

Only 5 options in pure-ftpd.conf are important for now: 

nano /usr/local/etc/pure-ftpd.conf

<snip>
ChrootEveryone              yes
<snip>
NoAnonymous                 yes
<snip>
MySQLConfigFile             /usr/local/etc/pureftpd-mysql.conf
<snip>
CreateHomeDir               yes
<snip>
TLS                         1

Common

Make your pureftpd-mysql.conf look something like this, remember to replace the ftpdpass for your MySQL user:

Centos: 

nano /etc/pure-ftpd/pureftpd-mysql.conf

FreeBSD: 

nano /usr/local/etc/pureftpd-mysql.conf

MYSQLSocket      /var/lib/mysql/mysql.sock
#MYSQLServer     localhost
#MYSQLPort       3306
MYSQLUser       pureftpd
MYSQLPassword   ftpdpass
MYSQLDatabase   pureftpd
#MYSQLCrypt md5, cleartext, crypt() or password() - md5 is VERY RECOMMENDABLE uppon cleartext
MYSQLCrypt      md5
MYSQLGetPW      SELECT Password FROM ftpd WHERE User="\L" AND status="1" AND (ipaccess = "*" OR ipaccess LIKE "\R")
MYSQLGetUID     SELECT Uid FROM ftpd WHERE User="\L" AND status="1" AND (ipaccess = "*" OR ipaccess LIKE "\R")
MYSQLGetGID     SELECT Gid FROM ftpd WHERE User="\L"AND status="1" AND (ipaccess = "*" OR ipaccess LIKE "\R")
MYSQLGetDir     SELECT Dir FROM ftpd WHERE User="\L"AND status="1" AND (ipaccess = "*" OR ipaccess LIKE "\R")
MySQLGetBandwidthUL SELECT ULBandwidth FROM ftpd WHERE User="\L"AND status="1" AND (ipaccess = "*" OR ipaccess LIKE "\R")
MySQLGetBandwidthDL SELECT DLBandwidth FROM ftpd WHERE User="\L"AND status="1" AND (ipaccess = "*" OR ipaccess LIKE "\R")
MySQLGetQTASZ   SELECT QuotaSize FROM ftpd WHERE User="\L"AND status="1" AND (ipaccess = "*" OR ipaccess LIKE "\R")
MySQLGetQTAFS   SELECT QuotaFiles FROM ftpd WHERE User="\L"AND status="1" AND (ipaccess = "*" OR ipaccess LIKE "\R")

Starting Pure-ftpd

Now we should be able to start the ftp server and test if it works:

Centos 

chkconfig --levels 235 pure-ftpd on
/etc/init.d/pure-ftpd start

FreeBSD 

echo 'pureftpd_enable="YES"' >> /etc/rc.conf
/usr/local/etc/rc.d/pure-ftpd start

Creating a user

Common

Last thing to do before we actually test it, is populate the database and make a user: 

mysql -u root -p
USE pureftpd;

Our testuser will have status 1 for active and no quota or any other restrictions. Password will be encrypted using MySQLs MD5 and the UID and GID 2001 we made when we installed Pure-ftpd. The homedir will be /ftp/testuser and the password is secretpassword: 

INSERT INTO `ftpd` (`User`, `status`, `Password`, `Uid`, `Gid`, `Dir`, `ULBandwidth`, `DLBandwidth`, `comment`, `ipaccess`, `QuotaSize`, `QuotaFiles`)
 VALUES ('testuser', '1', MD5('secretpassword'), '2001', '2001', '/ftp/testuser', '', '', '', '*', '', '0');

A 100KB/s bandwidth throttled user with 50mb quota would look like this: 

INSERT INTO `ftpd` (`User`, `status`, `Password`, `Uid`, `Gid`, `Dir`, `ULBandwidth`, `DLBandwidth`, `comment`, `ipaccess`, `QuotaSize`, `QuotaFiles`)
 VALUES ('testuser', '1', MD5('secretpassword'), '2001', '2001', '/ftp/testuser', '100', '100', '', '*', '50', '0');

quit;
Retrieved from "http://wiki.foppa.dk/index.php/Pureftpd%2BMySQL"
Personal tools
menu