Manage ProFTP Users with MySQL

📅 February 13, 2018
Suppose you want to maintain a list of users allowed to login to an FTP server but you do not want to create user accounts for them on the Linux system. The FileZilla server has this feature built in, so is there are way to specify usernames and their passwords for FTP users in ProFTP?

Yes!

One way is to use a MySQL database that ProFTP checks for allowed users. If a user is listed in the database, then he is allowed to log in.

This might sound like overkill. Why use a full-fledged relational database for FTP? Actually, you can much, much more than manage logins. Every aspect of the FTP session can be recorded and analyzed using a database. Uploads, IP addresses, last logins, login history, access count, upload/download quotas, and more are possible. Almost anything you want to record about your users is possible with ProFTP and a database, such as MySQL.

This article shows how to set up ProFTP to access a MySQL database that lists users allowed to log in without needing to create user accounts on a Linux Mint system.

Prerequisites

We will set up our FTP server in Linux Mint 18.3 MATE running kernel 4.14.18. To save time, we will be reusing the same ProFTP server we created for the Anonymous ProFTP Virtual Host articles. For review, please revisit how to set up ProFTP in a virtual environment. We will assume that ProFTP is already set up and running normally. We will expand upon it by adding MySQL interaction. The network ID has been changed to 192.168.2.0/24 for variety.

Why MySQL?

Other options, such as sqlite, are available. The biggest reason is the handy GUI MySQL Workbench tool. It is easy to create, modify, and manage tables.

Setup Overview

After ProFTP is up and running, there are a number of extra steps to perform that enable database interaction. It can be quite involving, so here is a breakdown of the overall process:

  1. Setup dedicated SQL FTP user and directory
  2. Install MySQL
  3. Install ProFTP MySQL module
  4. Create tables
  5. Configure ProFTP

Create Dedicated SQL FTP User

Recall that anonymous logins require that we have a dedicated Linux user account. All anonymous users who log in act as this user during the FTP session. We must do the same for SQL FTP users. Any user in the database will become this user upon login.

Open Users and Groups from the Linux Mint control panel, and add a standard user named boo (for this example). Give boo a password (booboo), and make sure boo can log in to Linux as a local user.

Linux Mint 18.3 MATE User Settings with the user boo added. This user will be used for all SQL FTP logins.

Create a Dedicated FTP Directory for the SQL FTP Server

We are going to create a virtual host in ProFTP to host all named logins using SQL. No anonymous logins are allowed for this server even though the other two anonymous virtual hosts are still active. To keep this virtual host separate from the others, we will limit all data to its own directory. We already created a dedicated virtual storage drive for FTP data in the previous articles, so create a new directory on it name sqlftp.

sudo mkdir /media/ftpdata/sqlftp

Please see the setup article for review. The point is that the FTP server should store its data in a dedicated location.

Nemo on the virtual machine. Dedicated sqlftp directory sharing the same virtual drive as bumbleftp and zippyftp from previous articles. Each directory is the FTP storage area for a separate virtual FTP server. Data is not shared among them.

Change sqlftp Permissions

Like the other directories, we must change the ownership of sqlftp to root and the group must be boo.

sudo chown root:boo /media/ftpdata/sqlftp

(Change /media/ftpdata to the path of your FTP setup. This is the path used for this example.)

Set Group ID for sqlftp

Set the group ID for the sqlftp directory so all directories and files contained within are automatically assigned membership to the boo primary group. We must also set the group write permission. This command does both:

sudo chmod g+ws /media/ftpdata/sqlftp

If we run ls -l in a terminal in ftpdata, we should see the new settings.

ls -l /media/ftpdata

sqlftp data is owned by root, group ownership is boo, and the write permission and group ID are set.

 

Install MySQL

In order to use a database, we must install one. Open Synaptic Package Manager and install these packages:

  • mysql-server
  • mysql-workbench

Any dependencies will also be installed. Install on the same virtual machine that is running ProFTP.

During MySQL setup, you will be prompted for a MySQL password that lets you log in to the MySQL server as the user root. This example uses the password ftp123. Of course, you will want to use something else, but this is a demonstration.

MySQL Login:

  • username: root
  • password: ftp123
  • host: localhost (the same virtual machine as ProFTP)

