This module is contained in the mod_conf_sql.c file for ProFTPD 1.2.x, and is not compiled by default. Installation instructions are discussed here.

The most current version of mod_conf_sql can be found at:

  http://www.castaglia.org/proftpd/

Author

Please contact TJ Saunders <tj at castaglia.org> with any questions, concerns, or suggestions regarding this module.

Installation

  proftpd-dir/contrib/

  ./configure --with-modules=mod_sql:mod_sql_mysql:mod_conf_sql
  make
  make install

Usage

Thus, to represent the configuration file contents within SQL tables, three things are needed: a table of contexts, a table of configuration directives, and a table that describes which directives are contained within which contexts. mod_conf_sql thus relies on a database schema of three tables. The SQL queries it uses rely on inner joins; this means that the underlying database must support INNER JOINs.

Database Schema
Here is an example MySQL schema for the tables mod_conf_sql uses:

  DROP TABLE ftpctxt;
  CREATE TABLE ftpctxt (
    id INTEGER UNSIGNED UNIQUE PRIMARY KEY NOT NULL AUTO_INCREMENT,
    parent_id INTEGER UNSIGNED,
    name VARCHAR(255),
    value VARCHAR(255)
  );

  DROP TABLE ftpconf;
  CREATE TABLE ftpconf (
    id INTEGER UNSIGNED UNIQUE PRIMARY KEY NOT NULL AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    value BLOB
  );

  DROP TABLE ftpmap;
  CREATE TABLE ftpmap (
    conf_id INTEGER UNSIGNED NOT NULL,
    ctxt_id INTEGER UNSIGNED NOT NULL
  );

  DROP TABLE ftpctxt;
  CREATE TABLE ftpctxt (
    id SERIAL,
    parent_id INTEGER,
    name VARCHAR(255),
    value VARCHAR(255),
    PRIMARY KEY (id)
  );

  DROP TABLE ftpconf;
  CREATE TABLE ftpconf (
    id SERIAL,
    name VARCHAR(255) NOT NULL,
    value TEXT,
    PRIMARY KEY (id)
  );

  DROP TABLE ftpmap;
  CREATE TABLE ftpmap (
    conf_id INTEGER NOT NULL,
    ctxt_id INTEGER  NOT NULL
  );

  <Directory incoming>
    <Limit DELE MKD RMD STOR XMKD XRMD>
      DenyAll
    </Limit>
  </Directory>

Configuration URI
How does mod_conf_sql do its magic? This module uses ProFTPD's FSIO API to temporarily redefine what it means to open and read a file; it presents a file-like interface to a SQL table such that ProFTPD's configuration parser does not know that the configuration is coming from a database rather than a flat file.

In order to accomplish this magic, mod_conf_sql needs to know some things about the database, so that it can connect and retrieve the configuration data. This information is provided in the "path" to the configuration file, using proftpd's -c/--config command-line option. The specific "path" to use for mod_conf_sql uses an URI-like syntax:

  sql://dbuser:dbpass@dbserver/db:dbname
    /ctxt:table[:id,parent_id,key,value][:where=clause]
    /conf:table[:id,key,value][:where=clause]
    /map:table[:conf_id,ctxt_id][:where=clause]
    [/base_id=id]

The "sql://" prefix informs the FSIO API that this "path" should be handled differently from a normal Unix filesystem path. The dbuser, dbpass, and dbserver parts are the usual data needed to connect to a database; database server information, such as port number or Unix domain socket path, can be added to the dbserver string. dbname configures the name of the database to use. Then, one specifies the names of the three tables to use (the context table, the directives table, and the mapping table); one can optionally configure the column names in those tables. The default names of the table columns are shown in the example MySQL schema above. Note that if column names are specified, all of the columns in the table must be provided.

The following example shows a "path" where the table names are specified, but the column names in those tables are left to the default values:

  proftpd -c sql://foo:bar@localhost/db:proftpd/ctxt:ftpctxt/conf:ftpconf/map:ftpmap

  proftpd -c sql://foo:bar@localhost/db:proftpd/ctxt:ftpctxt:id,parent_id,type,info/conf:ftpconf:id,type,info/map:ftpmap:conf_id,ctxt_id

  proftpd -c sql://foo:bar@localhost/db:proftpd/ctxt:ftpctxt/conf:ftpconf/map:ftpmap/base_id=baseid

This URI-like path syntax can also be used as the parameter to the Include configuration directive. When doing so, it is very important that the /base_id=id URI syntax be used, so that the configuration parser knows the proper configuration data to retrieve from the SQL tables.

  <VirtualHost 1.2.3.4>
    Include sql://foo:bar@localhost/db:proftpd/ctxt:vhostctxt/conf:vhostconf/map:vhostmap/base_id=7
  </VirtualHost>

The mod_conf_sql module does not actually need mod_sql to be configured, using the normal mod_sql configuration directives; however, mod_conf_sql does require that mod_sql be compiled into proftpd. It is possible to store the configuration information in one database, and the user/group authentication information in a completely different database.

Importing/Exporting
While storing configuration information in SQL tables may make some tasks easier, it will making editing of configurations more complex. To help with this, mod_conf_sql is accompanied by two Perl scripts that can be used to import existing proftpd.conf files into a database, and to export configuration information from SQL tables back into a proftpd.conf file.

The conf2sql.pl script reads a given proftpd.conf configuration file and populates the SQL tables with the information from that file. One specifies the database connection information, and the full path to the proftpd.conf to be imported. The script will delete any existing information in the SQL tables. Use conf2sql.pl --help to see usage information.

Example:

  conf2sql.pl --dbdriver=mysql --dbname=proftpd --dbuser=foo --dbpass=bar --dbserver=localhost /etc/proftpd.conf

The sql2conf.pl script reads the SQL tables and reconstructs the proftpd.conf configuration file represented by those tables. One specifies the database connection information. Use sql2conf.pl --help to see usage information.

Example:

  sql2conf.pl --dbdriver=mysql --dbname=proftpd --dbuser=foo --dbpass=bar --dbserver=localhost