mod_conf_sqlmodule can be used to store configuration information, as would normally be found in the
proftpd.conffile, in SQL tables, and to have
proftpdconsequently 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:
Please contact TJ Saunders <tj at castaglia.org> with any questions, concerns, or suggestions regarding this module.
proftpd-dir/contrib/Then follow the normal steps for using third-party modules in proftpd, making sure to include
mod_sqland a SQL backend module (either
./configure --with-modules=mod_sql:mod_sql_mysql:mod_conf_sql make make install
proftpd.conf: contexts and directives. Contexts include
<VirtualHost>, the "server config" default context, and conditional contexts such as
<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
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
Here is an example MySQL schema for the tables
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
ftpmaptable 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.conffile. This top-level context must have a
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
--config command-line option. The specific
"path" to use for
mod_conf_sql uses an URI-like
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_sqlneeds. (This information cannot be stored in the configuration file because
mod_conf_sqlwill 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:ftpmapExplicitly 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_idThis example shows a URI that causes
mod_conf_sqlto 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 18.104.22.168> Include sql://foo:bar@localhost/db:proftpd/ctxt:vhostctxt/conf:vhostconf/map:vhostmap/base_id=7 </VirtualHost>This tells
mod_conf_sqlto look for a row in the
ftpctxttable whose ID is 7, and then to recurse through the contents of this "vhost" context.
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
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.
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
conf2sql.pl script reads a given
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.
conf2sql.pl --dbdriver=mysql --dbname=proftpd --dbuser=foo --dbpass=bar --dbserver=localhost /etc/proftpd.confNote 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.
sql2conf.pl script reads the SQL tables and reconstructs
proftpd.conf configuration file represented by those
tables. One specifies the database connection information. Use
sql2conf.pl --help to see usage information.
sql2conf.pl --dbdriver=mysql --dbname=proftpd --dbuser=foo --dbpass=bar --dbserver=localhostNote 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.