Pure-FTPD with user storage in MYSQL database

savio June 13, 2013 at 12:53

Pure-FTPD with user storage in MYSQL database

From the sandbox

Previously, you had to configure an FTP server more than once (it’s a matter of a few minutes), and as it turned out historically, ProFtpd was the unconditional candidate . Since this friend had problems in terms of security not so long ago, I decided to try something else, and did not regret.

My choice was based on the wonderful Pure-FTPd FTP server .
After installation, you will receive the following set of functions:

Download and upload speed control for each user
User UID and GUID selection
Accessibility ftp for the user only from the specified ip addresses
Enabling and disabling user accounts

The OS used is FreeBSD . And so let's go! It is assumed that you have installed and configured the DBMS MySql. Install Pure-FTPd from the ports (previously upgrading them)

cd /usr/ports/ftp/pure-ftpd
make install clean

I chose the following options.

After installation, we create configs from the default files

cd /usr/local/etc/
cp pureftpd-mysql.conf.sample pureftpd-mysql.conf
cp pure-ftpd.conf.sample pure-ftpd.conf

Editing the main configuration file

pure-ftpd.conf

# Chroot to list all users in their folders
ChrootEveryone yes

# If “no” was selected in the previous option, then the members of the next
# group will not chroot. All the rest will be. If you don’t want to
#chroot everyone, then just uncomment ChrootEveryone and TrustedGID.
# TrustedGID 100

# Enable compatibility features for client curves
BrokenClientsCompatibility no

# Maximum number of simultaneous users
MaxClientsNumber 50

# Work in the background (daemon)
Daemonize yes

# Maximum number of simultaneous connections from one IP
MaxClientsPerIP 8

# If you want to log all client commands, then in this
# point should be “yes”. If you also need to
log # server responses, then simply duplicate this item.
VerboseLog no

# Show or not files starting with a period , even when the client
# does not explicitly say that this should be done, with the -a option.
DisplayDotFiles yes

# Do not allow authenticated users - this FTP
# is only for anonymous clients.
AnonymousOnly no

# Deny anonymous names - FTP current for registered users.
NoAnonymous no

# Means syslog (auth, authpriv, daemon, ftp, security, user, local *)
# Default - "ftp". "None" - disables logging.
SyslogFacility ftp

# Show any cookies? (Display fortune cookies)
# FortunesFile / usr / share / fortune / zippy

# Do not resolve host names in logs. Logs are becoming less informative,
# but resources are also required. “Yes” - it makes sense to install it on very
# busy servers, or when DNS is not working.
DontResolve yes

# Maximum downtime (after the connection breaks), in minutes
# (default = 15 minutes)
MaxIdleTime 15

# LDAP configuration file (see README.LDAP)
# LDAPConfigFile /etc/pureftpd-ldap.conf

# MySQL configuration file (see README.MySQL)
MySQLConfigFile /usr/local/etc/pureftpd-mysql.conf

# Postgres configuration file (see README.PGSQL)
# PGSQLConfigFile /etc/pureftpd-pgsql.conf

# PureDB user database (see README.Virtual-Users)
# PureDB /etc/pureftpd.pdb

# path to the pure-authd socket (see README.Authentication-Modules)
# ExtAuth /var/run/ftpd.sock

# If you need to connect PAM authentication uncomment
# the next line
# PAMAuthentication yes

# If you need system, Unix authentication (/ etc / passwd),
# uncomment the next line
# UnixAuthentication yes

# Please note that LDAPConfigFile, MySQLConfigFile,
# PAMAuthentication and UnixAuthentication can only be used
# but they can be used together. For example, if you
# use MySQLConfigFile, then UnixAuthentication, then a request is sent
# to MySQL. If such a user is not found in the database, then
# system user is tried in / etc / passwd and / etc / shadow. If SQL
# authentication fails due to an incorrect password, then
# further user search is stopped. Authentication methods
# will be used in the order they are set

# The recursion limits of the 'ls' command. The first argument is the maximum number of files
# to be shown. Second - the maximum number of
LimitRecursion subdirectories is 10000 8

# Do anonymously have the right to create new directories?
AnonymousCanCreateDirs no

# If the system is loaded more than the value indicated here, then
# anonymos cannot download
anything MaxLoad 4

# Range of ports for passive connection. If your firewall is hacking
# standard range
# PassivePortRange 30,000 50,000

