ProFTPD and MySQL

[admin]

เก็บไว้เตือนความจำ

- โหลด package

apt-get install proftpd proftpd-mod-mysql

- สร้าง system user

groupadd -g 3001 ftpgroup
useradd -s /bin/false -d /bin/null -c "proftpd user" --uid 3001 -g ftpgroup ftpuser

- สร้าง database

mysql -u root -p

mysql> CREATE DATABASE proftpddb;
Query OK, 1 row affected (0.00 sec)

mysql> GRANT USAGE ON *.* TO proftpduser@localhost IDENTIFIED BY 'proftpdpassword';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT SELECT, INSERT, UPDATE, DELETE ON proftpddb.* TO 'proftpduser'@'localhost';
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> quit

- สร้าง database table

proftpd-mysql.sql:

=============================
CREATE TABLE ftpgroup (
 groupname varchar(16) NOT NULL default '',
 gid smallint(6) NOT NULL default '3000',
 members varchar(16) NOT NULL default '',
 KEY groupname (groupname)
 ) TYPE=MyISAM COMMENT='ProFTPd group table';

 CREATE TABLE ftpuser (
 id int(10) unsigned NOT NULL auto_increment,
 userid varchar(32) NOT NULL default '',
 passwd varchar(80) NOT NULL default '',
 uid smallint(6) NOT NULL default '3000',
 gid smallint(6) NOT NULL default '3000',
 homedir varchar(255) NOT NULL default '',
 shell varchar(16) NOT NULL default '/sbin/nologin',
 count int(11) NOT NULL default '0',
 accessed datetime NOT NULL default '0000-00-00 00:00:00',
 modified datetime NOT NULL default '0000-00-00 00:00:00',
 PRIMARY KEY (id),
 UNIQUE KEY userid (userid)
 ) TYPE=MyISAM COMMENT='ProFTPd user table';
=============================

mysql -u root -p proftpddb < proftpd-mysql.sql

- First User and Grop

# mysql -u proftpduser -p proftpddb

mysql> INSERT INTO `ftpgroup` (`groupname`, `gid`, `members`) VALUES ('ftpgroup', 3001, 'ftpuser');

mysql> INSERT INTO `ftpuser` (`id`, `userid`, `passwd`, `uid`, `gid`, `homedir`, `shell`, `count`, `accessed`, `modified`) VALUES (1, 'firstuser', PASSWORD('userpassword'), 3001, 3001, '/var/proftp/firstuser', '/sbin/nologin', 0, '', '');

- Create home

# mkdir /var/proftp
# chown ftpuser:ftpgroup /var/proftp
# chmod 775 /var/proftp

- config Proftpd

/etc/proftpd/sql.conf
=================
# Force the use of mysql backend
SQLBackend                      mysql

# The passwords in MySQL are using its own PASSWORD function
SQLAuthTypes                    Backend
SQLAuthenticate                 users* groups*

# details to connect to mysql db
# dbname@host dbuser dbpass
SQLConnectInfo                  proftpddb@localhost proftpduser proftpdpassword

# Let proFTPd know the name of the columns in the user table
# Mind that this need to match the name in you table
SQLUserInfo                     ftpuser userid passwd uid gid homedir shell

# Let proFTPd know the name of the columns in the group table
# we want it to interact with. Again the names match with those in the db
SQLGroupInfo                    ftpgroup groupname gid members

# proftpd will dynamicaly create if the homedir does not yet exist
#SQLHomedirOnDemand              on

# update counter when a user logs in
SQLLog                          PASS updatecount
SQLNamedQuery                   updatecount UPDATE "count=count+1, accessed=now() WHERE userid='%u'" ftpuser

# change modified time anytime a user delete a file or upload one
SQLLog                          STOR,DELE modified
SQLNamedQuery                   modified UPDATE "modified=now() WHERE userid='%u'" ftpuser

=================

/etc/proftpd/proftpd.conf
=================
Include /etc/proftpd/mysql.conf
RootLogin off
RequireValidShell off
DefaultRoot                     ~
=================

/etc/proftpd/modules.conf
=================
LoadModule mod_sql.c
LoadModule mod_sql_mysql.c
=================