Knowledge Base

A huge thanks to one of our customers, Gabriel, for putting together this tutorial. Enjoy!
 

This guide describes how to configure an FTP server that reads users from a mysql database.
It has the advantage that you can have many diferent users attached to a singular Unix account in a very easy way.

What's needed to create an FTP server?

1 - Unix group, a subgroup, an user, password, and a directory attached to the user
2 - An FTP server (we'll use pure-ftpd)
3 - In this case we're going to use MySQL to manage the virtual accounts (users that use Unix users config)
4 - Config your linux Firewall for the ftp server

1:

# useradd -gftp -Gftpusers -pxxxx -d/var/www -m technician

xxxx is your password
/var/www is your desired directory
technician is the username you want

usually ftp groups are there already, if not, use: # groupadd ftp

Note that there is an ID attached to the user (UID) and one attached to the group (GID),
The default is 500 for both, if you set it diferent, use your ID's in the next instructions.

2:

In my case I use Fedora, so i did
# yum install pure-ftpd
In Ubunto you use
# apt-get install pure-ftpd-mysql
Else, Check www.pureftpd.org or your distro documentation on how to install it with mysql support

Note, if you compile the program by hand, you also need to put quotas support.

Configuration:

# nano -w /etc/pure-ftpd/pure-ftpd.conf

Change the options that you fit your needs, find these that should be uncomment and look like this:

ChrootEveryone yes
MySQLConfigFile /etc/pure-ftpd/pureftpd-mysql.conf
UnixAuthentication yes
PassivePortRange 30000 50000
MinUID 499

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

MYSQLSocket /var/run/mysqld/mysqld.sock

If mysql is in the same box, you don't need to config these 2.
#MYSQLServer localhost
#MYSQLPort 3306

# this is the mysql user that you will create ahead
MYSQLUser pureftpd

# that user's password

MYSQLPassword yourpass

# the mysql schema's name
MYSQLDatabase pureftpd


# Supports encrypted md5, cleartext, crypt() ou password(), I recommend md5
MYSQLCrypt md5

# status check means if value is not 1 that user is disabled
# I use "*" for IP so it allows any, but it may be defined.

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")

3:

make a MySQL user named pureftpd and put the "yourpass" that you wrote in the pureftpd-mysql.conf file.
make a schema(database) named pureftpd

If you don't know how to do this, get mysql gui tools here: http://dev.mysql.com/downloads/gui-tools/5.0.html
or use your control panel

Run this script in the pureftpd schema (code credits to Emerson Araujo)

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)
) ENGINE=MyISAM;

Now we can add users in this table!
Remember the technician Unix user we made? now we're going to make a virtual user named richard using technician's ID

run this script in the pureftpd schema:

INSERT INTO `ftpd` (`User`, `status`, `Password`, `Uid`, `Gid`, `Dir`, `ULBandwidth`, `DLBandwidth`, `comment`, `ipaccess`, `QuotaSize`, `QuotaFiles`) VALUES ('richard', '1', MD5('yourpass'), '500', '500', '/var/www', '100', '100', '', '*', '0', '0');

Note: "yourpass" must be the same as the Unix password or it won't work.
I use Quotafiles and QuotaSize 0 wich means theres no limit for number of files nor size.
Upload and Download are limited to 100Kb
Uid and Gid = 500 it's technician user ID and ftp group ID

4:

You're ready to go... NOT!

Open your TCP ports 21 and TCP range 30000 to 50000 (PASV) in your firewall

Now yes, you're all set... NOT!, you need to restart pure-ftpd

# /etc/init.d/pure-ftpd restart

It all should work fine now, login with your ftp client using richard/yourpass

You should also check linux chmod command that sets files and folders attributes and access. In this example you can use:
# chmod 666 /var/www

hope it helped.