Installing ProFTP with MySQL Support

Warning: This article/tutorial is more the 45 days old. As such the information contained within could be, by now, out of date. Please read all information to make sure that this article/tutorial will work with your current version of the Operating System

Taken from http://www.afp548.com/
ProFTPd, for the pro?
by Didde Brockman

The FTP server installed by default on Mac OS X Server is good enough for most administrators looking to enable users to upload or download files from their home directories or Share Points, but that’s about it. When you’re looking at a rather complex directory structure and a lot of different users it gets a bit complicated.

After a few hours of looking through the different possible solutions which might suit our demands, I decided to write down a list of the features the server had to include. As it would be hosting a wide variety of Web sites through Apache with individual clients wanting access to their files it’d have to be scaleable to the degree where we could add FTP users easily without having to use the fairly slow Workgroup Manager application. Also we didn’t want them to be able to SSH into the machine as this could be seen a potential security risk.

So, what we needed was a server software which would contain its own users and groups database separate from Apple’s NetInfo.

ProFTPd is used on large sites like SourceForge and several others so I figured it should be able to handle a large enough load of users. While I was reading through the documentation for it I stumbled across a component (add-on) that enables user verification through an SQL database like PostGreSQL or MySQL. This seemed like the perfect solution for us since we then could create a simple Web application to add users and groups to the database which ProFTPd authenticates against. I decided to give it a try…

The installation process might seem complicated if one is not used to the idea of a command line interface, but it’s pretty straight forward actually. The process below demands root privileges, I trust you to use sudo or be logged in as root. I decided to use MySQL for this setup since it’s included with Mac OS X server, also I will assume you already have it up and running. If not, there are plenty of good articles out there to cover that matter. Believe me, it’s a very simple process.
Installation outline
These are the steps I am going to walk you through.

1. We will download ProFTPd and the add-on enabling it to authenticate through the MySQL database.

1. Configure two database tables, users and groups, which will be used for user management.

1. Integrate our new and shiny FTP server with xinetd.

Get it?
First we should get the software we’re going to need. So head on over to a directory where you’re comfortable with putting temporary stuff. I use my own /usr/local/play for it, but any directory of your choice will do just fine.

As of the writing of this article the latest stable version is 1.2.7, so that’s the version we’re going to download.

% curl -O ftp://ftp.proftpd.org/distrib/source/proftpd-1.2.7.tar.gz
% tar xvfz proftpd-1.2.7.tar.gz
% cd proftpd-1.2.7

Ok, now that we’ve got ProFTPd we should get the module required to enable it to authenticate through a database. The files in question belong in proftpd-1.2.7/contrib. If you’re looking for more information about this module, head on over to http://www.lastditcheffort.org/~aah/proftpd/mod_sql/.

% cd contrib
% curl -O http://www.lastditcheffort.org/~aah/proftpd/mod_sql/src/mod_sql-4.08.tar.gz
% tar xvfz mod_sql-4.08.tar.gz

We’re now ready to configure and compile, but we need to tell ProFTPd that we want use MySQL for authentication by handing a couple of directives to it.

% cd ..
% ./configure –with-modules=mod_sql:mod_sql_mysql \
–with-includes=/usr/local/include \
–with-libraries=/usr/lib/mysql

Then we make, and install…

% make
% make install

ProFTPd will land in /usr/local/sbin and its configuration file will be located at /usr/local/etc/proftpd.conf. The one thing I really like about this server is the Apache style configuration file. If you feel comfortable with httpd.conf, you will like the syntax proftpd.conf.

Now that we have ProFTPd ready to run we will create the necessary database and tables for authentication. I’ll use the Terminal to interface with MySQL but if you prefer to use a GUI application for it, more power to you. The naming conventions I’ll be using are not “standard”, meaning you can name your own anything you like, as long as it will be reflected in the configuration file we will build later on.

% mysql -u root -p
> Password:

mysql> create database ftpauth;
mysql> use ftpauth;

mysql> create table usertable (
> userid text,
> passwd text,
> homedir text,
> shell text,
> uid int,
> gid int);

mysql> create table grouptable (
> groupname text,
> gid int,
> members text);

What we just did was to create a database named ftpauth. Along with that we constructed a table called usertable which will contain the users ProFTPd will be letting in. Also the table grouptable was made here. It will be the container for the groups which the users will be inserted into.

Finally we want to create a user in the database who can connect and select (lookup) the relevant information to be able to authenticate an FTP session. Since we’re going to be running both the MySQL and ProFTPd on the same machine we only let our user, proftpd connect from the localhost which is good from a security perspective.

mysql> grant select on ftpauth.* to proftpd@localhost identified by ‘myPasswd’;
mysql> flush privileges;

xinetd
You have a choice to make right here. You could run ProFTPd in “StandAlone” mode, or you could integrate it with xinetd. The default FTP server is run through the latter. What it means is that you don’t have to boot ProFTPd manually or through a startup item, xinetd will take care of that for you. Basically it does all the listening on port 21 and spawns a child process when needed. I would recommend using xinetd since you can control your FTP server through the Server Settings application just like you would with the default FTP server.

