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):
LoadModule mod_sftp.c LoadModule mod_sql.c LoadModule mod_sql_mysql.c LoadModule mod_sql_passwd.c AuthOrder mod_sql.c #ServerName "sftpd.mimar.rs" #DefaultAddress 10.66.66.66 Port 2222 SocketBindTight on ServerType standalone User proftpd Group proftpd DefaultRoot ~ RequireValidShell no CreateHome off ScoreboardFile /var/run/proftpd/scoreboard ServerLog /var/log/proftpd/server.log AllowOverwrite on Umask 0002 <IfModule mod_sftp.c> SFTPEngine on SFTPAuthMethods password SFTPClientAlive 10 30 SFTPCompression delayed SFTPHostKey /etc/ssh/ssh_host_rsa_key SFTPHostKey /etc/ssh/ssh_host_ecdsa_key SFTPLog /var/log/proftpd/sftp.log SFTPDHParamFile /usr/local/etc/proftpd/dhparams.pem </IfModule> <IfModule mod_sql.c> SQLAuthenticate users SQLAuthTypes Crypt SQLBackend mysql # SQLConnectInfo database@server mysqluser VeRyLoNgMySqLpAsS SQLLogFile /var/log/proftpd/sql.log SQLUserInfo users userid passwd uid gid homedir shell SQLNamedQuery accessed UPDATE "last_accessed = NOW() WHERE userid='%u'" users SQLLog PASS accessed </IfModule>
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:
mkdir /var/log/proftpd 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, gid INTEGER, homedir VARCHAR(255), shell VARCHAR(255), last_accessed DATETIME );
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.