# Force IP address in PASV / EPSV / SPSV responses. - for NAT.
# Symbolic hostnames are also accepted for gateways with dynamic IP
# ForcePassiveIP 192.168.0.1

# Upload / download ratio for anonymous.
# AnonymousRatio 1 10

# Upload / download ratio for all users.
# This directive does not overlap the previous one.
# UserRatio 1 10

# Prevent the downloading of files owned by “ftp”, i.e.
# files have been uploaded but not approved by the local (local) admin.
AntiWarez yes

# IP address / port on which we are listening (default = all IP and port 21).
#Bind 192.168.254.254,21

# Maximum speed for anonymous in KB / s
# AnonymousBandwidth 8

# Maximum speed for all users (including anonymous) in KB / s
# Use AnonymousBandwidth or UserBandwidth, using both,
# does not make sense.
# UserBandwidth 8

# Mask for created files.:.
# 177: 077 - if you are paranoid :)
# umask is such a number, subtracting which from the maximum (777) and
# you get the desired mask. those. for the case below, the masks will be, respectively:
# 644 for files, and 755 for
Umask directories 133: 022

# The minimum UID with which the user will be started.
# (In the native version there was 100. I set a thousand)
MinUID 1000

# Allow FXP transfer for authorized users.
AllowUserFXP no

# Allow FXP transfer for anonymous and non-anonymous
AllowAnonymousFXP no

# Users cannot delete and modify files starting with a dot ('.')
# Even if they are their owners. If TrustedGID is enabled, this group has
# access to these files.
ProhibitDotFilesWrite no

# Prevent reading files beginning with a point
ProhibitDotFilesRead no

# Never overwrite files. When the name for the downloaded file already
# exists, it will be automatically renamed to file.1, file.2, file.3, ...
AutoRename no

# Prevent anonymous users from downloading new files (no = upload allowed)
AnonymousCantUpload no

# Only connecting to this IP address may not be anonymous. You
# can use this directive to use multiple IP
#s for anonymous FTP, and leave a private, secure IP for
# remote administration. also you can allow unstable
# local IP (type 10.xxx) for authentication and leave the public
# (for anonymous) FTP server on another IP.
#TrustedIP 10.1.1.1

# If you want the PID to be added to each line of the log,
# then uncomment the next line.
#LogPID yes

# Create an additional log file with a log in the “apache” format:
# fw.c9x.org - jedi [13 / Dec / 1975: 19: 36: 39] “GET /icap.tar.bz2" 200 21808
# This log file can be processed by programs for
# analyzing Apache logs.
# AltLog clf: /var/log/pureftpd.log

# Create an additional log file in a format optimized for
# statistical reports (hz like this. I'll have to see)
# AltLog stats: /var/log/pureftpd.log

# Create another log with the transferred files in the W3C standard
# (compatible with many commercial analyzers)
# AltLog w3c: /var/log/pureftpd.log

# Disable the CHMOD command. Users will not be able to change
# permissions on files.
#NoChmod yes

# Allow users to upload but not delete files.
#KeepAllFiles yes

# Automatically create a user's home directory,
# if it is missing
#CreateHomeDir yes

# Enable virtual quota. The first number is the maximum number of files.
# The second number is the maximum size, in megabytes.
# So 1000: 10 limits each user to 1000 files and 10 megs.
#Quota 1000: 10

# If pure-ftpd is compiled with standalone support, you can change the
# location of the pid file. The default position is /var/run/pure-ftpd.pid
#PIDFile /var/run/pure-ftpd.pid

# If pure-ftpd is compiled with support for pure-uploadscript,
# this item allows you to write information about newly uploaded
# files in /var/run/pure-ftpd.upload.pipe so pure-uploadscript can
# read them and process the downloaded file.
#CallUploadScript yes

# This option is useful on servers, where the anonymization is allowed to upload.
# If / var / ftp is located in a separate / var section, this
# allows you to save free space and protect log files. When the percentage
# filling more than specified here, the automatic upload is prohibited.
MaxDiskUsage 99

# Set 'yes' in this option if you want to allow users
# to rename files.
#NoRename yes