root:ftp123@localhost is the login for the MySQL server, not a user account on Linux or ProFTP.

Install ProFTP’s MySQL Module

There are a number of additional modules available that add more functionality to ProFTP. (SQLite requires proftpd-mod-sqlite, PostgreSQL requires proftpd-mod-pgsql, LDAP requires proftpd-mod-ldap, to name a few.) To interact with MySQL, we must install the proftpd-mod-mysql package. Chances are that this is not installed by default, so install it using Synaptic or the command line if you prefer.

sudo apt install proftpd-mod-mysql

Set Up the Database

Open MySQL Workbench. (For this example, it should be installed on the same virtual machine as MySQL and ProFTP.)

MySQL Workbench running in a Linux Mint 18.3 MATE virtual machine. The default root connection is shown.

You should see a default connection in MySQL Workbench’s opening screen. Clicking this connects to the MySQL server as root with full privileges. This is not good security, but it is fine for this article. In practice, you would want to create a dedicated, limited account that ProFTP interacts with. However, SQL security is beyond this article’s focus, so we will use the default root. We have a lot to do without getting sidetracked. This is on a private, virtual LAN anyway, so there is no danger of exploitation. Just keep in mind that you definitely want to practice improved security if used for production or on any untrusted network.

MySQL listens on port 3306 using the IP address of the virtual machine (192.168.2.30 in this case). Click the connection to log in. You will be prompted for the password you entered during MySQL installation. Enter ftp123 (the password for this article.)

MySQL Workbench allows you to graphically interact with the MySQL server.

Create ftp Database

Click the Create a new schema icon at the top to create a new database, called a schema.

The schema contains the collection of tables that ProFTP will access.

Name the schema ftp, and set the Default Collation to utf8 – default collation. This sets the character encoding used by the schema. This way, if you need to include, say, kanji characters, UTF-8 will support them. Click Apply, and the new schema will appear as the name ftp under the SCHEMAS listing on the left side of MySQL Workbench.

The sys schema applies to MySQL itself. Leave it alone.

Create the users Table

With the schema created, we now populate it with tables. Tables store our FTP data. Usernames and passwords, login histories, uploaded files, and more are stored in tables. These tables record what we want them to record and nothing else. So, we must specify exactly what we want to store in the database by constructing tables.

The first table we need is a users table. This table contains a list of all FTP users, and it the reason why we are using MySQL in the first place. Rather than creating individual accounts on the Linux system, we are creating a single table that contains a list of users allowed to log in even if they do not have Linux accounts. To allow or deny user logins, simply add or remove them from the users table (or set the shell — mentioned later).

Click the + icon next to the ftp schema, right-click Tables, and select Create Table. A new window appears.

The default new table creation begins with an empty table.

Each table in the schema must have unique name. In the Name field at the top, enter the name users. (You may pick any name you like, but we will go with this.)

Here is where things become interesting and delve into the world of relational databases. We do not add users here. No, no. Instead, we create a table that will contain users in the future. What we need to do now is specify columns that will contain data within the table. Each column must have a specific data type that indicates what kind of data it is allowed to hold. You can customize this table to meet your needs.

For our simple users table, create the following columns and assign each the data type and attributes.

  • Name: users
  • Schema: ftp

Under Column Name, enter each field, one per line.

id        INT(11)     PK NN UQ AI
userid    VARCHAR(45)    NN
passwd    VARCHAR(45)    NN
uid       SMALLINT(6)    NN        Default: 1005
gid       SMALLINT(6)    NN        Default: 1005
homedir   VARCHAR(255)   NN        Default: /media/ftpdata/sqlftp
shell     VARCHAR(16)    NN        Default: /bin/false

NN = Not Null. Check this option. This means there must always be a value in the field. It cannot be null. (Null is a specific value in SQL that is different from 0 or empty.) To ensure that a value is not null upon creation, we can set a default value for that field that will automatically populate.

The numbers after VARCHAR, as in VARCHAR(45), reserve up to that many characters, so the userid may be up to 45 characters in length. VARCHAR is a variable length string of characters rather than a fixed length to save storage space in the database. Only as much space required for the field will be used. If a username is yoohoo, which is six characters, then only six character will be stored.

