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’

After some reading of consolidation options for Apache logs, I ran across mod_log_sql (we are hating spread) which will take Apache logs and log them off to a MySQL database. Sounded great! We could then run scripts to go through and parse the values and run statistics on. Twas perfect for our needs. RIGHT!!!

At my company we run about 12 vhosts over about 5-6 load balanced web servers. All of them were configured to log to our loganalysis server which is a pretty beefy machine. The logging all in all worked well with a few major exceptions listed below.

1) I used the directive:

LogSQLRequestIgnore .gif .jpg .css .ico .png .js

This directive is supposed to be used to ignore any pages ending with that extension. This did not work at all and I had to create a script to actually delete those before analyzing the logs. Bummer but not that big of a deal.

2) While working with the server to optimize the database, there were various times when I would need to restart the MySQL service and a few times I needed to reboot the server. During these periods of time, the web servers were unable to log to the database which brought them to their knees. The inability of the module to handle a database outage gracefully was a major deal breaker for us. I feel this issue is a result of intense disk IO when the database is down. The server is logging to its Apache logs, the the backup SQL logs, and to the Apache error logs for every failed request. This becomes emense with thousands of requests per second. Should this server die or needing maintenance would have ultimately brought our company to a hault.

Long story short, we’re scrapping mod_log_sql and going with an NFS mount out to all the web services which we can then parse and run statistics on using some custom scripts and/or AWStats or Splunk.

I know I’m probably in the past on this, but I recently discovered MySQL triggers and the great power and convenience behind them. Below, I’m trying to capture some basic examples of how to use them.

Triggers are actions that are performed when another action occurs. Specifically on any INSERT, UPDATE, or DELETE. One brief example would be, when you delete a row/record from one table, you can automatically delete any reference to that record in other tables.

First create two (2) tables:

CREATE TABLE a (a_id int(11) NOT NULL auto_increment, a_value varchar(100) NOT NULL, PRIMARY KEY (a_id))

CREATE TABLE b (b_id int(11) NOT NULL auto_increment, b_value varchar(100) NOT NULL, a_id int(11) NOT NULL, PRIMARY KEY (b_id))

We will use/assume the relationship of 1-to-many (a-to-b). In the b-table we have a column ‘a_id’ which will be a logical foreign key back to the a-table. (I know you can specify foreign keys on the tables but for this tutorial/example that isn’t exactly necessary.)

Now, on to the triggers…

There are 6 trigger events associated as BEFORE or AFTER of INSERT, UPDATE, or DELETE:

BEFORE INSERT
AFTER INSERT
BEFORE UPDATE
AFTER UPDATE
BEFORE DELETE
AFTER DELETE

The general syntax for creating a trigger is:

delimiter //
CREATE TRIGGER
[trigger_name]
[BEFORE/AFTER] [INSERT/UPDATE/DELETE] ON
[table_action_perfomed_on]
FOR EACH ROW BEGIN
[sql_statement_of_what_to_do];
END;
//

DELETES: This trigger will run upon a row being deleted.

delimiter //
CREATE TRIGGER
a_table_on_delete
BEFORE DELETE ON
a
FOR EACH ROW BEGIN
DELETE FROM b WHERE a_id=OLD.a_id;
END;
//

Once you have created your trigger, you can view your triggers using SHOW TRIGGERS:

mysql> show triggers\G;
*************************** 1. row***************************
Trigger: a_table_on_delete
Event: DELETE
Table: a
Statement: BEGIN
DELETE FROM b WHERE a_id=OLD.a_id;
END
Timing: BEFORE
Created: NULL
sql_mode:
Definer: root@localhost

Also, be sure to reset your delimter for standard SQL statements.

mysql> delimiter ;