# Enable 'customer proof': some kind of error, like 'chmod 0 public_html',
# when working together, chtol ... In short, this is not a bug but a feature ... :) And so
# dumb clients do not strain your support, you need to put ' yes' at this
# point. If clients have a little knowledge of Unix then this feature
# is useless. If you have a hosting - turn it on.
# (the translation is almost verbatim - but what I still didn’t understand about ...)
CustomerProof yes

# The number of parallel processes. Current works if the server was
# compiled with the option '--with-peruserlimits' (something about the fact that
# in most binary distributors it is).
# Format: <maximum sessions per user>: <maximum sessions of anonymos>
# For example, 3:20 means that an authenticated user can have three
# active sessions. And for all anonymous accounts - a maximum of 20 sessions.
#PerUserLimits 3:20

# When a file is uploaded to the server, and there is a previous version (with the same name),
# the old file will not be deleted or truncated. The download will be made
# to a temporary file and upon completion of the download, an atomic
switch # will be made to the new version of the file. For example, when loading a large PHP
script #, Apache will work with the old version until it is fully loaded
# and immediately switches to a new one as the current it will be completely transferred
# This option is not compatible with virtual quotas.
#NoTruncate yes

# This option can take three values:
# 0 - disable SSL / TLS encryption (by default).
# 1 - accept both encrypted and regular connections.
# 2 - reject connections that do not use SSL / TLS,
# including anonymous connections.
# Do not blindly comment on this. Check that:
# 1) The server is compiled with SSL / TLS support (--with-tls),
# 2) A valid certificate is
installed , # 3) Only compatible clients are logged in.
# TLS 1

# List of ciphers to be accepted for SSL / TLS connections
# Prefix -S: to completely disable SSL, but not TLS.
# TLSCipherSuite HIGH: MEDIUM: + TLSv1:! SSLv2: + SSLv3

# Listens for current IPv4 address in standalone mode (i.e. IPv6 is disabled)
# By default, IPv4 and IPv6 are enabled.
IPV4Only yes

# Current IPv6 address is listening in standalone mode (i.e. IPv4 is disabled)
# By default, IPv4 and IPv6 are enabled.
# IPV6Only yes

# UTF-8 support for file names (RFC 2640)
# Define the encoding for the server file system and, optionally,
# default encoding for clients that do not use UTF-8.
# Current works if pure-ftpd is compiled with '--with-rfc2640'
FileSystemCharset utf-8
ClientCharset cp1251

as well as the config for Pure-FTPd with MySql database

pure-ftpd.conf

# MySQL config for pureftpd

# Optional: Name or IP of the MySQL server. Do not set this
item # if using a local unix socket.
#MYSQLServer 127.0.0.1

# Optional: The port on which MySQL hangs. Do not set this
item # if using a local unix socket.
#MYSQLPort 3306

# Optional: Sets the name of the mysql.sock socket if MySQL is on the same host.
MYSQLSocket /tmp/mysql.sock

# Mandatory: the user we use in the database.
MYSQLUser pure-ftpd

# Required: user password, from which we climb into MySQL.
MYSQLPassword pure-ftpd

# Required: the database we are working with.
MYSQLDatabase pureftpd

# Required: how the password is saved in the database
# Possible values: “cleartext”, “crypt”, “md5” and “password”
# (“password” = MySQL password () function)
# You can use “any” to try “crypt”, “md5” and “password”
MYSQLCrypt cleartext

# In the following directives, parts of the lines are replaced, before
# the query is executed:
#
# \ L is replaced by the username that logs in.
# \ I is replaced by the IP address of the server the user is accessing
# \ P is replaced by the port number with which the user is connected.
# \ R is replaced by the IP address of the user.
# \ D is replaced by the user's IP address, in the form of a long decimal number
# (for example, 192.168.254.1 == 3232300545).
#
# You can plan a relatively complex database query using
# this set of variables. If one database is used on several servers,
then "\ I" allows you to determine whether the user is breaking down on that server.

# Query to get the password from the database:
MYSQLGetPW SELECT `password` FROM` users` WHERE `user` =" \ L "AND` active` = '1'

# Query to get the system username, or UID
MYSQLGetUID SELECT `uid` FROM `users` WHERE` user` = "\ L"

# Optional: default UID - instead of asking to retrieve it MYSQLGetUID
#MYSQLDefaultUID 1000

# Database query to get the group name or gid
MYSQLGetGID SELECT `gid` FROM` users` WHERE `user` = "\ L"

# Optional:

