36.3 ProFTPD (with MySQL)

Tip

Connecting to a ProFTPD server using the built-in Windows FTP client will not produce garbled text.

Installing ProFTPD

Note

This section is based on MySQL 8.0. For MySQL installation and basic setup, please refer to other chapters.

Please complete the installation and configuration of MySQL 8.x on your own, and ensure that its version is consistent with the databases/mysql8X-client version installed by proftpd-mod_sql_mysql.

Install ProFTPD and its MySQL module:

# pkg install proftpd proftpd-mod_sql_mysql

Or install using Ports:

# cd /usr/ports/ftp/proftpd/ && make install clean
# cd /usr/ports/databases/proftpd-mod_sql_mysql/ && make install clean

ProFTPD Configuration File

Edit the ProFTPD configuration file /usr/local/etc/proftpd.conf:

Tip

See the sample file at /usr/local/etc/proftpd.conf.sample.

ServerName "Test Ftp Server"                     # FTP server name
ServerType standalone                             # Standalone mode
DefaultServer on                                  # Set as default server
ServerIdent on "FTP Server ready"                # Server identification information
Port 21                                          # Listening port
Umask 022                                        # File creation mask

# Timeout settings
TimeoutLogin 300                                 # Login timeout (seconds)
TimeoutIdle 36000                                # Idle timeout (seconds)
TimeoutNoTransfer 36000                          # No-transfer timeout (seconds)

# Resource limits
User proftpd                                     # Service running user
Group proftpd                                    # Service running group
RLimitMemory 256M 256M                           # Memory limit
RLimitOpenFiles 1024 1024                        # Open file count limit
PassivePorts 50000 60000                          # Passive mode port range

# Log configuration
LogFormat default "%h %l %u %t \"%r\" %s %b"     # Default log format
LogFormat auth "%v [%P] %h %t \"%r\" %s"         # Authentication log format
SystemLog /var/log/proftpd/proftpd.log           # System log path
TransferLog /var/log/proftpd/xfer.log            # File transfer log path
ExtendedLog /var/log/proftpd/auth.log AUTH auth  # Extended authentication log

# MySQL module loading
LoadModule mod_sql.c
LoadModule mod_sql_mysql.c
LoadModule mod_sql_passwd.c

# Only allow access to respective directories
DefaultRoot ~                                    # Restrict users to their home directory

# Allow file overwriting
AllowOverwrite on                                # Allow file overwrite

<Global>
  # Database connection
  SQLConnectInfo proftpd@localhost proftpd <your_database_password>   # Database connection info (user, host, password)

  # Password authentication settings
  SQLAuthTypes SHA256                             # Use SHA256 encrypted authentication
  SQLPasswordEngine on                            # Enable SQL password engine

  # User table mapping
  SQLUserInfo users username password uid gid homedir shell   # Map users table fields
  SQLDefaultGID 2000                              # Default GID
  SQLDefaultUID 2000                              # Default UID
  RequireValidShell off                            # Do not require a valid shell

  # Authentication order
  AuthOrder mod_sql.c
  SQLAuthenticate users                            # Use SQL user table for authentication

  # Login statistics
  SQLNamedQuery getcount SELECT "CONCAT('Total logins: ', count) FROM users WHERE username='%u'"   # Query login count
  SQLNamedQuery updatecount UPDATE "count=count+1 WHERE username='%u'" users                       # Update login count
  SQLShowInfo PASS "230" "%{getcount}"              # Display login statistics
  SQLLog PASS updatecount                            # Record login statistics

  # File operation logging
  SQLNamedQuery log_work FREEFORM "INSERT INTO worklog (user_name, file_and_path, bytes, send_time, client_ip, client_name, client_command) VALUES ('%u', '%f', %b, NULLIF('%T', ''), '%a', '%h', '%m')"  # File operation log record
  SQLLog RETR,STOR,DELE log_work                    # Log RETR, STOR, DELE operations to the worklog table
</Global>

Create the log directory:

# mkdir /var/log/proftpd

Warning

If the directory is not manually created, the system will display proftpd[3435]: warning: handling possibly truncated configuration data at line 65 of '/usr/local/etc/proftpd.conf'.

Tip

The ProFTPD configuration file /usr/local/etc/proftpd.conf has a syntax check command: proftpd -t -d5.

In this configuration, the server uses port 21 for active mode and ports 50000-60000 for passive mode. Ensure that the firewall allows these ports.

For the PF firewall, use the following rule:

Allow inbound TCP traffic from the external interface $ext_if (replace with the actual network interface) to local ports 21 and 50000-60000:

pass in quick on $ext_if proto tcp from any to $ext_if port { 21, 50000:60000 }

Related File Structure

/usr/local/etc/
├── proftpd.conf # ProFTPD main configuration file
└── proftpd.conf.sample # ProFTPD sample configuration file

Creating Users

For security reasons, ProFTPD should be run as a non-root user. Add a new user:

# adduser
Username: proftpd # Username
Full name: FTP User # User full name
Uid (Leave empty for default):
Login group [proftpd]:
Login group is proftpd. Invite proftpd into other groups? []:
Login class [default]:
Shell (sh csh tcsh bash nologin) [sh]: nologin # Do not allow system login
Home directory [/home/proftpd]:
Home directory permissions (Leave empty for default):
Enable ZFS encryption? (yes/no) [no]:
Use password-based authentication? [yes]: no
Lock out the account after creation? [no]:
Username    : proftpd
Password    : <disabled>
Full Name   : FTP User
Uid         : 1002
ZFS dataset : zroot/home/proftpd
Class       :
Groups      : proftpd
Home        : /home/proftpd
Home Mode   :
Shell       : /usr/sbin/nologin
Locked      : no
adduser: INFO: Successfully created ZFS dataset (zroot/home/proftpd).
adduser: INFO: Successfully added (proftpd) to the user database.
Add another user? (yes/no) [no]:
Goodbye!

MySQL Database Setup

Create the MySQL database and user:

CREATE DATABASE `proftpd` CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;	 -- Create proftpd database using utf8mb4 character set and general collation

Create the database user and password:

CREATE USER 'proftpd'@'localhost' IDENTIFIED BY '<your_database_password>';   -- Create MySQL user proftpd, only allowed to connect from localhost, replace with actual password
GRANT SELECT, INSERT, UPDATE, DELETE ON proftpd.* TO 'proftpd'@'localhost';   -- Grant proftpd user CRUD permissions on the proftpd database
FLUSH PRIVILEGES;   -- Flush privileges to make the configuration take effect immediately

Create data tables:

USE proftpd;   -- Select the proftpd database

-- Create users table
DROP TABLE IF EXISTS users;
CREATE TABLE `users` (
   `username` VARCHAR(30) NOT NULL,   -- Username
   `descr` TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,   -- User description
   `password` VARCHAR(64) NOT NULL,   -- User password (SHA-256 encrypted)
   `uid` INT(11) DEFAULT NULL,        -- User UID
   `gid` INT(11) DEFAULT NULL,        -- User GID
   `homedir` VARCHAR(255) DEFAULT NULL,   -- User home directory
   `shell` VARCHAR(255) DEFAULT NULL,     -- User shell
   `count` INT(11) NOT NULL DEFAULT 0,    -- Login count statistics
   PRIMARY KEY (`username`)                 -- Primary key is username
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- Create worklog table
DROP TABLE IF EXISTS worklog;
CREATE TABLE worklog (
   id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,   -- Log ID, auto-increment primary key
   date TIMESTAMP(0) DEFAULT CURRENT_TIMESTAMP(0),   -- Operation time
   user_name VARCHAR(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,  -- Username
   file_and_path VARCHAR(1024) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,  -- File and path
   bytes BIGINT UNSIGNED DEFAULT NULL,   -- File size
   send_time VARCHAR(9) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,  -- File transfer time
   client_ip VARCHAR(15) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,  -- Client IP
   client_name TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,        -- Client name
   client_command VARCHAR(5) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,  -- Client command
   PRIMARY KEY (id),
   UNIQUE INDEX id (id)
) ENGINE=InnoDB CHARACTER SET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC;

-- Insert test user
INSERT INTO `proftpd`.`users` (`username`, `descr`, `password`, `uid`, `gid`, `homedir`, `shell`, `count`)
VALUES ('test', 'Test user', SHA2('FTPpassword_here', 256), '1002', '1002', '/home/www/ftp', NULL, '0');   -- Add test user record

The following has been created:

Item	Value
FTP username	test
FTP login password	FTPpassword_here
UID	1002
GID	1002

Warning

The UID and GID above must match those of the proftpd user, otherwise there will only be read permission without write permission.

You can obtain the UID, GID, and group information for the proftpd user using:

# id proftpd
uid=1002(proftpd) gid=1002(proftpd) groups=1002(proftpd)

Test the database connection:

# mysql -u proftpd -p -h localhost proftpd	# Connect to the proftpd database on localhost using the proftpd user, the system prompts for a password

proftpd@localhost [proftpd]> show databases;   -- Display the list of all databases on the MySQL server
+--------------------+
| Database           |
+--------------------+
| information_schema |
| performance_schema |
| proftpd            |
+--------------------+
3 rows in set (0.00 sec)

proftpd@localhost [proftpd]> use proftpd;   -- Select the proftpd database as the current operating database
Database changed
proftpd@localhost [proftpd]> select * from users;   -- Query all records in the users table
+----------+-----------+------------------------------------------------------------------+------+------+---------------+-------+-------+
| username | descr     | password                                                         | uid  | gid  | homedir       | shell | count |
+----------+-----------+------------------------------------------------------------------+------+------+---------------+-------+-------+
| test     | Test user | d1d6930fda5f964acba51ec4c35d0ddb3b36d25bfef59f1120abd2e4f9f140d9 | 1002 | 1002 | /home/www/ftp | NULL  |     0 |
+----------+-----------+------------------------------------------------------------------+------+------+---------------+-------+-------+
1 row in set (0.00 sec)

Create the directory and test the FTP user:

# mkdir -p /home/www/ftp                  # Create the FTP user's home directory and subdirectories
# chown -R proftpd:proftpd /home/www/ftp # Set directory owner and group to proftpd
# chmod -R 775 /home/www/ftp             # Set directory permissions to 775, allowing group users to read and write

Service Operations

After configuration is complete, start and manage the ProFTPD service:

# service proftpd enable    # Set ProFTPD service to start on boot
# service proftpd start     # Start the ProFTPD service
# service proftpd stop      # Stop the ProFTPD service
# service proftpd restart   # Restart the ProFTPD service

Log in to FTP with the username test and the password FTPpassword_here.