OpenVPN with pam-mysql username/password authentication

This document describes how to install openvpn with pam-mysql username/password authentication. This procedure was carried out on Debian/Ubuntu Linux, but with minor changes can be used on other Linux distributions too.

1. Install openvpn, mysql and pam-mysql

sudo su -
mkdir -p /root/work/openvpn
cd /etc/openvpn
apt-get install libpam-mysql openvpn mysql-server

Note: please keep mysql-server password in mind because we are going to use it later (I will refer to this password as 'mysqlpasswd').

2. Create a new openvpn configuration

vim /etc/openvpn/server.conf

port 1194
proto udp
dev tun

ca /etc/openvpn/easy-rsa/keys/ca.crt
cert /etc/openvpn/easy-rsa/keys/server.crt
key /etc/openvpn/easy-rsa/keys/server.key
dh /etc/openvpn/easy-rsa/keys/dh1024.pem

server 10.128.127.0 255.255.255.0
ifconfig-pool-persist ipp.txt

keepalive 10 120

comp-lzo

max-clients 50

persist-key
persist-tun

status openvpn-status.log
log-append /var/log/openvpn.log
verb 3
mute 20

client-cert-not-required
username-as-common-name

plugin /usr/lib/openvpn/openvpn-auth-pam.so openvpn

3. Follow the commands below to setup OpenVPN server

cp -r /usr/share/doc/openvpn/examples/easy-rsa/2.0/ easy-rsa
cd easy-rsa
vim vars # Edit KEY_* vars appropriately

export KEY_COUNTRY="RO"
export KEY_PROVINCE="B"
export KEY_CITY="City"
export KEY_ORG="VPN"
export KEY_EMAIL="user@test.org"
export KEY_COMMONNAME="VPN"

source ./vars
./clean-all
./build-dh
./pkitool --initca
./pkitool --server server

4. Allow traffic to be routed from clients to server

echo 1 > /proc/sys/net/ipv4/ip_forward
sed -i -e 's/#net.ipv4.ip_forward/net.ipv4.ip_forward/g' /etc/sysctl.conf
sysctl -p
iptables -t nat -A POSTROUTING -s 10.128.127.0/24 -o eth0 -j MASQUERADE
iptables-save > /root/work/openvpn/iptables_rules

5. Configure PAM. Create a file named openvpn in /etc/pam.d directory

vim /etc/pam.d/openvpn

auth optional /lib/security/pam_mysql.so user=root passwd=mysqlpasswd host=localhost db=vpn_db table=tbl_user usercolumn=username passwdcolumn=password where=active=1 sqllog=no crypt=1 verbose=0
account required /lib/security/pam_mysql.so user=root passwd=mysqlpasswd host=localhost db=vpn_db table=tbl_user usercolumn=userid passwdcolumn=password where=active=1 sqllog=no crypt=1 verbose=0

6. Create the mysql database, the table where we will keep users and insert some test user.

mysql -u root -pmysqlpasswd

create database vpn_db;
use vpn_db;
CREATE TABLE `tbl_user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(25) DEFAULT NULL,
  `password` varchar(25) DEFAULT NULL,
  `active` int(11) DEFAULT NULL,
  `sqllog` enum('yes','no') DEFAULT NULL,
  `crypt` int(11) DEFAULT NULL,
  `verbose` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO tbl_user
SET username = "user",
    PASSWORD = Encrypt("test"),
    active = "1"; 
\q

7. Client side operations
7.1. Download and install the latest stable version of openvpn-gui from http://openvpn.se/download.html
7.2. Create a configuration file named server.conf in C:\Program Files\OpenVPN\config (for Windows 32bit version) or C:\Program Files (x86)\OpenVPN\config (for Windows 64bit version). The content of the file should look like:
client
dev tun
proto udp
remote VPN-SERVER 1194
resolv-retry infinite
nobind
persist-key
persist-tun
ca ca.crt
auth-user-pass
cipher BF-CBC
comp-lzo
verb 4
mute 20

Note:

  • VPN-SERVER must be the IP or hostname of  the server we just configured
  • ca.crt was generated on step 3. This file must be copied from the server and given to the client

2 thoughts on “OpenVPN with pam-mysql username/password authentication”

  1. in “account required” configuration line it should be “username”

    old:
    account required /lib/security/pam_mysql.so user=root passwd=mysqlpasswd host=localhost db=vpn_db table=tbl_user usercolumn=userid passwdcolumn=password where=active=1 sqllog=no crypt=1 verbose=0

    new:
    account required /lib/security/pam_mysql.so user=root passwd=mysqlpasswd host=localhost db=vpn_db table=tbl_user usercolumn=username passwdcolumn=password where=active=1 sqllog=no crypt=1 verbose=0

Leave a Reply

Your email address will not be published. Required fields are marked *