Hack 11 Use proftp with a MySQL Authentication Source
Make sure that your database system's OS is running as
proftpd is a powerful FTP daemon with a configuration syntax much like Apache. It has a whole slew of options not available in most FTP daemons, including ratios, virtual hosting, and a modularized design that allows people to write their own modules.
One such module is mod_sql , which allows proftpd to use a SQL database as its back-end authentication source. Currently, mod_sql supports MySQL and PostgreSQL. This can be a good way to help lock down access to your server, as inbound users will authenticate against the database (and therefore not require an actual shell account on the server). In this hack, we'll get proftpd authenticating against a MySQL database.
First, download and build the source to proftpd and mod_sql :
~$ bzcat proftpd-1.2.6.tar.bz2 tar xf - ~/proftpd-1.2.6/contrib$ tar zvxf ../../mod_sql-4.08.tar.gz ~/proftpd-1.2.6/contrib$ cd . . ~/proftpd-1.2.6$ ./configure --with-modules=mod_sql:mod_sql_mysql \ --with-includes=/usr/local/mysql/include/ \ --with-libraries=/usr/local/mysql/lib/
(Naturally, substitute the
rob@catlin:~/proftpd-1.2.6$ make && sudo make install
Next, create a database for
to use (
$ mysqladmin create proftpd
Then, permit read-only access to it from proftpd :
$ mysql -e "grant select on proftpd.* to proftpd@localhost \ identified by 'secret'; "
Create two tables in the database, with this schema:
CREATE TABLE users ( userid varchar(30) NOT NULL default '', password varchar(30) NOT NULL default '', uid int(11) default NULL, gid int(11) default NULL, homedir varchar(255) default NULL, shell varchar(255) default NULL, UNIQUE KEY uid (uid), UNIQUE KEY userid (userid) ) TYPE=MyISAM; CREATE TABLE groups ( groupname varchar(30) NOT NULL default '', gid int(11) NOT NULL default '0', members varchar(255) default NULL ) TYPE=MyISAM;
One quick way to create the tables is to save this schema to a file called proftpd.schema and run a command like mysql proftpd < proftpd.schema .
Now we need to tell proftpd to use this database for authentication. Add the following lines to /usr/local/etc/proftpd.conf :
SQLConnectInfo proftpd proftpd secret SQLAuthTypes crypt backend SQLMinUserGID 111 SQLMinUserUID 111
line takes the form
SQLConnectInfo proftpd@dbhost:5678 somebody somepassword
line lets you create users with passwords stored in the standard Unix crypt format, or
line as specified won't allow blank passwords; if you need that functionality, also include the empty keyword. The
lines specify the minimum
Finally, we're ready to create users in the database. This will create the user jimbo, with effective user rights as www/www, and dump him in the /usr/local/apache/htdocs/ directory at login:
mysql -e "insert into users values ('jimbo',PASSWORD('sHHH'),'111', \ '111', '/usr/local/apache/htdocs','/bin/bash');" proftpd
The password for jimbo is encrypted with
function before being stored. The
line is passed to
directive. It has no
At this point, you should be able to fire up proftpd and log in as user jimbo, with a password of sHHH. If you are having trouble getting connected, try running proftpd in the foreground with debugging on, like this:
# proftpd -n -d 5
Watch the messages as you attempt to connect, and you should be able to track down the source of difficulty. In my experience, it's almost always due to a failure to set something properly in proftpd.conf , usually regarding permissions.
module can do far more than I've shown here; it can connect to existing mysql databases with arbitrary table
— Rob Flickenger (Linux Server Hacks)