Please note if you decide to run it in “StandAlone” mode it will have to be reflected in proftpd.conf. Simply replace “inetd” with “StandAlone”.

What we want to do is to tell xinetd that there’s no point of using the old FTP server anymore. So, here’s what we do:

% pico /etc/xinetd.d/ftp

… Then we want to comment out these lines:

server = /usr/libexec/xftpd
server_args = -a

… And add this line:

server = /usr/local/sbin/proftpd

This way, if you ever change your mind you simpy remove our added line and remove the # in front of the two lines we edited and you will be back from where you started.

Finally we need to tell xinetd to use the new settings:

% kill -HUP `cat /var/run/xinetd.pid`

proftpd.conf
Now we have both ProFTPd and MySQL ready to serve. We just need to give ProFTPd some parameters so it can connect to our database and look through the tables we just created. Below is my sample configuration file. Please note that it is far from complete. It simply illustrates how to point the authentication to the database. You can create your own secure and customized configuration based on the documentation available from http://www.proftpd.org/.

The directives conserning SQL are the ones we want to talk about further as the others only relate to the actual configuration of ProFTPd.

ServerName “My new and Shiny ProFTPd server!”

# Could use “StandAlone”, but we want xinetd to handle incoming connections
ServerType inetd

ServerAdmin admin@myplace.com

# Hide as much info as possible to outsiders
ServerIdent on “Welcome. Please login…”
DeferWelcome on

DefaultServer on

Port 21
Umask 022

User root
Group wheel

# Lock users into their home directories
DefaultRoot ~

# The passwords in the MySQL are encrypted using PASSWORD().
# Otherwise we could have used “Plaintext”.
SQLAuthTypes Backend

SQLAuthenticate users*

# This string is used to connect to the database. As you notice,
# the names and values match the ones we created earlier.
# database_name@host database_user database_user_password
SQLConnectInfo ftpauth@localhost proftpd myPasswd

# Here we tell ProFTPd the names of the database columns in the “usertable”
# we want it to interact with. Again, as you notice, the names and values
# match the ones we created earlier.
SQLUserInfo usertable 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, as you notice, the names and values
# match the ones we created earlier.
SQLGroupInfo grouptable groupname gid members

What just happened? Well, first we told ProFTPd how to connect to our dear database containing the user and group information. We also described the layout, or rather – the names of the columns which are interesting for authentication. If you recall, we created a database named ftpauth, two tables; usertable and grouptable. Also we granted access to these to a specific user named proftpd who only can select, not delete or update, and from the localhost only.
Connecting with our testuser
I sure want to give this beast a testdrive now, don’t you? There has been a lot of work and less play, but now we’re almost there. We just need to create a user to login in with, and along with that we can throw him in a group as well.

Get into MySQL again…

% mysql -u root -p
> Password:

mysql> insert into grouptable (
groupname,
gid,
members
)
values(
‘testgroup’,
65001,
‘testuser’
);

… And so, there we have the group of our soon to be user. Now we just need him.

mysql> insert into usertable (
userid,
passwd,
homedir,
shell,
uid,
gid
)
values(
‘testuser’,
PASSWORD(‘anotherPasswd’),
‘/Library/WebServer/testuser_website’,
‘/bin/tcsh’,
64001,
65001
);

testuser now has an account he can use. He will only have access to /Library/WebServer/testuser_website, belong to the group of gid 65001 which is our testgroup and his password is not stored in plaintext in the database, but since we told ProFTPd to authenticate using the backend method it work.

Congratulations. You now should have a working setup where you easily can add FTP-only users to you machine setup without having to involve NetInfo or any passwd files. What you really need to do is to test it all out right away…

If the login fails, then try to run ProFTPd directly from the Terminal. This way you’ll see if there is a problem with the configuration file or if something else is out of bounds. You should see something like this if you have set ProFTPd to run in xinetd mode.

% /usr/local/sbin/proftpd
localhost – Fatal: Socket operation on non-socket
localhost – (Running from command line? Use `ServerType standalone’ in config file!)

What now?
I really recommend that you read through the documentation and FAQ’s available at http://www.proftpd.org/ so you can create your own secure configuration file which suits your needs.

Also, don’t forget to look up the powerful /usr/local/bin/ftpwho and /usr/local/bin/ftptop commands as they can easily be part of a script which monitors your FTP traffic. You should also head on over to http://www.lastditcheffort.org/~aah/proftpd/mod_sql/ and read about the MySQL module’s logging capabilities as it can actually log FTP actions into the database.

Mac OS X server also ships with Tomcat which means you could use Jsp (or PHP if that is your preference) to administer users and groups through simple HTML forms. Or maybe you could put together a small string parser Jsp which read a bunch of users, passwords and groups from a text file (or another datasource) and batched them all into the database. Quite nice, maybe that will be part two of this article.

Enjoy and good luck!