Virtual Hosting With Proftpd And MySQL (Incl. Quota) - Page 2

Do you like HowtoForge? Please consider supporting us by becoming a subscriber.
Submitted by falko (Contact Author) (Forums) on Sat, 2005-10-15 14:25. ::

4 Configure Proftpd

Open /etc/proftpd.conf and add the following lines to it:

DefaultRoot ~


# 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


# 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
SQLHomedirOnDemand 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

Make sure that you replace the string password with the real password for the MySQL user proftpd in the line SQLConnectInfo!

Restart Proftpd:

/etc/init.d/proftpd restart

Martin Mrajca has sent me these lines that you can add to /etc/proftpd.conf so that Proftpd sends you a banner with used / available space after LIST:

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."

Don't forget to restart Proftpd afterwards if you add these lines!

 

5 Populate The Database And Test

To populate the database you can use the MySQL shell:

mysql -u root -p
USE ftp;

First we create an entry in the table ftpgroup. It contains the groupname, the groupid and the username of the ftp group/user we created at the end of step two (replace the groupid appropriately if you use another one than 2001):

INSERT INTO `ftpgroup` (`groupname`, `gid`, `members`) VALUES ('ftpgroup', 2001, 'ftpuser');

Now we are done with the table ftpgroup. We do not have to create further entries here. Whenever you create a new virtual ftp user, you do this in the tables ftpquotalimits and ftpuser. So let us create our first user (we are still on the MySQL shell):

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', 'secret', 2001, 2001, '/home/www.example.com', '/sbin/nologin', 0, '', '');
quit;

(Do not forget to replace the groud- and userid 2001 appropriately in the last INSERT statement if you are using other values than in this tutorial!)

Now open your FTP client program on your work station (something like WS_FTP or SmartFTP if you are on a Windows system) and try to connect. As hostname you use server1.example.com (or the IP address of the system), the username is exampleuser, and the password is secret.

If you are able to connect - congratulations! If not, something went wrong.

Now, if you run

ls -l /home

you should see that the directory /home/www.example.com (exampleuser's home directory) has been automatically created, and it belongs to ftpuser and ftpgroup (the user/group we created at the end of step two).

To leave the MySQL shell, type

quit;

up

Copyright © 2005 Falko Timme
All Rights Reserved.
add comment | view as pdf view as pdf | print: this | all page(s) |
Please do not use the comment function to ask for help! If you need help, please use our forum.
Comments will be published after administrator approval.
Two problems on Ubuntu Edgy
Submitted by Lunisneko (registered user) on Sun, 2007-02-25 19:15.

While running through this howto, I noticed two things that needed rectification in Ubuntu Edgy (6.10)

Firstly, you will need to uncomment the PostgreSQL module in /etc/proftpd/modules.conf when using MySQL. There is some silent conflict there that immediately disconnects users without even attempting to connect to MySQL when the PostgreSQL module is loaded alongside the PostgreSQL module.

Secondly,  there is the following warning:

 "SQLAuthenticate: use of * in SQLAuthenticate has been deprecated.  Use AuthOrder for setting authoritativeness"

 To rectify this, simply comment out that line, and add a line like the following:

AuthOrder mod_sql.c

Which will only allow logins for users in the MySQL database. See this link on AuthOrder for more information:

http://www.proftpd.org/docs/directives/linked/config_ref_AuthOrder.html

Great HowTO, by the way :) 

reply | view as pdf view as pdf
User quotas banner for the LIST command
Submitted by DavidW (registered user) on Wed, 2006-10-18 16:03.

Martin Mrajca's contribution to add a banner with report of the user quota to the LIST command is useful, but his solution suffers from two flaws. If the quota is 0 (signified by bytes_in_avail = 0), it reports nonsense, also it uses more database queries than are necessary.

These two flaws are fixed by using the following instead:

SQLNamedQuery userquota SELECT "IF ((SELECT (@availmbytes:=ROUND((`bytes_in_avail`/1048576),2)) FROM `ftpquotalimits` WHERE `name`='%u') = 0, \"No user quota applies.\", CONCAT(\"User quota: Used \", (SELECT (@usedmbytes:=ROUND((`bytes_in_used`/1048576),2)) FROM `ftpquotatallies` WHERE `name`='%u'), \"MB from \", @availmbytes, \"MB. You have \", ROUND(@availmbytes-@usedmbytes,2), \"MB available space.\"))"

SQLShowInfo   LIST    "226" "%{userquota}"

 

This makes use of MySQL user defined variables to store the results of the two queries, so that the results can be reused. This works for me with MySQL 5.0; I think it should work against MySQL 4.x but I'm not promising.

reply | view as pdf view as pdf
Sponsored Links: Unified Communications: Thoughts, Strategies and Predictions
Join the discussion.
www.seamlessenterprise.com

IP Convergence
Integrate your wireless and wireline networks.
Learn how from the experts at Sprint.
www.seamlessenterprise.com

Wireless & Wireline Integration
Thoughts, strategies and solutions: join the discussion
www.seamlessenterprise.com

Unified Communications 2009
Join the Discussion. Now.
www.seamlessenterprise.com