Derek Neely

Pure-FTP is  a powerful ftp server. If you are like me however, you don’t like to create system accounts for each individual user. Well, this is where we are in luck! Pure-FTP has the ability to tie into both PostgreSQL and MySQL.  In this example I have tied it into a PostgreSQL database. I will also include the MySQL snippet of the configuration but it hasn’t been tested by me. Also note that this is a Gentoo based installation but the general configuration should be the same.

Install Pure-FTP with MySQL and/or PostgreSQL support:

# > vi /etc/make.conf
– add ‘postgres’ and/or ‘mysql’ to the USE flag or:

#> USE=”mysql postgres” emerge -av net-ftp/pure-ftpd

Now that Pure-FTP is installed with the various database support, we can configure Pure-FTP to authenticate off of a database. You may need to tailor the SQL queries to match the schema of your database.

PostgreSQL:

#>  vi /etc/pureftpd-pgsql.conf

# If PostgreSQL listens to a TCP socket
PGSQLServer localhost
PGSQLPort 5432
# *or* if PostgreSQL can only be reached through a local Unix socket
# PGSQLServer /tmp
# PGSQLPort .s.PGSQL.5432
# Mandatory : user to bind the server as.
PGSQLUser [pureftpd]
# Mandatory : user password. You *must* have a password.
PGSQLPassword [pureftpd_password]
# Mandatory : database to open.
PGSQLDatabase [pureftpd_database]
# Mandatory : how passwords are stored
# Valid values are : “cleartext”, “crypt”, “md5″ and “any”
#PGSQLCrypt cleartext
PGSQLCrypt crypt

# In the following directives, parts of the strings are replaced at
# run-time before performing queries :
#
# \L is replaced by the login of the user trying to authenticate.
# \I is replaced by the IP address the user connected to.
# \P is replaced by the port number the user connected to.
# \R is replaced by the IP address the user connected from.
# \D is replaced by the remote IP address, as a long decimal number.
#
# Very complex queries can be performed using these substitution strings,
# especially for virtual hosting.
# Query to execute in order to fetch the password
PGSQLGetPW SELECT password FROM ftp_users WHERE ftp_user=’\L’
# Query to execute in order to fetch the system user name or uid
PGSQLGetUID SELECT uid FROM ftp_users WHERE ftp_user=’\L’
# Optional : default UID – if set this overrides PGSQLGetUID
#PGSQLDefaultUID 1000
# Query to execute in order to fetch the system user group or gid
PGSQLGetGID SELECT gid FROM ftp_users WHERE ftp_user=’\L’
# Optional : default GID – if set this overrides PGSQLGetGID
#PGSQLDefaultGID 1000
# Query to execute in order to fetch the home directory
PGSQLGetDir SELECT dir FROM ftp_users WHERE ftp_user=’\L’
#########OPTIONAL SETTINGS#############
# Optional : query to get the maximal number of files
# Pure-FTPd must have been compiled with virtual quotas support.
# PGSQLGetQTAFS SELECT QuotaFiles FROM users WHERE User=’\L’
# Optional : query to get the maximal disk usage (virtual quotas)
# The number should be in Megabytes.
# Pure-FTPd must have been compiled with virtual quotas support.
# PGSQLGetQTASZ SELECT QuotaSize FROM users WHERE User=’\L’
# Optional : ratios. The server has to be compiled with ratio support.
PGSQLGetRatioUL SELECT ul_ratio FROM ftp_users WHERE ftp_user=’\L’
PGSQLGetRatioDL SELECT dl_ratio FROM ftp_users WHERE ftp_user=’\L’
# Optional : bandwidth throttling.
# The server has to be compiled with throttling support.
# Values are in KB/s .
PGSQLGetBandwidthUL SELECT ul_bandwidth FROM ftp_users WHERE ftp_user=’\L’
PGSQLGetBandwidthDL SELECT dl_bandwidth FROM ftp_users WHERE ftp_user=’\L’

Now we need to modify the pure-ftpd config file (keep in mind this is Gentoo)

#> vi /etc/conf.d/pure-ftpd

Look for the line: AUTH=”-l unix” and change to:

AUTH=”-l pgsql:/etc/pureftpd-pgsql.conf

#> /etc/init.d/pure-ftpd restart

This should conclude your intstallation of Pure-FTP with Postgres database support.

MySQL Config File:

Coming Soon!

A couple of little tweaks that I’ve had to use for some of the configurations.

If you want all users to go to the same directory and don’t have or want to store the directory information in the database you can change this line in the pureftpd-pgsql.conf:

PGSQLGetDir SELECT ‘/home/ftpdir’ FROM ftp_users WHERE ftp_user=’\L’