ProFTPd PHP/MySQL Administration ================================ Updated: 2005-10-15 Authors: Daniel Sundberg , mail this email for support Project website: http://ppmy.sf.net This software is distributed under the terms of the GNU Public License (GPL). See the COPYING file which you should have received with this software for more information. HOWTO ===== Table of contents -------------------- 1 Introduction 2 Install the php files 3 Configure PPMy 4 Set MySQL database 4.1 Start the mysql client 4.2 Create database 4.3 Create tables 4.4 User priviledges 5 Create users and groups 5.1 Enter the website 5.2 Create a group 5.3 Create at least one user 6 Configure proftpd 6.1 Compile proftpd 6.2 Configure proftpd on the local machine 6.3 Configure proftpd on the other machine 6.4 User home directories 7 PPMy on Windows XP 7.1 Will PPMy work on Windows? 8 Additional resources 8.1 Proftpd documentation 8.2 MySQL passwords 8.3 Additional resources 8.4 Contact the author 1. Introduction --------------- This is a short which will explain how you can set up a ProFTPd server with the user database in a MySQL database. I will provide an example on how to set up the php administration tool on one machine (the machine where you have got the database) and how you can configure two ftpservers to use this database. This HOWTO assumes that you already has a properly setup PHP/MySQL installation. If you do not have PHP and/or MySQL installed you can find information on how to do this on : http://www.php.net/manual/en/installation.php Please make sure you have a working PHP/MySQL installation before you ask me for support on my program. Some Linux distributions have precompiled packages with apache, php, proftpd and mysql. For example, on my Ubuntu Linux system, setting up apache, php, mysql and proftpd with the mysql module enabled is just a matter of typing a few commands following the tutorial on: https://wiki.ubuntu.com/ApacheMySQLPHP?highlight=%28php%29%7C%28apache%29%7C%28mysql%29 You should also make sure you have an installation of proftpd with the mysql module compiled, and you should make sure that you know which configuration file to edit. You should also backup your old config file before you start working with my example configurations. You can find how to install mysql on the following urls: http://www.mysql.com/doc/en/Installing.html http://www.mysql.com/doc/en/Linux-RPM.html An idiot proof installation howto for apache, php and mysql can be found at: http://www.php.net/manual/en/install.unix.php 2. Install the PHP-files --------------------- Unpack the php-files: tar xvzf ppmy-0.3.tar.gz Then you should copy this folder to a directory where it can be found by your web server. On my system that would be: cd ppmy-0.3 mkdir /usr/local/apache/htdocs/proftp-admin cp *.php /usr/local/apache/htdocs/proftpd-admin Change /usr/local/apache/htdocs to the path where you have put your website. You may also change "proftpd-admin" to something else if you want. 3. Configure PPMy ----------------- Edit /usr/local/apache/htdocs/proftpd-admin/ftpcfg.php in your favourite editor. Change the variables to appropriate values. You probably want to check: $cfg->db_host="localhost"; $cfg->db_name="proftpd"; $cfg->db_user="proftpd"; $cfg->db_pass="proftpd"; $cfg->homedir_prefix="/home/ftp/user"; If you can create your own database you can leave these fields as they are. If you can't create your own database but only tables, change db_name to the name of your database. If you want to use home directories for your users you should change the homedir prefix to something that exists on your system. You should also make sure that this directory is writable by the user running the proftpd process. This is specified by the proftpd.conf configuration file. 4. Setup MySQL Database ----------------------- You need one database and at least two tables in that database. If you do not have access to create a database you can skip the first. 4.1 Start mysql client I use the following command to start the mysql client: /usr/local/mysql/bin/mysql -umyuser -p When you are prompted for a password you simply enter your password. If you have got a fresh install of mysql you might have to issue the following command: /usr/local/mysql/bin/mysql -uroot to start the mysql client. See the mysql manual for information on how to change the root password. 4.2 Create database mysql> CREATE DATABASE proftpd; If you do not want to create a database just do: mysql> use [DATABASE]; where database is the database you want to use or mysql> use proftpd; if you created the database above. 4.3 Create tables for users and groups mysql> CREATE TABLE users ( userid varchar(10) NOT NULL default '', uid int(10) unsigned NOT NULL auto_increment, gid int(10) unsigned NOT NULL default '0', passwd varchar(255) NOT NULL default '', homedir varchar(255) NOT NULL default '', description varchar(255) NOT NULL default '', disabled int(10) unsigned NOT NULL default '0', shell varchar(20) NOT NULL default '', expires datetime NOT NULL default '0000-00-00 00:00:00', email varchar(255) NOT NULL default '', name varchar(255) NOT NULL default '', ul_bytes bigint(20) NOT NULL default '0', dl_bytes bigint(20) NOT NULL default '0', login_count bigint(20) NOT NULL default '0', dl_count bigint(20) NOT NULL default '0', ul_count bigint(20) NOT NULL default '0', last_login datetime default NULL, PRIMARY KEY (uid) ) TYPE=MyISAM; mysql> CREATE TABLE groups ( groupid varchar(10) NOT NULL default '', gid int(10) unsigned NOT NULL auto_increment, members varchar(255) NOT NULL default '', PRIMARY KEY (gid) ) TYPE=MyISAM; mysql> INSERT INTO users (uid) VALUES (9999); mysql> INSERT INTO groups (gid) VALUES (9999); mysql> DELETE FROM users WHERE uid=9999; mysql> DELETE FROM groups WHERE gid=9999; The last four lines is there to make the default uid and gid 10000. This might be a good idea since you do not want your ftp users to conflict with your system users (although there might be situations when you want to have a ftp user with the same uid as a system user). Also, if uid and gid is below 1000 you will not be able to see the uid of your ftp users when they are logged in. This script is also avaliable in the file "tables.sh" included in this source distribution. /usr/local/mysql/bin/mysql -u root < tables should accomplish the same as the above commands (not tested). 4.4 User priviledges You should also give access for the proftpd mysql database user to read and write these tables: mysql> GRANT ALL ON users TO proftpd@localhost IDENTIFIED BY 'proftpd'; mysql> GRANT ALL ON groups TO proftpd@localhost IDENTIFIED BY 'proftpd'; This creates a database user named proftpd which can connect from localhost with password "proftpd". Since we want to use this database from another system too we want to add another host to this user: mysql> GRANT ALL ON users TO proftpd@server1.something.com IDENTIFIED BY 'proftpd'; mysql> GRANT ALL ON groups TO proftpd@server1.something.com IDENTIFIED BY 'proftpd'; 5. Create users and groups ---------------------------- 5.1 Enter the website Now it is time to create at least one group using the webb based admin utility. Point your webb browser to http://localhost/proftpd-admin/index.php or whatever host you just installed the php files on. 5.2 Create a group The first thing you will see is an empty user table. Before you go ahead and create some users you should create at least one group. If you want to setup ftp servers on two computer three groups might be useful. One group with exclusive users for each machine and one group in which we place users that should have access to both server. In this way we can add more servers later and automaticly give these priviledged users access to the new servers. 5.3 Create at least one user Click "Add user" on the Index page and fill in at least username and password. 6. Configure ProFTPd -------------------- 6.1 Compile ProFTPd or install packages from your favourite distribution. From README.mod_sql in the proftpd source distribution: ./configure --with-modules=mod_sql:mod_sql_mysql \ --with-includes=/usr/local/include \ --with-libraries=/usr/local/lib/mysql make Now it should compile and everything should install just fine. 6.2 Configure ProFTPd on the local machine. Of course you need some things in your proftpd.conf. The most important line in the configuration is probably "SQLConnectInfo". SQLConnectInfo proftpd@localhost proftpd proftpd "proftpd@localhost" tells proftpd that it should connect to the MySQL-server on the host "localhost" with username and password "proftpd". "proftpd" is the username you created for the database in 4.4. Change "localhost" to the appropriate hostname. The second and third argument is the password and database. See proftpd reference documentation for more information. Since you have the database password in clear text it might be a good idea to limit readaccess for the configuration file on a multiuser system. I have the following MySQL-related stuff in my proftpd.conf: Order deny,allow AllowGroup ftpserver1 AllowGroup ftpall SQLConnectInfo proftpd@localhost proftpd proftpd SQLAuthenticate users groups SQLAuthTypes Crypt Backend SQLDefaultHomedir ~ SQLUserInfo users userid passwd uid gid homedir shell SQLGroupInfo groups groupid gid members SQLHomedirOnDemand on SQLLogFile /var/log/proftpd.sql_log # Count number of logins for each user SQLLog PASS logincount SQLNamedQuery logincount UPDATE "login_count=login_count+1 WHERE userid='%u'" users # Save last login SQLLog PASS lastlogin SQLNamedQuery lastlogin UPDATE "last_login=now() WHERE userid='%u'" users # Count number of bytes/files downloaded and uploaded SQLLog RETR dlbytescount SQLNamedQuery dlbytescount UPDATE "dl_bytes=dl_bytes+%b WHERE userid='%u'" users SQLLog RETR dlcount SQLNamedQuery dlcount UPDATE "dl_count=dl_count+1 WHERE userid='%u'" users SQLLog STOR ulbytescount SQLNamedQuery ulbytescount UPDATE "ul_bytes=ul_bytes+%b WHERE userid='%u'" users SQLLog STOR ulcount SQLNamedQuery ulcount UPDATE "ul_count=ul_count+1 WHERE userid='%u'" users # Do not let disabled or expired users to login SQLUserWhereClause "disabled!=1 and (NOW()<=expires or expires=-1)" # Do not require a valid shell, this might be a good thing to turn on for SQL-users # This might simplify the configuration a little bit RequireValidShell off 6.3 Configure proftpd on an additional system using the same user database. On the second system you might want to replace a few rows: Order deny,allow AllowGroup ftpserver2 AllowGroup ftpall SQLConnectInfo proftpd@server1 proftpd proftpd 6.4 User homedir support I you are setting up a web server with user accounts you might want a home directory for each user. When you create a user you can specify a home directory prefix for the user. If the user you create are named "dsg" and the home directory prefix is "/home/ftp/users" the resulting home directory will be "/home/ftp/users/dsg". To automatically let proftpd create this directory you could use the following directive in the proftpd configuration file: SQLHomedirOnDemand on It is important to remember that for that to work "/home/ftp/user" must be writable by the user that owns the proftpd process. If you do not want to use custom home directories for your user, simply use "SQLHomedirOnDemand off" in your proftpd.conf. 7. Will PPMy work on Windows XP? -------------------------------- 7.1 PPMy on Windows I have not yet been able to make PPMy work on my Windows XP system, however, I have taken a few steps towards this goal. The first thing we need is a fully funtional apache+php+mysql installation. This was pretty easy, I just had to download and install the wamp package (http://www.wampserver.com/). Then I had to set "old_passwords" in my wamp config file located in c:\windows. Search for "wamp" in c:\windows using windows search tool to find the appropriate configuration file. Then you have to install cygwin to be able to run proftpd. Proftpd is included in the cygwin distribution but the sql module is not compiled so we have to compile proftpd from source. To be able to compile proftpd with mysql support we need to have mysql development packaged installed. Mysql can be compiled in cygwin so this is not a big problem. I choose to install mysql with /usr/local/mysql as installtion prefix. To compile proftpd I used "./configure --prefix=/usr/local/proftpd --with-includes=/usr/local/mysql/include". However, I also had to make some changes to the "Make.rules" file that are created by the configure script. I had to set the following variales in the beginning of the file: DFLAGS=-Llib -L/usr/local/mysql/lib/mysql LIBS=-lsupp -lmysqlclient -lz -lcrypt -lm The proftpd compiled without problems after this modification. When the ftp daemon is started an ordinary system user can login without problems but I do not manage to login with a user added to the mysql database. Proftpd says the user is not found but according to the mysql log the user is indeed found in the database. For some reason I can not get proftpd to show me exactely where things get wrong. Maybe I have to run proftpd in a debugger to find out where things go wrong. 8. Additional resources and trouble shooting -------------------------------------------- 8.1 Proftpd documentation There are not very much documentation on SQL-connectivity on the ProFTPd website but there are excellent documentation in the file README.mod_sql included in the proftpd source distribution. 8.2 MySQL passwords MySQL password errors. The default length of the encrypted password is longer in newer versions of MySQL. In some php versions this has not been fixed and you will get an error when using PPMy (for example this happened on my Windows XP work station). A workaround is to add the directive "old_passwords" at the end of your mysql configuration file (on my ubuntu linux system this file is located in /etc/mysql). 8.3 Useful resources Setting up proftpd with mysql and quota support: http://www.khoosys.net/single.htm?ipg=848 To debug your ftp server you could use the following command in /var/log: tail --follow auth.log messages mysql.err mysql.log proftpd.sql_log xferlog user.log You could also enable logging for the MySQL server by editing /etc/mysql/my.cnf. 8.4 Contact the author I hope this document will help you to get started to use a MySQL database as verification backend to your ftp server. If you have questions or want to improve this program, please send a mail. -- Daniel Sundberg http://www.sf.net/projects/ppmy 2005-10-15