Proftpd specific user configuration from MySQL

I have already set up a proftpd server with a MySQL connection. Everything works fine.

I would like to set specific permissions for each user from the database using (PathAllowFilter, PathDenyFilter, ...)

The server running on Ubuntu 12.04 LTS distribution.

It is not so easy, there is no single module to do this. But I found a solution for this.

It's not optimal because you have to restart ProFTPd server each time you change MySQL configuration, but it works.

As you have a ProFTPd server that already run with MySQL, i will explain only the part of specific user configuration.

For this solution you need ProFTPd to be compiled with these modules:

• mod_ifsession (with this module you will be able to configure <IfUser> conditions)
• mod_conf_sql (with this module you will be able to load configuration from MySQL)

To help you with ProFTPd recompilation, you can run this command proftpd -V to see how your version is configured. You can found some documentation here.

Once you have compiled your ProFTPd server and it's run, you will have to log on your MySQL server.

If you read mod_conf_sql, they say to create 3 tables ftpctxt, ftpconf, ftpmap. We will not create these tables unless you want to have global configuration from MySQL.

We will fake the MySQL configuration with "views".

1. First you add each specific configuration as user column (make sure to have a default value):

ALTER TABLE ftpuser #
ADD PathDenyFilter VARCHAR( 255 ) NOT NULL DEFAULT '(\.ftp)|(\.hta)[a-z]+$';`

ALTER TABLE ftpuser 
ADD PathAllowFilter VARCHAR( 255 ) NOT NULL DEFAULT '.*$';`
....

2. Create the conf view:

• User's id and configuration column are concatenated to make an unique id
• User's configuration column is used as type
• User's configuration value is used as info

• View is an union of selects (for every column an union is required)

  CREATE VIEW ftpuser_conf AS SELECT concat(ftpuser.id,'-PathDenyFilter') 
  AS id,'PathDenyFilter' AS type,ftpuser.PathDenyFilter AS info from ftpuser 
  UNION 
  SELECT concat(ftpuser.id,'-PathAllowFilter')
  AS id,'PathAllowFilter' AS type, ftpuser.PathAllowFilter AS info 
  from ftpuser;
  

3. Create the ctxt view

• This view is a concatenation of a "Default" row and user's rows ("Default" row has 1 as id and user's rows have user's id + 1 as id.
• Concatenate "userconf-" and user's id as name
• "IfUser" as type

• User's username as info

  CREATE VIEW ftpuser_ctxt AS
    SELECT 1 AS id,NULL AS parent_id, 'default' AS name, 'default' AS type, NULL AS info
    UNION
    SELECT (ftpuser.id + 1) AS id,1 AS parent_id,
           concat('userconf-',ftpuser.userid) AS name,
           'IfUser' AS type,ftpuser.userid AS info
    FRON ftpuser;
  

4. Create the map view

• User's id and configuration column are concatenated for conf_id
• User's id + 1 for ctxt_id

• View is an union of selects (for every column an union is required)

  CREATE VIEW ftpuser_map 
  AS SELECT concat(ftpuser.id,'-PathDenyFilter') 
  AS conf_id,(ftpuser.id + 1) AS ctxt_id 
  from ftpuser 
  union 
  select concat(ftpuser.id,'-PathAllowFilter') 
  AS conf_id,(ftpuser.id + 1) AS ctxt_id 
  from ftpuser;
  

5. Add these lines to your ProFTPd configuration

<IfModule mod_conf_sql.c>
    Include sql://user:password@host/db:database/ctxt:ftpuser_ctxt:id,parent_id,type,info/conf:ftpuser_conf:id,type,info/map:ftpuser_map:conf_id,ctxt_id/base_id=1
</IfModule>

Where:

• user => your MySQL username
• password => your MySQL password
• host => your MySQL host
• database => your MySQL database

6. Restart your ProFTPd server

I hope this will help you. Good luck