MYSQLGetDir SELECT `home` FROM` users` WHERE `user` =" \ L "

# Optional: Request for the maximum number of files from user
# (I wonder - what is the deep meaning of this so that the inode on the
# server does not start chtol? :))
# Must be compiled with `virtual quotas support`.
MySQLGetQTAFS SELECT `QuotaFiles` FROM` users` WHERE `user` =" \ L "

# Optional: quota request (disk usage)
# Number, in megabytes.
# Pure-FTPd must be compiled with `virtual quotas support`.
MySQLGetQTASZ SELECT `QuotaSize` FROM` users` WHERE `user` =" \ L "

# Optional: Relations. Requests for the download / upload ratio.
# Dol; It should not be compiled with this function.
MySQLGetRatioUL SELECT `ULRatio` FROM` users` WHERE `user` =" \ L "
MySQLGetRatioDL SELECT` DLRatio` FROM `users` WHERE` user` = "\ L"

# Optional: Channel width for the user. The server must be
# compiled with this option. Value in KB / s.
MySQLGetBandwidthUL SELECT `ULBandwidth` FROM` users` WHERE `user` =" \ L "
MySQLGetBandwidthDL SELECT` DLBandwidth` FROM users WHERE `user` =" \ L "

# Release the user from the hamster (~). Never do this if:
# 1) You know exactly what you are doing.
# 2) Real and virtual users coincide.
#MySQLForceTildeExpansion 1

# If you updated the tables to transactional (Gemini,
# BerkeleyDB, Innobase ...), you can enable SQL transactions
# Leave commented out if using MyISAM databases,
# or an older version of MySQL (<3.23.x).
#MySQLTransactions On

Next, we create the database itself ( pureftpd ) and a table with users ( users )

CREATE TABLE `users` (
  `User` varchar(50) NOT NULL DEFAULT '',
  `Password` varchar(250) DEFAULT '',
  `Uid` int(11) DEFAULT '10000',
  `Gid` int(11) DEFAULT '10000',
  `Dir` varchar(250) DEFAULT '',
  `QuotaFiles` int(11) DEFAULT NULL,
  `QuotaSize` int(11) DEFAULT '0',
  `ULRatio` int(11) DEFAULT '0',
  `DLRation` int(11) DEFAULT NULL,
  `ULBandwidth` int(11) DEFAULT '0',
  `DLBandwidth` int(11) DEFAULT '0',
  `Comment` tinytext,
  `active` int(11) DEFAULT '0',
  PRIMARY KEY (`User`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

Add to /etc/rc.conf

pureftpd_enable="YES"

and start our FTP server

/usr/local/etc/rc.d/pure-ftpd start
Starting pureftpd.
Running: /usr/local/sbin/pure-ftpd -g/var/run/pure-ftpd.pid -A -c50 -B -C10 -D -fftp -I15 -lmysql:/usr/local/etc/pureftpd-mysql.conf -L10000:8 -m4 -s -U133:022 -u80 -k99 -Z -8utf-8 -9cp1251

That would be all. It remains to enter the user in the users table (specify the username, password, home directory) and try to connect. But I was haunted by the issue of restricting access to a user by his IP address. In the classic case, the ip field is added to the user table, and the sql query for MYSQLGetPW in pureftpd-mysql.conf takes the following form

SELECT Password FROM users WHERE User='\L' AND ip='\R'

But he does not suit me. First, for each user, you need to specify the IP address from which they will connect, and secondly, you can specify only one single IP. In real life, you need to bind to the IP'shnik only certain users, and the ability to set the nth number of IP addresses for one user.

MySql syntax includes program control flow functions, among which we need the IF function.

Create an IP table

CREATE TABLE `ip` (
  `user` varchar(200) DEFAULT NULL,
  `ip` varchar(16) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

We bring the sql query for MYSQLGetPW to the following form:

 SELECT IF((SELECT COUNT(`user`) FROM ip WHERE user='\L')=0,
(SELECT Password FROM users WHERE User='\L'),
(SELECT users.Password FROM users,ip WHERE users.User='\L' AND ip.ip='\R' AND ip.user='\L'))

In the end result, all users for whom entries in the IP table are not set will be authenticated without checking for the IP address, and for those users who are specified in the IP table, they will be checked from which IP address they are trying to connect, and you can set unlimited number of IP addresses for one login

Tags: