Admin: /* HOW TO */

2009-10-18T20:46:09Z

HOW TO

Admin: /* Presumptions */

2009-10-11T10:20:26Z

Presumptions

Admin at 19:36, 1 October 2009

2009-10-01T19:36:39Z

Admin: /* HOW TO */

2009-10-01T18:43:37Z

HOW TO

Admin: New page: ==Presumptions== I assume you already know how to use your distro's package manager .. and you know how to use it.. I also assume you know your way around your distro ... ==HOW TO==
2009-06-25T18:42:02Z

New page: ==Presumptions== I assume you already know how to use your distro's package manager .. and you know how to use it.. I also assume you know your way around your distro ... ==HOW TO== <sour...

New page
==Presumptions==
I assume you already know how to use your distro's package manager .. and you know how to use it.. I also assume you know your way around your distro ...

==HOW TO==
<source lang="bash">
groupadd -g 2001 ftpgroup
useradd -u 2001 -s /bin/false -d /bin/null -c "proftpd user" -g ftpgroup ftpuser
mysql -u root -p
</source>
<source lang="mysql">
CREATE DATABASE `ftp`;
GRANT SELECT, INSERT, UPDATE, DELETE ON ftp.* TO 'proftpd'@'localhost' IDENTIFIED BY 'P4ssw0rd';
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)
) TYPE=MyISAM;

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 int(10) unsigned NOT NULL default '0',
bytes_out_avail int(10) unsigned NOT NULL default '0',
bytes_xfer_avail int(10) 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'
) TYPE=MyISAM;

CREATE TABLE ftpquotatallies (
name varchar(30) NOT NULL default '',
quota_type enum('user','group','class','all') NOT NULL default 'user',
bytes_in_used int(10) unsigned NOT NULL default '0',
bytes_out_used int(10) unsigned NOT NULL default '0',
bytes_xfer_used int(10) 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'
) TYPE=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)
) TYPE=MyISAM;
QUIT;
</source>
Now edit /etc/proftpd.conf and add the following lines at the end of the file
<source lang="bash">
LoadModule mod_sql.c
LoadModule mod_sql_mysql.c
LoadModule mod_quotatab.c
LoadModule mod_quotatab_sql.c
DefaultRoot ~
SQLAuthTypes Plaintext Crypt
SQLAuthenticate users* groups*
SQLConnectInfo ftp@localhost proftpd P4ssw0rd
SQLUserInfo ftpuser userid passwd uid gid homedir shell
SQLGroupInfo ftpgroup groupname gid members
SQLMinID 500
SQLHomedirOnDemand on
SQLLog PASS updatecount
SQLNamedQuery updatecount UPDATE "count=count+1, accessed=now() WHERE userid='%u'" ftpuser
SQLLog STOR,DELE modified
SQLNamedQuery modified UPDATE "modified=now() WHERE userid='%u'" ftpuser
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
</source>
Feel free to add these lines to have a banner with used space and stuff when you do ls...
<source lang="bash">
SQLNamedQuery gettally SELECT "ROUND((bytes_in_used/1048576),2) FROM ftpquotatallies WHERE name='%u'"
SQLNamedQuery getlimit SELECT "ROUND((bytes_in_avail/1048576),2) FROM ftpquotalimits WHERE name='%u'"
SQLNamedQuery getfree SELECT "ROUND(((ftpquotalimits.bytes_in_avail-ftpquotatallies.bytes_in_used)/1048576),2) FROM ftpquotalimits,ftpquotatallies WHERE ftpquotalimits.name = '%u' AND ftpquotatallies.name = '%u'"
SQLShowInfo LIST "226" "Used %{gettally}MB from %{getlimit}MB. You have %{getfree}MB available space."
</source>
Don't forget to restart proftpd after modifying the conf ! After this .. insert datas into sql and test if it works
<source lang="bash">
mysql -u root -p
</source>
<source lang="mysql">
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`) VALUES ('blahblah', 'user', 'true', 'hard', 15728640);
INSERT INTO `ftpuser` (`userid`, `passwd`, `uid`, `gid`, `homedir`, `shell`) VALUES ('blahblah', 'P4ssw0rd', 2001, 2001, '/home/vftp/blahblah', '/sbin/nologin');
INSERT INTO `ftp`.`ftpquotatallies` (`name`, `quota_type`) VALUES ('blahblah', 'user');
</source>

==Problems==
If you have any problems .. run
<source lang="bash">
proftpd -n -d 5 # and see what's going on ..
</source>

==SQL Info==
ftpuser Table:

The important columns are these (the others are handled by MySQL or Proftpd automatically, so do not fill these manually!):

userid: The name of the virtual Proftpd user (e.g. exampleuser).

passwd: The unencrypted (i.e., clear-text) password of the user.

uid: The userid of the ftp user you created at the end of step two (e.g. 2001).

gid: The groupid of the ftp group you created at the end of step two (e.g. 2001).

homedir: The home directory of the virtual Proftpd user (e.g. /home/vftp/user). 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.

shell: It is ok if you fill in /sbin/nologin here by default.

ftpquotalimits Table:

The important columns are these (the others are handled by MySQL or Proftpd automatically, so do not fill these manually!):

name: The name of the virtual Proftpd user (e.g. exampleuser).

quota_type: user or group. Normally, we use user here.

per_session: true or false. true means the quota limits are valid only for a session. For example, if the user has a quota of 15 MB, and he has uploaded 15 MB during the current session, then he cannot upload anything more. But if he logs out and in again, he again has 15 MB available. false means, that the user has 15 MB at, no matter if he logs out and in again.

limit_type: hard or soft. A hard quota limit is a never-to-exceed limit, while a soft quota can be temporarily exceeded. Normally you use hard here.

bytes_in_avail: Upload limit in bytes (e.g. 15728640 for 15 MB). 0 means unlimited.

bytes_out_avail: Download limit in bytes. 0 means unlimited.

bytes_xfer_avail: Transfer limit in bytes. The sum of uploads and downloads a user is allowed to do. 0 means unlimited.

files_in_avail: Upload limit in files. 0 means unlimited.

files_out_avail: Download limit in files. 0 means unlimited.

files_xfer_avail: Tranfer limit in files. 0 means unlimited.

The ftpquotatallies table is used by Proftpd internally to manage quotas so you do not have to make entries there!

==Final words==
You'd better know how to google and know some sql !

[[Category: MySQL]]
[[Category: ProFTPD]]

Virtual ProFTPD with MySQL and Quota - Revision history

Admin: /* HOW TO */

Admin: /* Presumptions */

Admin at 19:36, 1 October 2009

Admin: /* HOW TO */