homedir is set to a probably larger than needed value of up to 255 characters just in case we encounter a deeply nested path. The room is there to grown even if all 255 characters are not used per record.

The completed (for now) users table. Enter the default values too.

UID and GIU Fields

We created the dedicated user boo for SQL logins. When a user logs in, he will assume the role of this user who has a real account on the Linux system. However, we must use his user ID and group ID rather than his username, and we discover that by running cat /etc/passwd. Look for user boo and note the uid and gid values. They should be the same.

cat /etc/passwd

A few user entries from /etc/passwd. Notice that a MsSQL user account exists for the operation of the MySQL server, but you cannot log in to that account because its associated shell is /bin/false and its home directory is /nonexistent.

Each line in /etc/passwd represents one user on the system. Not all entries are real users. Some are requirements for system processes. The username is listed first, and each field is separated by a colon. Find user boo at the bottom. After the x, the user ID is listed next followed by the group ID. We need these values.

In this case both the user ID and group ID for user boo are 1005. Yours will most likely be different, so this is why you must look at /etc/passwd for yourself on your system. In MySQL Workbench, enter 1005 as the default value for the uid field, and enter 1005 for gid field. (Use your values from your system. 1005 is for this example.)

This is the user and group of a real system account that the FTP user will become. As you can guess, you are not limited to a single Linux account for all FTP users. You can specify different UIDs and GIDs for different FTP users. In this example, all SQL FTP users will share the same UID and GID.

homedir

Upon login, this sets the FTP user’s root directory. In this case, all FTP logins will go to /media/ftpdata/sqlftp. By changing this value on a per-user basis, you can give different FTP users their own directories without the need to create actual Linux user accounts. Of course, you must still create those directories.

shell

Set to /bin/false by default. This is more of a security precaution than anything else.

id

ProFTP will not be using this field, but we will add it anyway so that each user record in the users table can be identified by a unique identifying number called the primary key (PK). The primary key is special. It must always be uniwue, and it must always auto-increment. Set the extra options for the primary key:

  • PK (This is the primary key. Only one field may have this set.)
  • NN (Not Null. Must always contain a valid value.)
  • UQ (Unique. No duplicate keys are allowed within the same users table.)
  • AI (Auto-Increment. Automatically generate and assign the next higher number when a new record is added to the table.)
  • INT (11)  (The primary key is an integer value — A number 0 or greater.)

MySQL documentation will provide more information about table configuration.

With the table setup complete, click Apply. There will be a confirmation dialog, so accept it. If all went well, the changes will be applied.

This handy feature of MySQL is that the database server handles table management automatically for us so all we need to

Oops! I Made a Mistake

You can alter any table at any time to make corrections or add more fields. Right-click the table under the ftp schema and choose Alter Table. You will return to the table alteration window where you can make the changes.

There is no need to restart the MySQL server after making changes to a table. Changes are effectively instantly.

Add FTP Users to the users Table

Right-click the users table under the ftp schema and choose Select Rows – Limit 1000 at the top. This shows the current table contents. Add two users to the users table: yoohoo and kittypellet. Choose unique names that do not match any Linux accounts so we know that SQL FTP is working.

Click a blank cell under the userid column and type the name of the FTP username you want to add. Do the same for the passwd field. Pick any names and passwords you like. The information here is what will determine who may log into the ProFTP server. Press Enter to keep the text.

Yes, we are using plaintext passwords for this example. Best practice is to use password hashes, which ProFTP supports. However, we are setting up the MySQL-ProFTP communication, and plaintext passwords allow for easy entry and testing. If we cannot log in, we can look at the table to verify that the correct password is being used instead of guessing a hash value.

Just enter a username and password for each new FTP user you wish to add. MySQL will take care of the rest.

Notice that the fields containing default values will appear as null. This is normal. You may enter as many users as you like at a time. Click Apply when finished. The default values will now automatically fill the correct columns without our intervention. This is why default values are useful. They save us from retyping duplicate information.

This confirmation dialog forms an SQL statement from the modification made and attempts to apply them to the database.

Oops! An error occurred. In this case, a new user named woosh was added but his password was not set. Since the password field is set to Not Null (NN), a value is required. Thus, the error. Not Null ensures that each new user manually entered has a password associated with it.

