# This is a basic ProFTPD configuration file (rename it to 
# 'proftpd.conf' for actual use.  It establishes a single server
# and a single anonymous login.  It assumes that you have a user/group
# "nobody" and "ftp" for normal operation and anon.

ServerName      "FTP server"
ServerType      standalone
DefaultServer   on

# Port 21 is the standard FTP port.
Port            21

# Don't use IPv6 support by default.
UseIPv6         off

# Umask 022 is a good standard umask to prevent new dirs and files
# from being group and world writable.
Umask           022

# To prevent DoS attacks, set the maximum number of child processes
# to 30.  If you need to allow more than 30 concurrent connections
# at once, simply increase this value.  Note that this ONLY works
# in standalone mode, in inetd mode you should use an inetd server
# that allows you to limit maximum number of processes per service
# (such as xinetd).
MaxInstances    30

# Set the user and group under which the server will run.
User            nobody
Group           nogroup

# To cause every FTP user to be "jailed" (chrooted) into their home
# directory, uncomment this line.
DefaultRoot       ~
RequireValidShell off
WtmpLog off
PassivePorts 60000 60100

# Normally, we want files to be overwriteable.
AllowOverwrite  on

# Bar use of SITE CHMOD by default
<Limit SITE_CHMOD>
  DenyAll
</Limit>

# A basic anonymous configuration, no upload directories.  If you do not
# want anonymous users, simply delete this entire <Anonymous> section.
<Anonymous ~ftp>
  User          ftp
  Group         ftp

  # We want clients to be able to login with "anonymous" as well as "ftp"
  UserAlias     anonymous ftp

  # Limit the maximum number of anonymous logins
  MaxClients      10

  # We want 'welcome.msg' displayed at login, and '.message' displayed
  # in each newly chdired directory.
  DisplayLogin      welcome.msg
  DisplayChdir      .message

  # Limit WRITE everywhere in the anonymous chroot
  <Limit WRITE>
    DenyAll
  </Limit>
</Anonymous>

<IfModule mod_sql.c>
  SQLBackend mysql

  AuthOrder mod_sql.c
  SQLAuthTypes Crypt
  SQLAuthenticate users* groups*

  # We need our "default" connection to the userdb database
  SQLConnectInfo proftpd@mysql:3306 root password

  # Now that we have a default connection, we can create another connection, named "logdb" and using the "mysql" backend, to the logdb database
  SQLNamedConnectInfo logdb mysql proftpd@mysql:3306 root password

  # Point mod_sql at our users/groups tables
  SQLUserInfo users userid passwd uid gid homedir shell
  SQLUserWhereClause "login_allowed = 'true'"

  SQLGroupInfo groups groupname gid members

  # Update number of connection
  SQLLog PASS updatecount
  SQLNamedQuery updatecount UPDATE "count=count+1, last_accessed=now() WHERE userid='%u'" users

  # Update the users.last_accessed column on successful login in the userdb
  SQLNamedQuery last_accessed UPDATE "last_accessed = NOW() WHERE userid='%u'" users
  SQLLog PASS last_accessed

  # Add a row to the login_history table on successful login in the logdb
  SQLNamedQuery log_sess FREEFORM "INSERT INTO login_history (user, client_ip, server_ip, protocol, access_date) VALUES ('%u', '%a', '%V', '%{protocol}', NOW())" logdb

  SQLLog PASS log_sess IGNORE_ERRORS
</IfModule>

<IfModule mod_quotatab.c>
  QuotaEngine on
  QuotaDirectoryTally on
  QuotaDisplayUnits Mb
  QuotaShowQuotas on


  SQLNamedQuery get-quota-limit SELECT "name, quota_type, by_session, limit_type, bytes_up_limit, bytes_down_limit, bytes_transfer_limit, files_up_limit, files_down_limit, files_transfer_limit FROM quotalimits WHERE name = '%{0}' AND quota_type = '%{1}'"
  SQLNamedQuery get-quota-tally SELECT "name, quota_type, bytes_up_total, bytes_down_total, bytes_transfer_total, files_up_total, files_down_total, files_transfer_total FROM quotatotal WHERE name = '%{0}' AND quota_type = '%{1}'"
  SQLNamedQuery update-quota-tally UPDATE "bytes_up_total = bytes_up_total + %{0}, bytes_down_total = bytes_down_total + %{1}, bytes_transfer_total = bytes_transfer_total + %{2}, files_up_total = files_up_total + %{3}, files_down_total = files_down_total + %{4}, files_transfer_total = files_transfer_total + %{5} WHERE name = '%{6}' AND quota_type = '%{7}'" quotatotal
  SQLNamedQuery insert-quota-tally INSERT "%{0}, %{1}, %{2}, %{3}, %{4}, %{5}, %{6}, %{7}" quotatotal

  QuotaLimitTable sql:/get-quota-limit
  QuotaTallyTable sql:/get-quota-tally/update-quota-tally/insert-quota-tally

</IfModule>

<IfModule mod_sftp.c>

        SFTPEngine on
        Port 22
        SFTPLog /var/log/proftpd/sftp.log

        # Configure both the RSA and DSA host keys, using the same host key
        # files that OpenSSH uses.
        SFTPHostKey /etc/ssh/ssh_host_rsa_key
        SFTPHostKey /etc/ssh/ssh_host_dsa_key

        SFTPAuthMethods publickey password

        SFTPAuthorizedUserKeys file:/etc/proftpd/authorized_keys/%u

        # Enable compression
        SFTPCompression delayed
</IfModule>