Virtual Hosting With vsftpd And PostgreSQL

Want to support HowtoForge? Become a subscriber!
Submitted by norbi771 (Contact Author) (Forums) on Mon, 2007-05-07 18:00. :: FTP | FreeBSD

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+



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 LC_ALL pl_PL.ISO_8859-2

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

su - pgsql
initdb -E latin2



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

Make sure that 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/


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:
ftpd_banner=Welcome to FTP server

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 config_file=/etc/pam_pgsql_vsftpd.conf
account required config_file=/etc/pam_pgsql_vsftpd.conf

#vi /usr/local/pgsql/data/pg_hba.conf:
host system system 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 md5 host all tronix md5 host sameuser all md5 host all all reject

pw_type = md5
connect = hostaddr= 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

Let's create the postgres table and a test user:

#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"

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


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:

/usr/local/libexec/vsftpd &

Let's start vsftpd right now:


... 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.

Please do not use the comment function to ask for help! If you need help, please use our forum.
Comments will be published after administrator approval.
Submitted by Baptiste SIMON (not registered) on Wed, 2010-07-28 23:51.

I applied this tutorial for managing FTP accounts through a web interface developped with the Symfony framework... coupled to a MTA service. it works great. thanks for that !

I just added "virtual_use_local_privs=YES" in /etc/vsftpd.conf to be able to upload things without permitting the same for anonymous clients.

Have fun,