ProFTP SQL Configuration

Now that we have the MySQL server set up, we need to tell ProFTP to use it. There are three configuration files we need to edit:

  • /etc/proftpd/modules.conf
  • /etc/proftpd/sql.conf
  • /etc/proftpd/proftpd.conf

Edit /etc/proftpd/modules.conf

This file tells which modules ProFTP must load. By default, MySQL is disable. Enable it by opening /etc/proftpd/modules.conf as root and uncommenting the following lines:

LoadModule mod_sql.c
LoadModule mod_sql_mysql.c

If you were using a different database, such as PostgreSQL or SQLite, then you would uncomment the appropriate modules.

(If you are using TLS encrpytion to encrypt your FTP session, be sure to uncomment LoadModule mod_tls.c also.)

Save and exit.

Edit /etc/proftpd/sql.conf

This file is important. It contains the MySQL server connection information as well as other connection options. SQL validation will not work if errors exist here. Open /etc/proftpd/sql.conf as root for editing.

Take note of these ProFTP directives, and make sure they are uncommented and set as shown:

SQLBackend mysql
SQLEngine on
SQLAuthenticate users
SQLAuthTypes Plaintext
SQLAuthTypes Backend
SQLConnectInfo  ftp@localhost root ftp123
SQLMinID 500
SQLUsersInfo users userid passwd uid gid homedir shell

Below is a full listing of sql.conf used in this example. There are extra directives not covered yet.

#
# Proftpd sample configuration for SQL-based authentication.
#
# (This is not to be used if you prefer a PAM-based SQL authentication)
#

<IfModule mod_sql.c>
#
# Choose a SQL backend among MySQL or PostgreSQL.
# Both modules are loaded in default configuration, so you have to specify the backend 
# or comment out the unused module in /etc/proftpd/modules.conf.
# Use 'mysql' or 'postgres' as possible values.
#
SQLBackend mysql
#
SQLEngine on

SQLAuthenticate users
#
# Use both a crypted or plaintext password 
#SQLAuthTypes Crypt Plaintext
SQLAuthTypes Plaintext
#
# Use a backend-crypted or a crypted password
#SQLAuthTypes Backend Crypt 
SQLAuthTypes Backend
#
# Connection 
#SQLConnectInfo proftpd@sql.example.com proftpd_user proftpd_password

SQLConnectInfo ftp@localhost root ftp123
SQLMinID 500
#
# Describes both users/groups tables
#
#SQLUserInfo users userid passwd uid gid homedir shell
#SQLGroupInfo groups groupname gid members
#
#CreateHome on

SQLUserInfo users userid passwd uid gid homedir shell

SQLLog PASS updatelogin
SQLNamedQuery updatelogin UPDATE "count=count+1, lastlogin=now(), ip='%a' WHERE userid='%u'" users


SQLLog PASS recordlogindate
# INSERT INTO ftp.logins (userid, logindate) VALUES ('boo', now());
SQLNamedQuery recordlogindate INSERT "'%u', now(), '%a', '%{protocol}'" logins


# Record uploads
SQLLog STOR recordupload
SQLNamedQuery recordupload INSERT "'%u', '%a', now(), '%f', %b, '%d'" uploads

# Record downloads
SQLLog RETR recorddownload
SQLNamedQuery recorddownload INSERT "'%u', '%a', now(), '%f', %b, '%d'" downloads


# Record FTP Action
#SQLLog * recordaction
#SQLNamedQuery recordaction INSERT "'%u', '%a', now(), '%r'" actions

</IfModule>

The ProFTP documentation provides details about each directive, but two important directives that should be explained here in order to reduce connection errors are SQLConnectInfo and SQLUserInfo.

SQLConnectInfo

This directive specifies the database to connect to.

SQLConnectInfo structure.

Contrary to what you might expect, ftp@localhost does not refer to a user account named ftp on localhost. It indicates the schema (named ftp) to use after ProFTP logs into MySQL. (This is ProFTP’s syntax.) The root and ftp123 following ftp@localhost specify the username and password of a MySQL account. In this case, it is the root account that was created when MySQL was installed.

