Uploading websites to their document roots was traditionally done by means of FTP. FTP is an ancient beast which should probably be eradicated from the Internet, mostly because it uses cleartext communication for both authentication (meaning it's trivial to sniff out credentials on the wire) and data transfer (meaning it's prone to MITM attacks). However, a lot of old-school web programmers are used to their FTP clients, such as FileZilla, and not too eager to learn new stuff. This article explains how to set up ProFTPD server in SFTP mode listening on non-standard port, whose clients - virtual users - will be authenticated from MySQL database and chrooted to their directories. Virtual users won't have local shell access, as opposed to local users who can ssh to standard port provided by FreeBSD's built-in ssh server, but they will be able to transfer files securely without the need to move away from FileZilla.
We'll start by installing needed package:
pkg install proftpd-mod_sql_mysql
ProFTPD's config file at
/usr/local/etc/proftpd.conf should look as follows (modify commented lines in accordance with your environment and uncomment them):
SFTPClientAlive 10 30
# SQLConnectInfo database@server mysqluser VeRyLoNgMySqLpAsS
SQLUserInfo users userid passwd uid gid homedir shell
SQLNamedQuery accessed UPDATE "last_accessed = NOW() WHERE userid='%u'" users
SQLLog PASS accessed
ProFTPD is on FreeBSD by default configured to run under
nogroup credentials, but I think it is better to run it under dedicated
proftpd unpriviledged account, as specified in above config. Let's create user and group:
pw groupadd proftpd -g 20012
pw useradd proftpd -u 20012 -c "ProFTPD Unprivileged User" -d /nonexistent -g proftpd -s /usr/sbin/nologin
We also need to create log dir, and give it appropriate permissions:
chown proftpd:proftpd /var/log/proftpd
On our MySQL server, we need to create a database, and execute the following query in order to create our
users table with needed fields:
CREATE TABLE users (
userid VARCHAR(30) NOT NULL UNIQUE,
passwd VARCHAR(255) NOT NULL,
uid INTEGER UNIQUE,
Insert appropriate values into above fields:
useridshould be filled with virtual user's plaintext username.
passwdshould be filled with SHA512-hashed password. You can use ProFTPD's
ftpasswd --hash --sha512command to get it.
uidshould be different from any local user's uid. I start with 40001 and increase by one.
gidis in my case always 80, because all my virtual users manage their websites' document roots. YMMV.
homedirshould be absolute path to directory into which we want to chroot particular user
shellshould always be
last_accessedempty - it will be filled by ProFTPD server on virtual users' login
homedir should be chowned to their respective
gid for each virtual user.
All that remains to be done is to modify
rc.conf to start ProFTPD at boot time, and also start it immediately:
echo 'proftpd_enable="YES"' >> /etc/rc.conf
service proftpd start
You should be able to catch any errors in logfiles under
/var/log/proftpd/ and correct them.
Hopefully, our change-reluctant web programmers will be able to choose 'SFTP - SSH File Transfer Protocol' instead of 'FTP - File Transfer Protocol' and enter port 2222 in FileZilla. If not, point them to the picture below:
Got any feedback? Drop me a line, please.