Virtual Hosting With vsftpd And PostgreSQL

This document describes how to install a vsftpd server that uses virtual users from a PostgreSQL database instead of real system users. I couldn't find any tutorial like that on the internet, so when that configuration finally worked for me, I decided to publish it. The documentation is based on FreeBSD 6.2 which I was recently forced to use (I usually use Debian). Nevertheless the document should be suitable for almost any Linux distribution as well (may require very small amendments).

OK, let's start.

Required Packages

• vsftpd
• PostgreSQL Server/Client 7.4+
• PAM_PGSQL

Installation

1. PostgreSQL

#cd /usr/ports/database/postgresql-server82
make install clean

Set your postgres locale  before initdb:

#vi /usr/local/pgsql/.cshrc: setenv PGLIB /usr/local/lib
# note: PGDATA can be overridden by the -D startup option
setenv PGDATA $HOME/data

#You might want to set some locale stuff here
setenv PGDATESTYLE ISO
setenv LC_ALL pl_PL.ISO_8859-2

Install db cluster with right encoding (taken from LC_ALL):

su - pgsql
initdb -E latin2

2. PAM-PGSQL

cd /usr/ports/security/pam-pgsql
make install clean

Make sure that pam-pgsql.so is in /usr/lib. If you get SEGFAULT most likely pam_pgsql is not in the right path (I encountered that problem when I tried pam_pwdfile).

ls /usr/lib/pam_pgsql.so

/usr/lib/pam_pgsql.so

3. vsftpd

cd /usr/ports/ftp/vsftpd
make install clean
echo "vsftpd_enable=\"YES\"" >> /etc/rc.conf
adduser vsftpd

4. And Now ... Working Examples Of Configuration Files

vsftpd - is the name of the pam config file /etc/pam.d/vsftpd.

vsftpd - is the name of recently added user (the user needs write access rights to localroot from the example local_umask=0000 - I wanted files to be stored with chmod 77x).

#vi /usr/local/etc/vsftpd.conf:
listen=YES
anonymous_enable=NO
local_enable=YES
virtual_use_local_privs=YES
write_enable=YES
connect_from_port_20=YES
secure_chroot_dir=/usr/local/share/vsftpd/empty
pam_service_name=vsftpd
guest_enable=YES
user_sub_token=$USER
local_root=/usr/local/www/apache22/data/$USER
chroot_local_user=YES
hide_ids=YES
ftpd_banner=Welcome to FTP server
file_open_mode=0770
local_umask=0000
anon_mkdir_write_enable=NO
guest_username=vsftpd

By default pam_pgsql is looking for the configuration in the file /etc/pam_pgsql.conf. I might think of a setup where I authenticate different services vs. different tables in the postgres database.

The config_file switch does the job - this is how the file looks:

#vi /etc/pam.d/vsftpd:
auth required pam_pgsql.so config_file=/etc/pam_pgsql_vsftpd.conf
account required pam_pgsql.so config_file=/etc/pam_pgsql_vsftpd.conf

#vi /usr/local/pgsql/data/pg_hba.conf:
host system system 127.0.0.1 255.255.255.255 md5 local all pgsql ident sameuser

# # All other connections by UNIX sockets
local all all ident sameuser
# # All IPv4 connections from localhost
# host all all 127.0.0.1 255.255.255.255 md5 host all tronix 0.0.0.0
0.0.0.0 md5 host sameuser all 0.0.0.0 0.0.0.0 md5 host all all 0.0.0.0 0.0.0.0 reject

#vi/etc/pam_pgsql_vsftpd.conf:
debug
pw_type = md5
connect = hostaddr=127.0.0.1 port=5432 dbname=system user=system password=pass connect_timeout=15
auth_query = select s_password from accounts where s_username = %u
acct_query = select b_expired as acc_expired, 0 as acc_new_pwreq, (s_password ISNULL OR s_password = '') as user_password from accounts where s_username = %u
 

#su - pgsql
#psql system

Welcome to psql 8.2.4, the PostgreSQL interactive terminal.

Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit

system=# CREATE TABLE "accounts"(
"i_id" SERIAL,
"s_username" VARCHAR(30) NOT NULL,
"s_password" VARCHAR(50),
"b_expired" BOOLEAN DEFAULT false,
CONSTRAINT "accounts_s_username_key" UNIQUE("s_username")
);

NOTICE: CREATE TABLE will create implicit sequence "accounts_i_id_seq" for serial column "accounts.i_id"
NOTICE: CREATE TABLE / UNIQUE will create implicit index "accounts_s_username_key" for table "accounts"
CREATE TABLE

system=# insert into accounts(s_username, s_password) values('testuser',md5('secret_pass'));

INSERT 0 1

system=# select * from accounts;

i_id | s_username | s_password | b_expired
------+------------+----------------------------------+-----------
1 | testuser | baeed6d497bd4f4676306544a0e7faba | f
(1 row)

Let's create a folder for our new virtual user:

mkdir /usr/local/www/apache22/data/testuser
chown vsftpd:vsftpd /usr/local/www/apache22/data/testuser
ls -la /usr/local/www/apache22/data/testuser

Let's start vsftpd on system startup:

#vi /usr/local/etc/rc.d/vsftpd:

#!/bin/sh
/usr/local/libexec/vsftpd &

Let's start vsftpd right now:

#/usr/local/etc/rc.d/vsftpd

... and vsftpd should work fine now.

I am aware that some things might be done in a different way, but this document should be good enough to guide you.