install proftpd with account in sql

컴파일 옵션

./configure

--prefix=/usr/local/proftpd

--with-shared=mod_sql:mod_sql_mysql

보통 설치시엔 기타 옵션이 더 있는데.. 일단 sql기반의 유저를 사용하기 위해서는 위 옵션이면 된다.

설정 파일

시스템 기반의 계정을 사용 하는 경우에는 필요가 없음

<IfModule mod_dso.c>
LoadModule mod_quotatab.c
LoadModule mod_quotatab_sql.c
LoadModule mod_sql.c
LoadModule mod_sql_mysql.c
</IfModule>

################################################################################
# Auth
################################################################################
# Use only AuthUserFiles when authenticating, and not the system's /etc/passwd
AuthOrder mod_sql.c mod_auth_file.c mod_auth_pam.c mod_auth_unix.c
MaxLoginAttempts 2
# Block based on /etc/ftpusers
UseFtpUsers on
AuthPAM on
# Use pam to authenticate (default) and be authoritative.
# Only need the LoadModule if mod_auth_pam is a DSO.
AuthPAMConfig proftpd

DebugLevel 0
TransferLog /usr/local/proftpd/logs/transfer.log
ExtendedLog /usr/local/proftpd/logs/proftpd.log ALL

<IfModule mod_sql.c>
 SQLBackend mysql
 SQLAuthenticate users groups
 SQLAuthTypes OpenSSL Crypt Plaintext

SQLLogFile /usr/local/proftpd/logs/sql.log
 #SQLLog DELE,MKD,RETR,RMD,RNFR,RNTO,STOR,APPE extendedlog

# used to connect to the database
 # databasename@host database_user user_password
 SQLConnectInfo ftp_db@localhost ftp_db password_of_user

# set min UID and GID - otherwise these are 999 each
 SQLDefaultUID 99 
 SQLDefaultGID 99

# create a user's home directory on demand if it doesn't exist
 CreateHome on 755 dirmode 711

# Here we tell ProFTPd the names of the database columns in the "usertable"
# Syntax: SQLUserInfo user-table user-name passwd uid gid home-dir shell
 SQLUserInfo t_users userid passwd uid gid homedir shell
 SQLUserWhereClause "allow = 1"

# Here we tell ProFTPd the names of the database columns in the "grouptable"
# Syntax: SQLGroupInfo group-table group-name gid members
 SQLGroupInfo t_group groupid gid member
 SQLGroupWhereClause "allow = 1"

QuotaEngine on
 QuotaDirectoryTally on
 QuotaDisplayUnits Mb
 QuotaShowQuotas on
 QuotaLimitTable sql:/get-quota-limit
 QuotaTallyTable sql:/get-quota-tally/update-quota-tally/insert-quota-tally

SQLNamedQuery get-quota-limit SELECT "name, quota_type, per_session, limit_type, bytes_in_avail, bytes_out_avail, bytes_xfer_avail, files_in_avail, files_out_avail, files_xfer_avail FROM quotalimits WHERE name = '%{0}' AND quota_type = '%{1}'"
 SQLNamedQuery get-quota-tally SELECT "name, quota_type, bytes_in_used, bytes_out_used, bytes_xfer_used, files_in_used, files_out_used, files_xfer_used FROM quotatallies WHERE name = '%{0}' AND quota_type = '%{1}'"
 SQLNamedQuery update-quota-tally UPDATE "bytes_in_used = bytes_in_used + %{0}, bytes_out_used = bytes_out_used + %{1}, bytes_xfer_used = bytes_xfer_used + %{2}, files_in_used = files_in_used + %{3}, files_out_used = files_out_used + %{4}, files_xfer_used = files_xfer_used + %{5} WHERE name = '%{6}' AND quota_type = '%{7}'" quotatallies
 SQLNamedQuery insert-quota-tally INSERT "%{0}, %{1}, %{2}, %{3}, %{4}, %{5}, %{6}, %{7}" quotatallies

 # 업로드, 삭제, 이름변경등의 작업시 purge 시키기 위한 디비에 해당 파일의 경로정보를 insert
 SQLLog STOR,DELE,RNTO chlog
 SQLNamedQuery chlog FREEFORM "INSERT INTO t_files (`ip`, `id`, `cmd`, `rnfr`, `path`) VALUES ('%a', '%u', '%m', '%w', '%f')"
</IfModule>

테이블 생성

SQLUserInfo

CREATE TABLE `t_users` (
 `no` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `idx` decimal(18,8) NOT NULL,
 `userid` varchar(32) NOT NULL,
 `passwd` varchar(255) NOT NULL,
 `uid` smallint(5) unsigned NOT NULL DEFAULT '99',
 `gid` smallint(5) unsigned NOT NULL DEFAULT '99',
 `homedir` varchar(255) NOT NULL,
 `shell` varchar(16) NOT NULL DEFAULT '/sbin/nologin',
 `ctime` int(10) unsigned NOT NULL DEFAULT '0',
 `atime` int(10) unsigned NOT NULL DEFAULT '0',
 `mtime` int(10) unsigned NOT NULL DEFAULT '0',
 `allow` tinyint(3) unsigned NOT NULL DEFAULT '1',
 PRIMARY KEY (`no`),
 UNIQUE KEY `idx` (`idx`),
 UNIQUE KEY `userid` (`userid`)
);

SQLGroupInfo

CREATE TABLE `t_group` (
 `no` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `idx` decimal(18,8) NOT NULL,
 `groupid` varchar(32) NOT NULL,
 `gid` smallint(5) unsigned NOT NULL DEFAULT '99',
 `member` varchar(32) NOT NULL,
 `allow` tinyint(3) unsigned NOT NULL DEFAULT '1',
 PRIMARY KEY (`no`),
 UNIQUE KEY `idx` (`idx`),
 UNIQUE KEY `groupid` (`groupid`),
 KEY `gid` (`gid`)
);

SQLNamedQuery

CREATE TABLE `t_files` (
 `no` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `ip` varchar(15) NOT NULL,
 `id` varchar(32) NOT NULL,
 `cmd` varchar(10) NOT NULL,
 `rnfr` varchar(255) NOT NULL,
 `path` varchar(255) NOT NULL,
 `ctime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
 PRIMARY KEY (`no`)
);