Best security practice would be to create a limited MySQL account, and let ProFTP connect using that. Yes, the MySQL password is stored in plaintext in sql.conf, so make sure that world users cannot read the file to see the password. For now, we will keep the default permissions.

SQLUserInfo

This is the table within the ftp schema (specified by SQLConnectInfo) that ProFTP will check to authenticate an FTP login. The first argument after the SQLUserInfo directive is the table containing the usernames and passwords. In this case, we have a MySQL table named users that contains this information, so we must specify the users table. The table name in sql.conf must match the name of the table in the ftp schema. We can change the table name to something like ftpusers, but we must also change SQLUserInfo to use ftpusers also.

Breakdown of the SQLUserInfo ProFTP directive.

The arguments following SQLUserInfo users are the names of the columns (fields) that contain specific details for a user (username, password, shell, and so on). These are required, and they must match the column names in the users table. Any differences will return errors when starting proftpd. You can add extra fields to the users table, but they will be ignored during authentication. These fields must appear first and in this order with matching names in the table and in sql.conf.

Save and exit sql.conf.

The proftpd documentation is rather abstract and not very helpful for new users to understand, so, hopefully, these descriptions and diagrams offer insight. These two directives were a major source of confusion and errors when writing this article. Now, it makes better sense in the world of ProFTP.

Edit /etc/proftpd/protpd.conf

We will use the same proftpd.conf from the virtual host example in previous articles. Open /etc/proftpd/proftpd.conf as root for editing. Simply add a third virtual host to proftpd.conf.

# SQL FTP
<VirtualHost 192.168.2.30>
    ServerName "SQL FTP"
    Port 5000

    Include /etc/proftpd/sql.conf
    RequireValidShell off
    DefaultRoot ~

    SQLDefaultHomeDir /media/ftpdata/sqlftp
    SQLLogFile /var/log/proftpd/sql.log

    TransferLog /var/log/proftpd/sqlftp_xfer.log

    <Limit LOGIN>
        AllowAll
    </Limit>
</VirtualHost>

A few notes:

Port 5000

Since this virtual server is running alongside two other anonymous virtual hosts on the same IP address, we must assign a unique port. Port 5000 was free, so that was chosen. Keep in mind that FTP uses two ports. The named port and the port immediately before it. In this case, ports 5000 and 4999 are used. Double check to ensure that you do not assign ports in use by other services.

Include /etc/proftpd/sql.conf

We want the MySQL authentication to only be performed with this virtual host, not the other two, so we add the SQL Include directive here for this virtual host, not in the Global context.

SQLDefaultHomeDir /media/ftpdata/sqlftp

After a user authenticates using SQL, what should his root directory be? This jails the user to the dedicated /media/ftpdata/sqlftp directory that we created earlier. This is a duplicate of the default homedir value in the users tables.

By changing this directory, we can assign that user a root directory different from other users regardless of the SQLDefaultHomeDir directive in proftpd.conf.

For example, if we set the homedir field of user yoohoo to /tmp (do not do this), then yoohoo will be limited to the system’s /tmp directory upon login. Other users are limited to /media/ftpdata/sqlftp. This shows the value of a database. We can manage users from a central location instead of editing different configuration files.

SQLLogFile /var/log/proftpd/sql.log

SQL-related errors are reported in detail in the sql.log file, not in proftpd.log. This enables SQL logging, which we need during setup and testing.

TransferLog /var/log/proftpd/sqlftp_xfer.log

Records upload/download transfer information in a dedicated log file for the SQL FTP virtual host. This keeps log information separate from the other two virtual hosts.

Here is a full listing of proftpd.conf with three virtual hosts:

#
# /etc/proftpd/proftpd.conf -- This is a basic ProFTPD configuration file.
# To really apply changes, reload proftpd after modifications, if
# it runs in daemon mode. It is not required in inetd/xinetd mode.
#

# -----------------------------------------------------------------------------
# SERVER CONFIG
# -----------------------------------------------------------------------------
UseIPv6 off
MaxInstances 30
SystemLog /var/log/proftpd/proftpd.log

# Uncomment this if you are using NIS or LDAP via NSS to retrieve passwords:
# PersistentPasswd off

