Installing ProFTPD with mysql support

Giving users ftp access has always been a security issue. But with ProFTPD and proper configuration, it should be possible to provide ftp access and sleep without worry. ProFTPD comes with database support, which means that you don't even have to create any unix accounts.

Here's a short description of the setup descriped on this page:

The purpose of this setup is to give website owners ftp access to their webhotel. These users will be completely virtual, meaning that you wont have to create any unix users.

When files are uploaded the ftp server will attach a userid and group to them. The user id and group is read from a database. Since the files must be readable by the webserver, the group will always be 'www'. The user id will be a unique number, which is also read from the database. But is it possible to give a file a user id which does not exist as a unix user? Yes, it does not matter to FreeBSD if the user id on a file acually exists. You must however dedicate a range of user ids to proftpd, which will never be used for a unix user.

I have only one IP address for this server, and all domains are therefore namebased in Apaches configuration file. Since the ftp server only cares about the IP address, you can do 'ftp://www.domain.tld' to all the domains/websites after this setup. The only thing which will be different for each ftp user, is their home directory (which will be the directory you have created for their website).

Lets begin :-)

Installing ProFTPD

I'll assume that Apache and MySQL is already installed and running on your server. When you dive in to the ports directory, make sure you choose the proftpd-mysql package, and not just proftp:

cd /usr/ports/ftp/proftpd-mysql/
make install clean distclean

......................................................................
.               Options for proftpd-mysql 1.3.0.r2_3                 .
. .................................................................. .
. .             [ ] IPV6        Use IPv6                           . .
. .             [ ] LDAP        Use LDAP                           . .
. .             [X] MYSQL       Use MySQL                          . .
. .             [ ] POSTGRESQL  Use Postgres                       . .
. .             [X] OPENSSL     Include mod_tls                    . .
. .             [X] QUOTA       Include mod_quota                  . .
. .             [X] IFSESSION   Include mod_ifsession              . .
. .             [X] README      Include mod_readme                 . .
. .             [X] RATIO       Include mod_ratio                  . .
. .             [X] REWRITE     Include mod_rewrite                . .
. .             [X] WRAP        Include mod_wrap                   . .
. .             [ ] RADIUS      Include mod_radius                 . .
. .                                                                . .
. .                                                                . .
. .                                                                . .
......................................................................
.                       [  OK  ]       Cancel                        .
......................................................................

After installation add this line to /etc/rc.conf:

proftpd_enable="YES"

As you probably know, this will ensure that ProFTPD is started automatically when the server is booted.

Dedicating a range of user id's

You must decide on a range of user id's which will be dedicated to ProFTPD and never be used for a unix user. Here is the rules I am following:

The above is just a guideline. You can of course decide on a range of your choice, but don't mess with usernumbers below 200. As you can see I have dedicated the range 2000-2999 to virtual ProFTPD users.

Setup MySQL tables

If you wish to have ProFTPD related data in a seperate database (recommended), create the new database - remember to login to mysql first or use an interface like phpMyAdmin:

create database proftpd;

Create a user which the proftpd daemon will use to connect to the database. The below will give access to all tables in the database, which is fine if you created the proftpd database. But if the tables is in an existing database, make sure that this user only has access to proftpd related tables.

GRANT SELECT , INSERT , UPDATE , DELETE ON `proftpd` . * TO 'proftpd'@'localhost' IDENTIFIED BY 'password';
FLUSH PRIVILEGES;

Remember to replace password with one of your choice.

If you are in a mysql prompt, use this command before you attempt to create the tables:

use proftpd;

Next create the necessary tables - just copy/paste the below into your mysql prompt or use it in your mysql interface (e.g. phpmyadmin):

Note that the gid field in the ftpgroup and ftpuser tables are set to be 80 by default. Change this to reflect the group id of your systems webserver user.

Configure ProFTPD

The configuration file is in /usr/local/etc/proftpd.conf. Open the file and uncomment this line:

DefaultRoot ~

Add this at the end of the file and replace password with the one you chose for the proftpd user (see SQLConnectInfo):

Create an ftp user

Before you can test your setup, let's create an ftp user.:

First create a row in the ftpgroup table. Remember to change the values to reflect your webservers unix user:

INSERT INTO ftpgroup (groupname, gid, members) VALUES ('www', 80, 'www');

You only have to run the above query once. The inserts below is the ones you'll use every time you add a new ftp user:

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 ('username', 'user', 'true', 'hard', 15728640, 0, 0, 0, 0, 0);

INSERT INTO ftpuser (userid, passwd, uid, gid, homedir, shell, count, accessed, modified) VALUES ('username', 'password', 2000, 80, '/usr/local/www/domain.tld', '/sbin/nologin', 0, '', '');

Remember to make any changes to reflect your own settings. Each field of the ftpquotalimits and ftpuser tables are explained at the bottom of this page.

Next time you create an ftp user, you'll set the user id to 2001 and so forth.

If the home directory (homedir) is not created yet, ProFTPD will do this automatically. But if it exist, you must ensure that it has the correct user and group. The following command will set user id to 2000 and group to www recursively:

chown -R 2000:www /usr/local/www/domain.tld

Testing...

Start ProFTPD (you must be root to do this):

In resent versions, the fileextension .sh has been removed, but it works the same

Fire up your favorite ftp client from your desktop and try to ftp to your server, or just type ftp://www.domain.tld into your browser.

ftpuser and ftpquotalimits explained

This is the fields which you'll fill when creating a new user. Don't touch any other fields, since they are handled by MySQL or Proftpd automatically

ftpuser table:

• userid: The name of the virtual Proftpd user (e.g. exampleuser).
• passwd: The unencrypted (i.e., clear-text) password of the user.
• uid: The users id (e.g. 2000, 2001 an so forth).
• gid: The groupid of the systems webserver (e.g. 80).
• homedir: The home directory of the virtual Proftpd user (e.g. /usr/local/www/domain.tld). 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. Ftp users do not need a shell.

ftpquotalimits table:

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

Troubleshooting

If you need to debug, run the daemon in the foreground as shown here. I will output information to your screen:

#proftpd -n -d 9
- mod_tls/2.1.2: using OpenSSL 0.9.7e-p1 25 Oct 2004
- SQLAuthenticate: use of * in SQLAuthenticate has been deprecated. Use AuthOrder for setting authoritativenes
- SQLAuthenticate: use of * in SQLAuthenticate has been deprecated. Use AuthOrder for setting authoritativeness
- warning: the SQLHomedirOnDemand directive is deprecated, and will be removed in the next release
your.hostname.tld -
your.hostname.tld - Config for ProFTPD Default Installation:
your.hostname.tld - Limit
your.hostname.tld - DenyAll
your.hostname.tld - DefaultServer
[...]

In this example the loglevel is set to the highest possible value (9).

Press [CTRL]+[C] to exit and stop the proftpd daemon.

Source of information

I just want to get things up and running, and the official documentation didn't do it (but the offical docs rarely does :-)

Luckily Google found this one for me, which has been a great inspiration (Thank you!): Howto forge - Virtual Hosting With Proftpd And MySQL