<!-- $Id: mod_sftp_sql.html,v 1.4 2010/02/04 19:26:53 castaglia Exp $ --> <!-- $Source: /cvsroot/proftp/proftpd/doc/contrib/mod_sftp_sql.html,v $ --> <html> <head> <title>ProFTPD module mod_sftp_sql</title> </head> <body bgcolor=white> <hr> <center> <h2><b>ProFTPD module <code>mod_sftp_sql</code></b></h2> </center> <hr><br> <p> The <a href="http://www.castaglia.org/proftpd/modules/mod_sftp.html"><code>mod_sftp</code></a> module for ProFTPD can support different storage formats for its user- and host-based authorized keys. By default, the <code>mod_sftp</code> module supports storing authorized keys in flats. This <code>mod_sftp_sql</code> module allows for authorized SSH keys to be stored in SQL tables. <p> This module is contained in the <code>mod_sftp_sql.c</code> file for ProFTPD 1.3.<i>x</i>, and is not compiled by default. Installation instructions are discussed <a href="#Installation">here</a>. Examples of how to use the <code>mod_sftp_sql</code> module are available <a href="#Usage">here</a>. <p> The most current version of <code>mod_sftp_sql</code> can be found at: <pre> <a href="http://www.castaglia.org/proftpd/">http://www.castaglia.org/proftpd/</a> </pre> <h2>Author</h2> <p> Please contact TJ Saunders <tj <i>at</i> castaglia.org> with any questions, concerns, or suggestions regarding this module. <p> <hr> <h2><a name="Installation">Installation</a></h2> To install <code>mod_sftp_sql</code>, copy the <code>mod_sftp_sql.c</code> file into: <pre> <i>proftpd-dir</i>/contrib/ </pre> after unpacking the latest proftpd-1.3.<i>x</i> source code. Then follow the usual steps for using third-party modules in proftpd, making sure to include the <code>mod_sftp</code> and <code>mod_sql</code> modules, which <code>mod_sftp_sql</code> requires. For example, if you use MySQL as your SQL database, then you might use: <pre> ./configure --with-modules=mod_sql:mod_sql_mysql:mod_sftp:mod_sftp_sql ... make make install </pre> <p> <hr><br> <h2><a name="Usage">Usage</a></h2> <p> The <code>mod_sftp_sql</code> module works by using <code>mod_sql</code>'s <code>SQLNamedQuery</code> ability to define a SQL <code>SELECT</code> statement which returns the requested key. Thus the <code>mod_sftp_sql</code> module has no configuration directives of its own. <p> To help demonstrate, see the example configuration below: <pre> <IfModule mod_sql.c> # Other mod_sql configuration here # Define a SELECT statement to retrieve users' authorized SSH keys SQLNamedQuery get-user-authorized-keys SELECT "key FROM sftpuserkeys WHERE name='%U'" # Define a SELECT statement to retrieve hosts' authorized SSH keys SQLNamedQuery get-host-authorized-keys SELECT "key FROM sftphostkeys WHERE host='%{0}'" </IfModule> <IfModule mod_sftp.c> SFTPEngine on SFTPLog /path/to/sftp.log # Host keys, for server host authentication SFTPHostKey /etc/ssh_host_dsa_key SFTPHostKey /etc/ssh_host_rsa_key <IfModule mod_sftp_sql.c> # Instead of using a file-based key store, we tell mod_sftp to use # the SQL-based key store provided by mod_sftp_sql SFTPAuthorizedUserKeys sql:/get-user-authorized-keys SFTPAuthorizedHostKeys sql:/get-host-authorized-keys </IfModule> </IfModule> </pre> <p> What should the schema be, for the table which holds these authorized keys? The <b>required</b> columns are one for the key (as a single base64-encoded string) and one for the name of the entity owning that key, <i>e.g.</i> the user name or FQDN (or IP address) of the host. These columns can be added to existing tables you might have, or be part of a new table. <p> For example, using SQLite, you could do: <pre> # sqlite3 sftp.db sqlite> CREATE TABLE sftpuserkeys ( sqlite> name TEXT NOT NULL, sqlite> key BLOB NOT NULL sqlite> ); sqlite> CREATE INDEX sftpuserkeys_idx ON sftpuserkeys (name); sqlite> CREATE TABLE sftphostkeys ( sqlite> host TEXT NOT NULL, sqlite> key BLOB NOT NULL sqlite> ); sqlite> CREATE INDEX sftphostkeys_idx ON sftphostkeys (host); </pre> and then configure <code>mod_sql</code> to use that <code>sftp.db</code> database file. The indices are a very good idea, especially if you have many rows and/or users. And for good data hygiene, adding a foreign key constraint on the <code>sftpuserkeys.name</code> column to your normal users table is recommended. <p> An example MySQL schema looks like: <pre> CREATE TABLE sftpuserkeys ( name VARCHAR(255) NOT NULL, key VARCHAR(255) NOT NULL ); CREATE INDEX sftpuserkeys_idx ON sftpuserkeys (name); CREATE TABLE sftphostkeys ( host VARCHAR(255) NOT NULL, key VARCHAR(255) NOT NULL ); CREATE INDEX sftphostkeys_idx ON sftphostkeys (host); </pre> <p> Which leads to the next question: how can I transfer existing authorized SSH keys from their current flat files into the SQL tables? First, you need to make sure that the key is in the RFC4716 format, using: <pre> # ssh-keygen -e -f /path/to/key.pub </pre> Then simply add the output data to your SQL table (<i>e.g.</i> to the <code>sftpuserkeys.key</code> column in the above example schema). <p> Other databases (<i>e.g.</i> MySQL, Postgres, Oracle, <i>etc</i>) have bulk data loading tools which can also be used to load a CSV file containing keys into your SQL tables, for use via <code>mod_sftp_sql</code>. <p> <hr><br> Author: <i>$Author: castaglia $</i><br> Last Updated: <i>$Date: 2010/02/04 19:26:53 $</i><br> <br><hr> <font size=2><b><i> © Copyright 2009-2010 TJ Saunders<br> All Rights Reserved<br> </i></b></font> <hr><br> </body> </html>