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's ftpasswd --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 user
  • shell 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.

Previous Post