Logicalwebhost Cheatsheet

apt-get install proftpd-mod-mysql phpmyadmin
  -> standalone (not inet.d)
groupadd -g 2001 ftpgroup
useradd -u 2001 -s /bin/false -d /bin/null -c "proftpd user" -g ftpgroup ftpuser

mysql -u root -p
 
CREATE DATABASE ftp;
GRANT SELECT, INSERT, UPDATE, DELETE ON ftp.* TO 'proftpd'@'localhost' IDENTIFIED BY 'password';
GRANT SELECT, INSERT, UPDATE, DELETE ON ftp.* TO 'proftpd'@'localhost.localdomain' IDENTIFIED BY 'password';
FLUSH PRIVILEGES;
 
USE ftp;
 
CREATE TABLE ftpgroup (
groupname varchar(16) NOT NULL default '',
gid smallint(6) NOT NULL default '5500',
members varchar(16) NOT NULL default '',
KEY groupname (groupname)
) ENGINE=MyISAM COMMENT='ProFTP group table';
 
CREATE TABLE ftpquotalimits (
name varchar(30) default NULL,
quota_type enum('user','group','class','all') NOT NULL default 'user',
per_session enum('false','true') NOT NULL default 'false',
limit_type enum('soft','hard') NOT NULL default 'soft',
bytes_in_avail bigint(20) unsigned NOT NULL default '0',
bytes_out_avail bigint(20) unsigned NOT NULL default '0',
bytes_xfer_avail bigint(20) unsigned NOT NULL default '0',
files_in_avail int(10) unsigned NOT NULL default '0',
files_out_avail int(10) unsigned NOT NULL default '0',
files_xfer_avail int(10) unsigned NOT NULL default '0'
) ENGINE=MyISAM;
 
CREATE TABLE ftpquotatallies (
name varchar(30) NOT NULL default '',
quota_type enum('user','group','class','all') NOT NULL default 'user',
bytes_in_used bigint(20) unsigned NOT NULL default '0',
bytes_out_used bigint(20) unsigned NOT NULL default '0',
bytes_xfer_used bigint(20) unsigned NOT NULL default '0',
files_in_used int(10) unsigned NOT NULL default '0',
files_out_used int(10) unsigned NOT NULL default '0',
files_xfer_used int(10) unsigned NOT NULL default '0'
) ENGINE=MyISAM;
 
CREATE TABLE ftpuser (
id int(10) unsigned NOT NULL auto_increment,
userid varchar(32) NOT NULL default '',
passwd varchar(32) NOT NULL default '',
uid smallint(6) NOT NULL default '5500',
gid smallint(6) NOT NULL default '5500',
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)
) ENGINE=MyISAM COMMENT='ProFTP user table';
 
quit;

vi /etc/proftpd/modules.conf
  LoadModule mod_sql.c <- uncomment
  LoadModule mod_sql_mysql.c <- uncomment
  LoadModule mod_quotatab_sql.c <- uncomment
vi /etc/proftpd/proftpd.conf
  UseIPv6                         off <- change to off
  #<IfModule mod_quotatab.c> <- comment out
  #QuotaEngine off <- comment out
  #</IfModule> <- comment out
  Include /etc/proftpd/sql.conf <- uncomment this

vi /etc/proftpd/sql.conf
  DefaultRoot ~
 
  SQLBackend              mysql
  # The passwords in MySQL are encrypted using CRYPT
  SQLAuthTypes            Plaintext Crypt
  SQLAuthenticate         users groups
 
  # used to connect to the database
  # databasename@host database_user user_password
  SQLConnectInfo  ftp@localhost proftpd password <- change this password to what you actually used
 
  # Here we tell ProFTPd the names of the database columns in the "usertable"
  # we want it to interact with. Match the names with those in the db
  SQLUserInfo     ftpuser userid passwd uid gid homedir shell
 
  # Here we tell ProFTPd the names of the database columns in the "grouptable"
  # we want it to interact with. Again the names match with those in the db
  SQLGroupInfo    ftpgroup groupname gid members
 
  # set min UID and GID - otherwise these are 999 each
  SQLMinID        500
 
  # create a user's home directory on demand if it doesn't exist
  CreateHome on
 
  # Update count every time user logs in
  SQLLog PASS updatecount
  SQLNamedQuery updatecount UPDATE "count=count+1, accessed=now() WHERE userid='%u'" ftpuser
 
  # Update modified everytime user uploads or deletes a file
  SQLLog  STOR,DELE modified
  SQLNamedQuery modified UPDATE "modified=now() WHERE userid='%u'" ftpuser
 
  # User quotas
  # ===========
  QuotaEngine on
  QuotaDirectoryTally on
  QuotaDisplayUnits Mb
  QuotaShowQuotas on
 
  SQLNamedQuery get-quota-limit SELECT "name, quota_type, per_session, limit_type, bytes_in_avail, bytes_out_avail, bytes_xfer_avail, files_in_avail, files_out_avail, files_xfer_avail FROM ftpquotalimits WHERE name = '%{0}' AND quota_type = '%{1}'"
 
  SQLNamedQuery get-quota-tally SELECT "name, quota_type, bytes_in_used, bytes_out_used, bytes_xfer_used, files_in_used, files_out_used, files_xfer_used FROM ftpquotatallies WHERE name = '%{0}' AND quota_type = '%{1}'"
 
  SQLNamedQuery update-quota-tally UPDATE "bytes_in_used = bytes_in_used + %{0}, bytes_out_used = bytes_out_used + %{1}, bytes_xfer_used =   bytes_xfer_used + %{2}, files_in_used = files_in_used + %{3}, files_out_used = files_out_used + %{4}, files_xfer_used = files_xfer_used + %{5} WHERE name = '%{6}' AND quota_type = '%{7}'" ftpquotatallies
 
  SQLNamedQuery insert-quota-tally INSERT "%{0}, %{1}, %{2}, %{3}, %{4}, %{5}, %{6}, %{7}" ftpquotatallies
 
  QuotaLimitTable sql:/get-quota-limit
  QuotaTallyTable sql:/get-quota-tally/update-quota-tally/insert-quota-tally
 
  RootLogin off
  RequireValidShell off
/etc/init.d/proftpd restart

mysql -u root -p
use ftp;
INSERT INTO `ftpgroup` (`groupname`, `gid`, `members`) VALUES ('ftpgroup', 2001, 'ftpuser');
INSERT INTO `ftpquotalimits` (`name`, `quota_type`, `per_session`, `limit_type`, `bytes_in_avail`, `bytes_out_avail`, `bytes_xfer_avail`, `files_in_avail`, `files_out_avail`, `files_xfer_avail`) VALUES ('exampleuser', 'user', 'true', 'hard', 15728640, 0, 0, 0, 0, 0);
INSERT INTO `ftpuser` (`id`, `userid`, `passwd`, `uid`, `gid`, `homedir`, `shell`, `count`, `accessed`, `modified`) VALUES (1, 'exampleuser', 'password', 2001, 2001, '/home/www.whateverdomain.com', '/sbin/nologin', 0, '', '');
quit;