Author Topic: Proftpd on CentOS 7 - Chrooted SFTP Server with Postgresql Backend Howto  (Read 1050 times)

Offline Bananaman

  • New user
  • *
  • Posts: 8
    • View Profile
Let's assume that you have CentOS 7 and Postgresql set up as you like them; I disabled SELinux. This Howto is going to outline how I, with a liberal dose of help from castaglia, got my database-backended, users-chrooted, SFTP-only server going. Since I was unable to find a Howto for this configuration I thought I'd post this while it was still fresh in my mind.

Fair warning - I am only moderately geeky, so I am not going to vouch for the security of this configuration. I'm only allowing certain IP addresses to access this machine. Feel free to correct any egregious mistakes I have made.

ProFTP install

Fortunately you can just yum all the packages you need:
yum -y install proftpd proftpd-utils proftpd-postgresql

Add proftpd user:
useradd proftpd

mv /etc/proftpd.conf /etc/proftpd.conf.orig - back up the original proftpd.conf for reference. I have stripped out a lot of options in the working example below, so you should look over the original for options you might find useful.

vi /etc/proftpd.conf and paste in the following configuration:

Code: [Select]
# This is the ProFTPD configuration file

ServerName                      ""
ServerIdent                     on "FTP Server ready."
ServerAdmin                     root@localhost
DefaultServer                   on

# Added to disable FTP
Port 0

AuthPAMConfig                   proftpd
AuthOrder                       mod_sql.c mod_auth_pam.c* mod_auth_unix.c
  <IfModule mod_auth_pam.c>
    AuthPAM off
UseReverseDNS                   off

# Set the user and group that the server runs as
User                            proftpd
Group                           proftpd

MaxInstances                    20
UseSendfile                     off
LogFormat                       default "%h %l %u %t \"%r\" %s %b"
LogFormat                       auth    "%v [%P] %h %t \"%r\" %s"

# Dynamic Shared Object (DSO) loading
# General database support (
LoadModule mod_sql.c
# Support for base-64 or hex encoded MD5 and SHA1 passwords from SQL tables
LoadModule mod_sql_passwd.c

# Added to enable reading passwords from database
  <IfModule mod_sql_passwd.c>
    SQLPasswordEngine on
    SQLPasswordEncoding hex

# Postgresql support (requires proftpd-postgresql package)
LoadModule mod_sql_postgres.c
# Administrative control actions for the ftpdctl program
LoadModule mod_ctrls_admin.c
# Support for the SSH2, SFTP, and SCP protocols, for secure file transfer over
# an SSH2 connection (
LoadModule mod_sftp.c
# Use SQL (via mod_sql) for looking up authorized SSH2 public keys for user
# and host based authentication
LoadModule mod_sftp_sql.c
# Implement a virtual chroot capability that does not require root privileges
LoadModule mod_vroot.c
# Allow only user root to load and unload modules, but allow everyone
# to see which modules have been loaded
ModuleControlsACLs              insmod,rmmod allow user root
ModuleControlsACLs              lsmod allow user *
# Enable basic controls via ftpdctl
ControlsEngine                  on
ControlsACLs                    all allow user root
ControlsSocketACL               allow user *
ControlsLog                     /var/log/proftpd/controls.log
# Enable admin controls via ftpdctl
<IfModule mod_ctrls_admin.c>
  AdminControlsEngine           on
  AdminControlsACLs             all allow user root
# Enable mod_vroot by default for better compatibility with PAM
<IfModule mod_vroot.c>
  VRootEngine                   on
# Dynamic ban lists (
# Enable this with PROFTPD_OPTIONS=-DDYNAMIC_BAN_LISTS in /etc/sysconfig/proftpd
  LoadModule                    mod_ban.c
  BanEngine                     on
  BanLog                        /var/log/proftpd/ban.log
  BanTable                      /var/run/proftpd/
  # If the same client reaches the MaxLoginAttempts limit 2 times
  # within 10 minutes, automatically add a ban for that client that
  # will expire after one hour.
  BanOnEvent                    MaxLoginAttempts 2/00:10:00 01:00:00
  # Inform the user that it's not worth persisting
  BanMessage                    "Host %a has been banned"
  # Allow the FTP admin to manually add/remove bans
  BanControlsACLs               all allow user ftpadm
# Set networking-specific "Quality of Service" (QoS) bits on the packets used
# by the server (contrib/mod_qos.html)
<IfDefine QOS>
  LoadModule                    mod_qos.c
  # RFC791 TOS parameter compatibility
  QoSOptions                    dataqos throughput ctrlqos lowdelay
  # For a DSCP environment (may require tweaking)
  #QoSOptions                   dataqos CS2 ctrlqos AF41