<IfModule mod_quotatab.c>
    QuotaEngine off
</IfModule>

<IfModule mod_ratio.c>
    Ratios off
</IfModule>

<IfModule mod_delay.c>
    DelayEngine on
</IfModule>

<IfModule mod_ctrls.c>
    ControlsEngine off
    ControlsMaxClients 2
    ControlsLog /var/log/proftpd/controls.log
    ControlsInterval 5
    ControlsSocket /var/run/proftpd/proftpd.sock
</IfModule>

<IfModule mod_ctrls_admin.c>
    AdminControlsEngine off
</IfModule>

Include /etc/proftpd/modules.conf

# -----------------------------------------------------------------------------
# GLOBALS
# -----------------------------------------------------------------------------

<Global>
    DisplayLogin welcome.msg
    DisplayChdir .message
    DenyFilter \*.*/,"%"
    ListOptions "-l"
    DefaultRoot ~
    RequireValidShell on 
    MultilineRFC2228 on
    ShowSymlinks on

    TimeoutNoTransfer 600
    TimeoutStalled 600
    TimeoutIdle 1200
 
    Umask 022 022
    AllowOverwrite on

    TransferLog /var/log/proftpd/xfer.log 
    IdentLookups off

    MaxClients 30
    DeferWelcome on
</Global>


# This is required to use both PAM-based authentication and local passwords
# AuthOrder mod_auth_pam.c* mod_auth_unix.c

# =============================================================================
# Zippy's FTP (Secure)
# =============================================================================
<VirtualHost 192.168.2.30>
    ServerName "Zippy's FTP"
    Port 21
    DefaultServer on
 
    TransferLog /var/log/proftpd/zippy_xfer.log

    Include /etc/proftpd/tls.conf
    TLSEngine                  on
    TLSRequired                on
    TLSRSACertificateFile      /etc/proftpd/ssl/zippy.cert
    TLSRSACertificateKeyFile   /etc/proftpd/ssl/zippy.key

    DisplayLogin welcome.msg

    <Anonymous /media/ftpdata/zippyftp> 
        User zippy
        Group zippy
        AnonRequirePassword off
        UserAlias anonymous zippy

        <Limit LOGIN>
            AllowAll
        </Limit>

        HideUser root
        HideGroup root
    </Anonymous>
</VirtualHost>


# =============================================================================
# Bumble's FTP (Secure)
# =============================================================================
<VirtualHost 192.168.2.30>
    ServerName "Bumble's FTP"
    Port 4000

    TransferLog /var/log/proftpd/bumble_xfer.log

    Include /etc/proftpd/tls.conf
    TLSEngine                   on
    TLSRequired                 on
    TLSRSACertificateFile       /etc/proftpd/ssl/bumble.cert
    TLSRSACertificateKeyFile    /etc/proftpd/ssl/bumble.key

    DisplayLogin welcome.msg

    <Anonymous /media/ftpdata/bumbleftp> 
        User bumble
        Group bumble
        AnonRequirePassword off
        UserAlias anonymous bumble

        <Limit LOGIN>
            AllowAll
       </Limit>

       HideUser root
       HideGroup root
   </Anonymous>
</VirtualHost>

# SQL FTP
# -------------------------------------------------------------
<VirtualHost 192.168.2.30>
    ServerName "SQL FTP"
    Port 5000

    Include /etc/proftpd/sql.conf
    RequireValidShell off
    SQLDefaultHomeDir /media/ftpdata/sqlftp
    SQLLogFile /var/log/proftpd/sql.log

    TransferLog /var/log/proftpd/sqlftp_xfer.log

    DisplayLogin welcome.msg

    <Limit LOGIN>
        AllowAll
    </Limit>
</VirtualHost>

This configuration creates three virtual hosts: Two anonymous with TLS encryption, and the third SQL-based authentication without encryption. There have been a few changes. The IP address is different. The TLS directives were moved from the Global context to each anonymous virtual host. This way, only TLS encryption is applied to the anonymous servers but not the SQL FTP server. We will add encryption later. For now, we want to make sure everything works.

Save proftpd.conf and exit.

Restart ProFTP

Anytime we make any changes to any of ProFTP’s configuration files, we must restart the server to apply the changes.

