Saturday, 21 May 2011

Connect Pure-FTPd with MySQL and manage users

Connect with MySQL
Login to MySQL and create a new database.
mysql> CREATE DATABASE pureftpd;

Create a new MySQL user for Pure-FTPd
mysql> GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP ON pureftpd.* TO 'pureftpd'@'localhost' IDENTIFIED BY 'yourPureFTPsecret';
mysql> GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP ON pureftpd.* TO 'pureftpd'@'localhost.localdomain' IDENTIFIED BY 'yourPureFTPSecret';
mysql> FLUSH PRIVILEGES;

Create the (only one) table that stores the user information
mysql> USE pureftpd;
mysql> 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;

Edit /etc/pure-ftpd/pure-ftpd.conf (with vi or whatever you like)
These lines should look like this:
ChrootEveryone              yes
NoAnonymous                 yes
MySQLConfigFile              /etc/pure-ftpd/pureftpd-mysql.conf
CreateHomeDir               yes
Actually, the one resticting Anonymous is not really about connecting Pure-FPTd and MySQL, it’s just how I want the server :)

Edit /etc/pure-ftpd/pureftpd-mysql.conf
These lines should look like this:
MYSQLSocket      /var/lib/mysql/mysql.sock
MYSQLUser       pureftpd
MYSQLPassword   yourPureFTPSecret
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")
The syntax is the same for all of them after FROM, “FROM ftpd WHERE User="\L"AND status="...”.

Create FTP User
mysql> USE pureftpd;
mysql> INSERT INTO `ftpd` (`User`, `status`, `Password`, `Uid`, `Gid`, `Dir`, `ULBandwidth`, `DLBandwidth`, `comment`, `ipaccess`, `QuotaSize`, `QuotaFiles`) VALUES ('varia', '1', MD5('yourSecretToChange'), '2001', '2001', '/home/varia', '100', '100', '', '*', '50', '0');
You will manage users from MySQL. I plan to develop a simple script to manage users but I just don’t have the time right now. I don’t know if anybody did it already. If you know about such a script, please let me know. Thank you!

This tutorial is based on information I learned earlier from different sourcers but mostly from here. So, thank you, Falko Timme!

Finally, to make it cool, let’s Configure Pure-FTPd welcome message!

No comments:

Post a Comment