# Added to set SFTP on port 222 so we can still SSH to 22
<IfModule mod_sftp.c>
        SFTPEngine on
        SFTPLog /var/log/proftpd/sftp.log
        Port 222
        SFTPHostKey /etc/ssh/ssh_host_rsa_key
        SFTPCompression delayed
        MaxLoginAttempts 6

# Global Config - config common to Server Config and all virtual hosts
  # Cause every FTP user except adm to be chrooted into their home directory
  DefaultRoot                     ~ !adm
  # Umask 022 is a good standard umask to prevent new dirs and files
  # from being group and world writable
  Umask                         022
  # Allow users to overwrite files and change permissions
  AllowOverwrite                yes

  # We put our mod_sql directives in a <Global> block so they'll be
  # inherited by the <Anonymous> block below, and any other <VirtualHost>
  # blocks we may want to add.  For a simple server these don't need to
  # be in a <Global> block but it won't hurt anything.

  # Added SQL logging
  SQLLogFile /var/log/proftpd/sql.log

  # Specify our connection information.
  SQLConnectInfo ftpdb@localhost:5432 proftpd

  # Specify our authentication schemes. 
  SQLAuthTypes OpenSSL

  # Specify the table and fields for user information.
  SQLUserInfo users username password uid gid homedir NULL

  # Added default IDs
  SQLDefaultUID        1003
  SQLDefaultGID        1003

  # This is not a mod_sql specific directive, but it's here because of
  # the way we specified 'SQLUserInfo', above.  By setting this to
  # 'off', we're telling ProFTPD to allow users to connect even if we
  # have no (or bad) shell information for them.  Since we specified the
  # shell field as 'NULL', above, we need to tell ProFTPD to allow the
  # users in even though their shell doesn't exist.
  RequireValidShell off

  # Here we tell mod_sql how to get out group information.
  SQLGroupInfo groups groupname gid members

# close our <Global> block.


This config adds logging in /var/log/proftpd/sql.log and /var/log/proftpd/sftp.log; in addition if you're troubleshooting you might want to check /var/log messages and /var/log/secure.

Some commands you might find useful:
systemctl start proftpd
systemctl status proftpd
systemctl enable proftpd
systemctl restart proftpd

To check the loaded modules, use ftpdctl lsmod.

User directories

Next we can create a directory where all the users' data will live:
mkdir /path/you/want/
Then add a new user directory underneath and set the appropriate permissions. In my case I also want another directory below this with special permissions. You may not need such a complex permissions structure; I need another application to interact with the user files.
mkdir /path/you/want/bananas
chmod 755 /path/you/want/bananas
mkdir /path/you/want/bananas/appaccess
chown <userid>:<userid> /path/you/want/bananas/appaccess

(Note - the userid here will be the uid & gid you add to the database.)
chmod 2770 /path/you/want/bananas/appaccess
(Note - the 2 in that chmod sets the setguid bit so that the group-owner is the owner of the directory, not of the file creator.)

PostgreSQL Setup

Lastly you need to set up a database and tables for your user information:
sudo su - postgres
create database ftpdb;
psql susdb

Code: [Select]
   CREATE TABLE users (
    username VARCHAR(30) NOT NULL UNIQUE,
    password VARCHAR(80) NOT NULL,
    gid INTEGER,
    homedir VARCHAR(255),
    shell VARCHAR(255),
  CREATE INDEX users_userid_idx ON users (userid);
  CREATE TABLE groups (
    groupname VARCHAR(30) NOT NULL,
    members VARCHAR(255)

  CREATE INDEX groups_gid_idx ON groups (gid);

Add proftpd user to PostgreSQL:
createuser -–interactive proftpd

Password generation

You can create a hash for the password temp1234 like so:
for c in `openssl list-message-digest-commands`; do /bin/echo ”{$c}“`/bin/echo -n “temp1234” | openssl dgst -binary -$c | openssl enc -base64`; done

 This will return something like the following:

You're going to use the one which starts with {md5}. For the sql command below, you'll use this, the path to the user's directory, and any uid and gid you'd like.

Adding the user to the database
Open the database - psql susdb
Create the user:
insert into users (username, password, uid, gid, homedir) values ('bananas','{md5}uJV3LROYqfUCLdZxgojGdw==',1111,1111,'/path/you/want/bananas');
Or if you just need to update:
update users set password = '{md5}uJV3LROYqfUCLdZxgojGdw==' where username = 'bananas';

Restart Proftpd and try to login!