First, the MySQL database has three tables: hosts, login_log, and users.
CREATE TABLE `users` (
`id` int(11) NOT NULL auto_increment,
`host_id` varchar(50) NOT NULL default '0',
`username` varchar(100) NOT NULL default '',
`passwd` varchar(100) NOT NULL COMMENT 'use PASSWORD() to encrypt',
`uid` int(5) NOT NULL default '5000',
`gid` int(5) NOT NULL default '5000',
`ftpdir` varchar(255) NOT NULL default '',
`ts_created` timestamp NOT NULL default '0000-00-00 00:00:00',
`ts_modified` timestamp NOT NULL default CURRENT_TIMESTAMP,
`deleted` smallint(1) NOT NULL default '0',
PRIMARY KEY (`id`),
UNIQUE KEY `username` (`username`)
) DEFAULT CHARSET=utf8;
CREATE TABLE `hosts` (
`id` int(11) NOT NULL auto_increment,
`name` varchar(50) NOT NULL default '',
PRIMARY KEY (`id`),
UNIQUE KEY `name` (`name`)
) DEFAULT CHARSET=utf8;
CREATE TABLE `login_log` (
`ip_address` varchar(15) NOT NULL default '',
`dns_name` varchar(150) default NULL,
`timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
`username` varchar(100) NOT NULL default ''
) DEFAULT CHARSET=utf8;
The "hosts" table is created with the intent that multiple hosts use this database for FTP authentication. Insert the FQDN of all hosts into the "name" field.
The "users" table holds all the pertinent user information. The "password" field is encrypted using the MySQL "PASSWORD" function. "ftpdir" holds the directory to chroot the user into. "uid" and "gid" are the respective user and group id to assign to files that are created on/uploaded to the server. "host_id" is used to specify which server this user can log in to. It ties back to the "hosts" table.
The "login_log" table is used for logging whenever a user logs into the server. "ip_address" stores the IP address of the person who logged in. "dns_name" is the contents of a reverse DNS lookup on the IP address. "username" is the name of the user that logged in.
Now for the actual "proftpd.conf" configuration.
ServerName "FTP Server"
ServerType standalone
DefaultServer on
Port 21
Umask 007
MaxInstances 30
User nobody
Group nogroup
This group of directives sets up some of the basics for the FTP server. Proftp suports Virtual Hosts in manner like Apache.
"DefaultServer" tells proftpd to use all the settings here as the default server when a connection comes in.
"ServerType" tells the Proftp daemon to listen for connections instead of using inetd.
"Port" sets the port to use.
"Umask" sets the default permissions.
"MaxInstances" sets the maximum simultaneous connections.
"User" and "Group" set the user and group to run the server as.
SQLEngine on
SQLBackend mysql
SQLAuthTypes Backend
SQLConnectInfo mysql_user@mysql_host db_name mypassword
SQLAuthenticate users
SQLUserInfo custom:/select_user
SQLNamedQuery select_user SELECT "username, passwd, uid, gid, ftpdir, '/bin/bash' FROM users WHERE deleted=0 AND username='%U' AND host_id=host_id_for_this_host"
"SQLEngine" tells the server to turn on the abililty to use a SQL backend.
"SQLBackend" should be set to "mysql" to use MySQL as the authentication backend.
"SQLAuthTypes" tells the server what encryption is used for the password in the backend. The value "Backend" tells it to use the "PASSWORD" function for encryption.
"SQLConnectInfo" gives the connection info for the MySQL database.
"SQLAuthenticate" specifies the table to use for authentication.
"SQLNamedQuery" is used to specify a query to be used in some other location. For instance, this one is called "select_user". Looking at the "SQLUserInfo" line it can be seen that we use a custom query to authenticate users. The "select_user" query is used whenever a user attempts to log in. The columns MUST be returned in a very specific order. See the "SQLUserInfo" directive in the Proftp documentation for more information.
SQLDefaultUID 65534
SQLDefaultGID 65534
SQLMinUserUID 20
SQLMinUserGID 20
SQLLog PASS insert_login
SQLNamedQuery insert_login INSERT "'%a', '%h', NOW(), '%U'" login_log
"SQLDefaultUID" and "SQLDefaultGID" are the user id and group id to use if none are specified. These values will probably never be used.
"SQLMinUserUID" and "SQLMinUserGID" are the minimum values allowed in the authentication table for a user. If these minimums aren't met the user will not be able to log in.
"SQLLog" tells Proftp a query to run when the given FTP command is given. In this case, it logs anything that successfully authenticates to the FTP server.
"SQLNamedQuery" again specifies a query to run when called. See "SQLNamedQuery" in the Proftp documentation for more details. To know what the different variables mean in the query, see "LogFormat" in the Proftp documentation.
SystemLog /var/log/proftpd/proftpd.log
DefaultRoot ~
AllowOverwrite on
AllowRetrieveRestart on
AllowStoreRestart on
UseReverseDNS on
IdentLookups off
ListOptions "-a"
TimeoutIdle 3600
"SystemLog" tells where to log all of the Proftpd messages.
"DefaultRoot" jails the user into their ftp directory when set to "~".
"AllowOverwrite" allows files to be overwritten.
"AllowRetrieveRestart" allows a file to restart a download from where it last finished.
"AllowStoreRestart" allows a file to restart an upload from where it last finished.
"UseReverseDNS" tells Proftp to do reverse DNS lookups.
"IdentLookups" tells Proftp whether or not to do Ident lookups.
"ListOptions" tells Proftp what options to pass to an "ls" command by default.
"TimeoutIdle" is the timeout before automatically disconnecting an idle user.
Quite a write-up, but I hope it helps someone.