sudo service proftpd restart

If any errors occur, then they will appear in the terminal.

Oops! Misspelled the SQLConnectInfo directive as SQLConectInfo, and this prevented proftpd from starting.

Testing the Login

ProFTP under this configuration is running three virtual hosts. The two anonymous hosts are not required for SQL usage, so you can ignore them in this example.

First, test that you can log in to the each virtual host anonymously as before. A TLS certificate confirmation prompt should appear. If both are working and you can log into them anonymously and upload/downlaod files according to the permissions set, then all is good.

Now, test the SQL FTP virtual host. This one does not have TLS encryption yet, so all login will be cleartext. Log in using one of the usernames and its password as entered in the MySQL users table. FileZilla is used as the FTP client here.

  • IP: 192.168.2.30
  • Port: 5000
  • Username: yoohoo
  • Password: yoohoo

FileZilla logged into the virtual FTP server after using MySQL for user authentication.

Remember to use the correct port that the SQL FTP server is running on. In this case, it is port 5000, not the default port 21. A premade text file named sqlftp_welcome.txt shows that the user yoohoo has successfully logged into the FTP server listening on port 5000.

Disconnect, and try logging in as the other user listed in the users table: kittypellet.

  • IP: 192.168.2.30
  • Port: 5000
  • Username: kittypellet
  • Password: kittypellet

FileZilla with user kittypellet logged in.

Notice that users yoohoo and kittypellet only exist in the MySQL table users. Neither exists as an actual user account on the Linux system. With this configuration, regular users can still log in to Linux from the virtual host listening on port 5000, but they will be jailed to their home directories on the system, not /media/ftpdata/sqlftp.

Denying User Logins

As it is now, any user listed in the users table may log in. What if we want to deny user login without removing the user from the users table?

To do this with the current configuration, we would have to delete the user from the users table. An easier way is the change the shell so only users who have valid shells, such as /bin/bash, in their records are allowed to log in.

Edit /etc/proftpd/proftpd.conf

First, we need to modify /etc/proftpd/proftpd.conf as root. In the VirtualHost context for the SQL FTP server, set the RequireValidShell directive to on.

RequireValidShell on

Save and exit proftpd.conf. Restart proftpd.

sudo service proftpd restart

Edit the users Table

Back in MySQL Workbench, open the users table in the ftp schema, and change the shell of user yoohoo from /bin/false to /bin/bash. Click Apply to save the changes.

MySQL Workbench. The users table has been modified so that user yoohoo has the shell /bin/bash.

Now, try to log into ProFTP again at port 5000 as user yoohoo. The login should be allowed normally. Disconnect and try to log in as user kittypellet. kittypellet should be denied login. If so, then all is working as intended.

Deny User Login by Shell

FTP users must have a valid shell listed in the users table in order to log in. If the shell is /bin/false or not found in /etc/shells, then the user cannot log in. By changing the shell to /bin/false in the users table, a user can be denied access without deleting that user from the table. This becomes important as more fields are added to the table to record user activity and you do not want to lose that history information.

Note: Do not rely upon MySQL’s NULL value in the users table to deny access for a user. If Not Null is unchecked and a user’s shell is set to Null, he can still log in without a valid shell. Keep the Not Null option and set a user’s shell to /bin/false to deny logins.

You can change the default shell in the users table to /bin/bash (or any other supported shell) so that newly added users will be allowed to log in by default. This will save extra typing for you.

Encrypting the Connection

Let’s encrypt the FTP session for the virtual host listening on port 5000. We will need to create a certificate and key for the FTP server.

Create a Dedicated SSL Directory

This is to hold the certificate and key files we will create. Make this directory if it does not already exist.

sudo mkdir /etc/proftpd/ssl

Generate the Certificate and Key

Install openssl if not already present on the virtual machine.

sudo apt install openssl

Next, run this command to create the files.

sudo openssl req -new -x509 -days 300 -nodes -out /etc/proftpd/ssl/sqlftp.cert -keyout /etc/proftpd/ssl/sqlftp.key

This command generates two files and stores them in /etc/proftpd/ssl to keep them organized.

  • sqlftp.cert – The X.509 certificate
  • sqlftp.key – The 2048-bit RSA private key file

