PostgreSQL and Proftpd

So you want to set up an ftp server, you need advanced functionality and you don't know where to turn? Try proftpd.

Part 1: Setting up proftpd with a postgresql backend

This tutorial is very niche, I'm going to cover only proftpd, and only insomuch as to get it operational with Postgres. A basic understanding of proftpd, and Postgres is assumed. This tutorial will get you up and running proftpd pretty fast, and having it utilize a database, if that database has triggers, the possibilities of this configuration are almost endless.

Getting Proftpd

First you will need proftpd, I'll be using Ubuntu's Dapper; there is a meta-package prepared courtesy of Francesco Paolo Lovergine entitled "proftpd-pgsql," which will set up a copy of proftpd compiled with the following modules. As a side note, while proftpd comes with the mods, the conf file is the standard proftpd boilerplate. I highly suggest you check out the mod_sql documentation, which while sub-par is still handy -- the docs on the proftpd site is very old use these instead: Link to docs. Also, be weary of the version, proftpd is now at 1.3, though i will be using 1.2.10 -- the version used in Dapper

The mods compiled into this version of proftpd are as follows

  mod_core.c
  mod_xfer.c
  mod_auth_unix.c
  mod_auth_file.c
  mod_auth.c
  mod_ls.c
  mod_log.c
  mod_site.c
  mod_auth_pam.c
  mod_quotatab.c
  mod_sql.c
  mod_sql_postgres.c
  mod_quotatab_sql.c
  mod_ratio.c
  mod_tls.c
  mod_rewrite.c
  mod_radius.c
  mod_wrap.c
  mod_quotatab_file.c
  mod_delay.c
  mod_readme.c
  mod_ifsession.c
  mod_cap.c
Go ahead and issue the command to get proftpd-pgsql: 
apt-get install proftpd-pgsql

Setting up Permissions.

For my purpose, (remember, I am the center of the universe) all I needed was a one dimensional structure, because groups are stored in a different level rather than unix permissions. So all files should come in with static permissions -- I only care about the owner and the owner's group. My plan was to make the proftpd default user, ftp, belong to his own aptly named group I was going to create -- 'ftp'. I was then going to make Postgres a member of this group, see part 2 for the reasoning. I needed both to be a member of the same group, and I needed the proftpd chroot folder to be 775, so thew whole group could add (move) files, I also made the folder owned by user 'ftp', with group 'ftp'. I accomplished this with the following script.

groupadd ftp
usermod -G ftp ftp
usermod -G ftp,postgres postgres
chown ftp:ftp ./ftp_directory/
chmod 775 ./ftp_directory/

Setting up Postgres

Now, we are going to have prepare our sql tables. I design utilizing schemas, and thats what the prefix of 'ftp.' on the tables is for. I've put all of my tables into an ftp schema, there is one thing worth nothing, I have been extremely restrictive on permissions for this project, because I'm not sure how safe mod_sql is. In doing so I have created a special user, named proftp, for proftpd in my database (which more applications utilize than proftpd), this user is restricted to selects on the auth_table, and inserts on the file_log table. I *highly* suggest you adopt some form of this policy. At the very least this prevents some degree of devastating sql injection attacks or information leaks in the event the mod_sql code is not as secure as we would like to think. A quick google reveals a previous security problem with the mod_sql codebase. Each query that mod_sql sends to the db is appeneded with a "LIMIT 1" clause, and with mod_delay also configured by default, I find this sufficient enough for this project.

My sql init script is as follows.

ALTER USER proftp UNENCRYPTED PASSWORD 'dealermadeftp';
CREATE SCHEMA ftp;
GRANT USAGE ON SCHEMA ftp TO proftp;

CREATE TABLE ftp.users (
        pkid      serial      PRIMARY KEY,
        userid    text        NOT NULL UNIQUE,
        passwd    text,
        uid       int,
        gid       int,
        homedir   text,
        shell     text
);
GRANT SELECT ON ftp.users TO proftp;
INSERT INTO ftp.users ( userid, passwd ) VALUES ( 'ecarroll', 'adm1n' );
INSERT INTO ftp.users ( userid, passwd ) VALUES ( 'jgallagher', 'adm1n' );

CREATE TABLE ftp.file_log (
        pkid               serial      PRIMARY KEY,
        userid             text        REFERENCES ftp.users(userid),
        abs_path           text,
        file               text,
        dns                text,
        time_transaction   text,
        ts_in              timestamp with time zone   NOT NULL DEFAULT CURRENT_TIMESTAMP
);
GRANT INSERT ON ftp.file_log TO proftp;
GRANT UPDATE ON TABLE ftp.file_log_pkid_seq TO proftp;

Connect to your database and run that with:

\i script_name

