Using ProFTPd with PostgreSQL (...and Rails)

This is an overview of how we will use ProFTPd to link up FTP accounts with Rollbook accounts and users. It’s fairly simple and allows us to leave our data models untouched. For security, care has been taken to ensure all database access by the ftp daemon is read-only and segregated to its own schema.
How it Works

For ProFTPd to use an sql backend for authentication, it needs to be able to query a table and get back a result of the form username, password, uid, gid, homedir, shell .
result field 	example result entry 	Description
username 	admin.sysadmin 	#{user.account.subdomain}.#{user.login}
password 	notreallythepassword 	#{user.password}
uid 	account_id+n
	In this setup, this and the gid are going to be the uid/gid of the account_id + an offset determined by the type of ftp account.  If the account type is course, then n == 40000.  If the account type is integration, then n == 10000.
gid 	account_id+n
homedir 	/home/ftp/admin 	This is of the form /home/ftp/#{user.account.subdomain}. We can change this to whatever we want; just using /home/ftp for this example
shell 	/usr/sbin/nologin 	Static; I don’t think we want users to have shell access.

We’re going to create a new schema and view called ‘ftp.users’ that aggregates the users and accounts tables into the format above.



The PostgreSQL setup

First, we create a new user with no create privileges called ‘ftp’ from the login shell.t
createuser -U postgres -SDR ftp


Then we connect to the database as a superuser
psql -U postgres rollbook_development


...create the schema for proftpd to access
CREATE SCHEMA ftp;


...and create the view to be queried by the ftp daemon.
CREATE VIEW ftp.users as
 SELECT (accounts.subdomain::text || ’.’::text) || users.login::text AS login,
  users.password,
  ’/usr/sbin/nologin’::text AS shell,
  21 AS uid,
  21 AS gid,
  ’/home/ftp/’::text || accounts.subdomain::text AS homedir
 FROM users, accounts
 WHERE users.account_id = accounts.id;

Test out a query…
select * from ftp.users where login = ‘admin.sysadmin’;

     login      | password |       shell       | uid | gid |     homedir     
----------------+----------+-------------------+-----+-----+-----------------
 admin.sysadmin | glib3mop | /usr/sbin/nologin |  21 |  21 | /home/ftp/admin

...and remember to give the ftp user read access to the schema and view.
GRANT USAGE ON SCHEMA ftp to ftp;
GRANT SELECT ON ftp.users to ftp;

note: If we do this, the above commands should be in a migration so we don’t lose track of the setup.


The ProFTPd setup

These are the directives in /etc/proftpd/proftpd.conf which set up sql authentication.

# Use sql authentication first, then fallback to /etc/passwd 
AuthOrder         mod_sql.c mod_auth_unix.c
# We're using plaintext passwords
SQLAuthTypes      Plaintext
SQLAuthenticate   users
# database connection info in the form of database@host username [password]
SQLConnectInfo    rollbook_development@localhost ftp
# database schema.table to use, and a list of column names to query 
# for the expected result
SQLUserInfo       ftp.users login password uid gid homedir shell

More info on various ProFTPd configuration directives and options can be found here .


possible but unexplored: handling encrypted passwords; triggers that fire sql statements depending on which ftp command is received (i.e. logging all STOR commands in a table to keep track of disk usage); bandwidth throttling; quota settings and syncing them with rollbook http upload account quotas