Virtual Hosting : PureFTPd Dan MySQL Di Freebsd

September 5, 2008 tunggul
Off
Network

Di tulisan ini saya mencoba membuat sebuah virtual hosting di Freebsd dengan PureFTPd server dimana menggunakan virtual users dari MySQL database dari pada menggunakan system users.
Dari sis performa lebih baik dan bisa menampung lebih banyak ftp users di satu mesin. Disini juga saya sertakan bagaimana mensetting quota dan upload/download bandwidth limits. Passwords user akan disimpan dengan encrypted MD5 strings di database.

Untuk administrasi MySQL database bisa digunakan phpMyAdmin, asumsi disini Mysql, apache dan phpmyadmin sudah terinstall di server.

1. Install Pure-ftpd dengan aktifkan “Support for users in MySQL database”

#cd /usr/ports/ftp/pure-ftpd
#make install clean

2. Membuat database dan tabel untuk pure-ftpd

#mysql -u root -p

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;

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;

3. Konfigure pure-ftpd
edit file pure-ftpd.conf , edit bagian ini :

[…]
ChrootEveryone              yes
[…]
MySQLConfigFile               /usr/local/etc/pure-ftpd/pureftpd-mysql.conf
[…]
CreateHomeDir               yes
[…]

edit file pureftpd-mysql.conf, tambahkan baris berikut :

MYSQLSocket      /tmp/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”)

4. Running dan test
jalankan pure-ftpd dan kemudian di coba membuat user exampleuser dengan status 1 (berarti ftp account is active), password secret (disimpan encrypted MySQL’s MD5 ), UID dan GID 2001 , home directory /home/www.example.com, upload dan download bandwidth = 100 KB/sec. (kilobytes per second), dan quota = 50 MB . melalui query sql berikut :

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’);

kalau sudah tinggal test login pake ftp client, kalau tidak ada error dan bisa login berarti sukses deh 🙂

Network
Network