During certificate generation, you will be prompted for information related to your FTP server. Enter whatever you like.

OpenSSL certificate generation.

Change the permissions of your certificate to 600 (read/write for root only) or else proftpd will not start.

sudo chmod 600 /etc/proftpd/ssl/sqlftp.cert

Only the certificate, not the key, must be set so only root may read it. For extra security, change permissions to all certificates and keys to 600 in /etc/proftpd/ssl.

Edit proftpd.conf

Open /etc/proftpd/proftpd.conf as root for editing. In the VirtualHost context for the SQL FTP server, add five directives.

Include /etc/proftpd/tls.conf
TLSEngine on
TLSRequired on
TLSRSACertificateFile /etc/proftpd/ssl/sqlftp.cert
TLSRSACertificateKeyFile /etc/proftpd/ssl/sqlftp.key

The VirtualHost context for the SQL FTP server in /etc/proftpd/proftpd.conf should now look like this:

# SQL FTP
# ------------------------------------------------------
<VirtualHost 192.168.2.30>
    ServerName "SQL FTP"
    Port 5000

    Include /etc/proftpd/sql.conf
    RequireValidShell on
    SQLDefaultHomeDir /media/ftpdata/sqlftp
    SQLLogFile /var/log/proftpd/sql.log

    Include                     /etc/proftpd/tls.conf
    TLSEngine                   on
    TLSRequired                 on
    TLSRSACertificateFile       /etc/proftpd/ssl/sqlftp.cert
    TLSRSACertificateKeyFile    /etc/proftpd/ssl/sqlftp.key

    TransferLog /var/log/proftpd/sqlftp_xfer.log
 
    <Limit LOGIN>
        AllowAll
    </Limit>
</VirtualHost>

 

If this is the only virtual host, we can remove the port, and the server will automatically run on port 21 as FTP is expected.

Restart proftpd and try to log in again with FileZilla as user yoohoo.

sudo service proftpd restart

You should see a certificate confirmation dialog.

Since this is a self-signed certificate, users will be presented with this confirmation before being allowed to continue connecting. Do you trust and accept? Of course! Choose OK.

If yoohoo logins in, then all is good. You should see TLS-related information in FileZilla’s log at the top.

TLS connection established. The FTP session is now secure. All passwords, commands, and data transfers are encrypted to prevent network sniffers from watching your actions.

Adding a New User

To allow another user to log in, simply add him to the users table in the ftp schema. Let’s add a user named slimeygadget. Notice that no user named slimeygadget exists on the Linux system, but he will be granted FTP access if he exists in the users table.

Just add a new userid and password, and click Apply. The default values will fill in the other fields.

Try logging in as user slimeygadget with the assigned password of password. Access is denied. Why? Because slimeygadget’s shell is set to /bin/false.

Even if a TLS connection is established, a user must have a valid shell specified in order to log in.

This denies him from logging in because it is not a valid shell. The RequireValidShell directive requires that a user have a valid shell in order to log in. Let’s change this shell to /bin/bash in MySQL Workbench.

slimeygadget’s shell changed to /bin/bash.

Now, slimeygadget can log in.

slimeygadget logged in with a valid /bin/bash shell.

 

The SQL Log File

Recall the directive SQLLogFile /var/log/proftpd/sql.log? This becomes essential when using MySQL. All SQL-related activity is listed here, including errors. If you ever suspect that something is not working between ProFTP and MySQL, check this log file. If can often help you diagnose database connection issues.

sudo cat /var/log/proftpd/sql.log

What Next?

MySQL authentication can take a while to set up, but once the system is in place, we can expand the capabilities of ProFTP in ways not possible before.

  • Add a user login counter that increments upon each login
  • Create an uploads table that records the IP address, username, date, and filename of each upload
  • Record the name of every file downloaded
  • For the super-spy FTP admin, create a table that logs every FTP command to watch user activity
  • Enable an FTP quota system so users do not download more than they upload within thresholds
  • Record a history of login dates for each user

These are just a few things we can do with a database. They go beyond simple logfile monitoring and add a level of FTP server customization.

Have fun!

Advertisements

, ,

  1. Leave a comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s

%d bloggers like this: