The mod_sftp module for ProFTPD can support different storage formats for its user- and host-based authorized keys. By default, the mod_sftp module supports storing authorized keys in flats. This mod_sftp_sql module allows for authorized SSH keys to be stored in SQL tables.

This module is contained in the mod_sftp_sql.c file for ProFTPD 1.3.x, and is not compiled by default. Installation instructions are discussed here. Examples of how to use the mod_sftp_sql module are available here.

The most current version of mod_sftp_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_sftp:mod_sftp_sql ...
  make
  make install

Usage

The mod_sftp_sql module works by using mod_sql's SQLNamedQuery ability to define a SQL SELECT statement which returns the requested key. Thus the mod_sftp_sql module has no configuration directives of its own.

To help demonstrate, see the example configuration below:

  <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>

What should the schema be, for the table which holds these authorized keys? The required columns are one for the key (as a single base64-encoded string) and one for the name of the entity owning that key, e.g. 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.

For example, using SQLite, you could do:

  # sqlite3 sftp.db
  sqlite> CREATE TABLE sftpuserkeys (
  sqlite>  name TEXT NOT NULL,
  sqlite>  key BLOB NOT NULL
  sqlite> );

  sqlite> CREATE TABLE sftphostkeys (
  sqlite>  host TEXT NOT NULL,
  sqlite>  key BLOB NOT NULL
  sqlite> );

Which leads to the next question: how can I transfer existing authorized SSH keys from their current flate files into the SQL tables? The mod_sftp_sql source code is distributed with a extract-rfc4716-key.pl Perl script which can be used to read an existing authorized keys file and print out the string which should be added to the database table. Note that the extract-rfc47167-key.pl script will print out the key data for each key contained in the file, one key per line, to stdout.

  # extract-rfc4716-key.pl ~/.sftp/authorized_keys 
AAAAB3NzaC1yc2EAAAABIwAAAIEA6d+bt06KSlp4Q6wMS57hFAEaiNIQfdguCN9uMP3C5dC4kCt/S85jYt3cdB9XECr8AIxCsivay8WTw77z4qBgR6XrOrzb2J57mX27oQxi4kjHNY3vcVU0Y9lRa6M8whdokpWQsgGaPDmoa2ScU56r0FFiHy0cDX+dDU3ycniQSwc=

  # sqlite sftp.db
  sqlite> INSERT INTO sftpuserkeys (name, key) VALUES ('tj', 'AAAAB3NzaC1yc2EAAAABIwAAAIEA6d+bt06KSlp4Q6wMS57hFAEaiNIQfdguCN9uMP3C5dC4kCt/S85jYt3cdB9XECr8AIxCsivay8WTw77z4qBgR6XrOrzb2J57mX27oQxi4kjHNY3vcVU0Y9lRa6M8whdokpWQsgGaPDmoa2ScU56r0FFiHy0cDX+dDU3ycniQSwc=');

Other databases (e.g. MySQL, Postgres, Oracle, etc) have bulk data loading tools which can also be used to load a CSV file containing keys into your SQL tables, for use via mod_sftp_sql.