ProFTPD module mod_conf_sql



The mod_conf_sql module can be used to store configuration information, as would normally be found in the proftpd.conf file, in SQL tables, and to have proftpd consequently retrieve that configuration information. Detailed usage instructions can be found here.

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

After unpacking the latest proftpd-1.2 code (at least 1.2.9rc1), copy the mod_conf_sql.c file into:
  proftpd-dir/contrib/
Then follow the normal steps for using third-party modules in proftpd, making sure to include mod_sql and a SQL backend module (either mod_sql_mysql or mod_sql_postgres), e.g.:
  ./configure --with-modules=mod_sql:mod_sql_mysql:mod_conf_sql
  make
  make install


Usage

Conceptually, there are two basic elements in proftpd.conf: contexts and directives. Contexts include <Anonymous>, <VirtualHost>, the "server config" default context, and conditional contexts such as <IfDefine> and <IfModule>. Configuration directives are contained within a context.

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
  );
Example PostgresQL schema:
  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
  );
Each context and configuration directive is assigned a unique ID. The ftpmap table maps the configuration directive to its appropriate context by IDs. In addition, each context has a parent context, which allows for nested contexts, such as:
  <Directory incoming>
    <Limit DELE MKD RMD STOR XMKD XRMD>
      DenyAll
    </Limit>
  </Directory>
The one allowed exception to this parent requirement context is the "server config" context, which is the default context in the proftpd.conf file. This top-level context must have a NULL parent ID.

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 syntax is long, but it has to be so in order to provide all of the information mod_conf_sql needs. (This information cannot be stored in the configuration file because mod_conf_sql will be constructing that configuration file).

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
Explicitly specifying the column names in the above URI would make the "path" look like:
  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
This example shows a URI that causes mod_conf_sql to treat the context with an ID of of baseid as the toplevel configuration context, rather than the default:
  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>
This tells mod_conf_sql to look for a row in the ftpctxt table whose ID is 7, and then to recurse through the contents of this "vhost" context.

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
Note that the script currently allows the SQL table names to be specified via command-line options, but the column names are assumed to be those mentioned above.

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
Note that the script currently allows the SQL table names to be specified via command-line options, but the column names are assumed to be those mentioned above.



Author: $Author: tj $
Last Updated: $Date: 2004/08/01 22:25:44 $


© Copyright 2003,2004 TJ Saunders
All Rights Reserved