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 nobody
/ 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:
userid
should be filled with virtual user's plaintext username.passwd
should be filled with SHA512-hashed password. You can use ProFTPD'sftpasswd --hash --sha512
command to get it.uid
should be different from any local user's uid. I start with 40001 and increase by one.gid
is in my case always 80, because all my virtual users manage their websites' document roots. YMMV.homedir
should be absolute path to directory into which we want to chroot particular usershell
should always be/usr/sbin/nologin
- leave
last_accessed
empty - it will be filled by ProFTPD server on virtual users' login
homedir
should be chowned to their respective uid
: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.