ubuntu server - pure-ftpd-mysql installation

requirement before install: LAMP installed in your system.

install pure-ftpd-mysql:
	sudo apt-get install pure-ftpd-mysql

create ftpgroup and ftpuser with 2001 id, change it if it is not available on your system:
	sudo groupadd -g 2001 ftpgroup
	sudo useradd -u 2001 -s /bin/false -d /bin/null -c "pureftpd user" -g ftpgroup ftpuser

----------------------------------------------------------------------------------------------

login to phpmyadmin and run the sql script below to create the pureftpd database and grant it with privileges:

	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;

----------------------------------------------------------------------------------------------

select the pureftpd database in the phpmyadmin and run the script below to create the ftpd table that will store the ftp user account:

	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;

----------------------------------------------------------------------------------------------

edit pure-ftpd-mysql config file:
	sudo nano /etc/pure-ftpd/db/mysql.conf

make sure that the value below is the same and uncommented. change MYSQLUser and MYSQLPassword value with the actual mysql user account in your system:

	MYSQLSocket      /var/run/mysqld/mysqld.sock
	MYSQLUser       pureftpd
	MYSQLPassword   ftpdpass
	MYSQLDatabase   pureftpd
	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")

--------------------------------------------------------------------------------------------

This 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:
	sudo nano /etc/pure-ftpd/conf/ChrootEveryone
put yes text value on the file and save.

This will make PureFTPd create a user's home directory if the home directory does not exist yet when the user login:
	sudo nano /etc/pure-ftpd/conf/CreateHomeDir
put yes text value on the file and save.

restart pure-ftpd:
	sudo /etc/init.d/pure-ftpd-mysql restart

--------------------------------------------------------------------------------------------

Whenever you want to create a new user, you have to create an entry in the table ftpd so I will explain the columns of this table here:

a. User: The name of the virtual PureFTPd user (e.g. exampleuser).
b. status: 0 or 1. 0 means the account is disabled, the user cannot login.
c. Password: The password of the virtual user. Make sure you use MySQL's MD5 function to save the password encrypted as an MD5 string:
d. UID: The userid of the ftp user you created at the end of step two (e.g. 2001).
e. GID: The groupid of the ftp group you created at the end of step two (e.g. 2001).
f. Dir: The home directory of the virtual PureFTPd user (e.g. /home/www.example.com). If it does not exist, it will be created when the new user logs in the first time via FTP. The virtual user will be jailed into this home directory, i.e., he cannot access other directories outside his home directory.
g. ULBandwidth: Upload bandwidth of the virtual user in KB/sec. (kilobytes per second). 0 means unlimited.
h. DLBandwidth: Download bandwidth of the virtual user in KB/sec. (kilobytes per second). 0 means unlimited.
i. comment: You can enter any comment here (e.g. for your internal administration) here. Normally you leave this field empty.
j. ipaccess: Enter IP addresses here that are allowed to connect to this FTP account. * means any IP address is allowed to connect.
k. QuotaSize: Storage space in MB (not KB, as in ULBandwidth and DLBandwidth!) the virtual user is allowed to use on the FTP server. 0 means unlimited.
l. QuotaFiles: amount of files the virtual user is allowed to save on the FTP server. 0 means unlimited.

--------------------------------------------------------------------------------------------

you can create user using phpmyadmin and enter user account information manually or run the script below:

	INSERT INTO `ftpd` (`User`, `status`, `Password`, `Uid`, `Gid`, `Dir`, `ULBandwidth`, `DLBandwidth`, `comment`, `ipaccess`, `QuotaSize`, `QuotaFiles`) VALUES ('exampleuser', '1', MD5('secret'), '2001', '2001', '/home/www.example.com', '100', '100', '', '*', '50', '0');

--------------------------------------------------------------------------------------------