or, you can use send '--file script_name' to psql as a command line arguement

Those on lesser databases (using this tutorial with mysql), you might want to replace text with varchar; but, because of Postgres design there is no advantage to this.

Setting up proftpd

This part is tricky and varies slightly if your using 1.3 -- once again this tutorial is for 1.2.10.

I've added this portion to my default proftpd boilerplate conf, located at /etc/proftpd.conf:

AuthOrder            mod_sql.c
SQLAuthTypes         Plaintext Empty
SQLAuthenticate      users
SQLConnectInfo       proftpd@localhost proftp dealermadeftp

SQLDefaultUID        110   # CHANGE FOR YOUR FTP USERS UID FOUND IN /etc/passwd
SQLDefaultGID        1001  # CHANGE FOR YOUR FTP USERS GID, FOUND IN /etc/groups
SQLDefaultHomedir    /home/ftp
RequireValidShell    off

SQLUserInfo          ftp.users userid passwd uid gid homedir shell

SQLNegativeCache     off
SQLLogFile           /var/log/proftpd-sql
SQLLog               STOR newfile
SQLNamedQuery        newfile FREEFORM "INSERT INTO ftp.file_log(userid,abs_path,file,dns,time_transaction) VALUES ('%U','%f','%J','%V','%T')"

# %U => userid
# %D => --Nothing,
# %f => abs_path
# %J => file
# %h => dns_remote, %V => dns_local
# %a => remote_ip, %L => local_ip
# %t => localtime
# %T => transfer_time

* The template variables are commented and only for your own reference. A brief rundown of the conf as follows:

  1. Here we set the AuthOrder, to mod_sql.c, in turn enabling mod_sql
  2. I've selected to use Plaintext passwords, or in the event I don't assign one, allow empty passwords. ( I will later implement a trigger to SHA1 the passwords in the database. )
  3. I then set SQLAuthenticate to users, I chose not to use groups for this project, If you need groups more infomration can be found on those tables at:This website for docs on groups
  4. SQLConnectInfo is the dsn in the format of "database@host user password,"
  5. SQLDefaultUID, SQLDefaultGID, SQLDefaultHomedir, configure the default, leaving the majority of my columns to NULL, I will rarely override this.
  6. RequireValidShell is also set to off, if you set it to on, and feed an invalid shell you will fail authentication.
  7. The SQLUserInfo corresponds to the Postgres tables, it is a literal mapping, again the "ftp." specifies the schema for the auth table
  8. SQLNegativeCache, caches auth-failures,
  9. SQLLogFile for verbose logging while configuring your server. I will disable when I go production
  10. SQLLog specifies what you ftp action you would like to log, because my users can only upload, I only log STOR, the format is 'ftpaction query_to_execute'
  11. SQLNamedQuery Check the syntax in the docs on this one for a better explanation the format I use is 'qryname FREEFORM custom_query_here', I do this largely because I wish to capture a lot of proftpd's template variables, there are other more simple syntax

Thats it. If all goes to plan you should be up and working. Now when someone uploads a file, this should be the result:

proftpd=# select * from ftp.file_log;
 pkid |  userid  |        abs_path        |     file      |  dns  | time_transaction |             ts_in 
------+----------+------------------------+---------------+-------+------------------+-------------------------------
    1 | ecarroll | /home/ftp/foo/testfile | /foo/testfile | AMD64 | 0.000            | 2006-06-11 20:49:12.623375-05
(1 row)

Part II: Beyond 'working'

... But *working* is never enough, right? Lets proceed to answer the following question posted in the configuration faq for proftpd:
Can I rotate files out of an upload directory after upload?

And the answer, you sure can. I will accomplish this though a stored procedure, that way files get moved the right way -- on-the-fly -- without a daemon.

You will first need to install *un*trusted plperlu on the system, this can be accomplished with

apt-get install postgresql-plperl-8.1

Then you need to install plperlu on the database

createlang plperlu your_database_here

Next we need to create a quick perl script trigger, that will hit on each insert into our ftp_log table

CREATE OR REPLACE FUNCTION ftp_file() RETURNS TRIGGER AS $$
        use warnings;
        use Cwd;
        use File::Basename;
        use File::Spec;
        use File::Copy;
        move (
                $_TD->{new}{abs_path},
                File::Spec->catfile( '/home/ftp/', basename($_TD->{new}{file}) )
        );
        return;
$$ LANGUAGE 'plperlu' VOLATILE;

CREATE TRIGGER ftp_file
        BEFORE INSERT
        ON ftp.file_log
        FOR EACH ROW
        EXECUTE PROCEDURE ftp_file()
;

This sql script can be installed the same way the prior one was, (the one used to create the tables)

Share this page:

1 Comment(s)