How to install a OpenVPN System Based On User/Password Authentication with mysql & Day Control (libpam-mysql)

This document describes how to install a OpenVPN server with User/Password authentication with mysql and day control using libpam-mysql. This will be a brief, but a very practical document.

  • Install mysql server

[codesyntax lang="bash"]

apt-get install mysql-server

[/codesyntax]

  • Create a mysql user and a database to be used later

[codesyntax lang="bash"]

mysql -u root -p

[/codesyntax]

CREATE DATABASE openvpn;
USE openvpn;

CREATE USER 'openvpn'@'localhost' IDENTIFIED BY 'lNPg5TAIy82zFpEn';
GRANT ALL PRIVILEGES ON `openvpn`.* TO 'openvpn'@'localhost';
FLUSH PRIVILEGES;

CREATE TABLE IF NOT EXISTS `user` (
    `user_id` varchar(32) COLLATE utf8_unicode_ci NOT NULL,
    `user_pass` varchar(32) COLLATE utf8_unicode_ci NOT NULL DEFAULT '1234',
    `user_mail` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
    `user_phone` varchar(16) COLLATE utf8_unicode_ci DEFAULT NULL,
    `user_start_date` date NOT NULL,
    `user_end_date` date NOT NULL,
    `user_online` enum('yes','no') NOT NULL DEFAULT 'no',
    `user_enable` enum('yes','no') NOT NULL DEFAULT 'yes',
PRIMARY KEY (`user_id`),
KEY `user_pass` (`user_pass`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE IF NOT EXISTS `log` (
    `log_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `user_id` varchar(32) COLLATE utf8_unicode_ci NOT NULL,
    `log_trusted_ip` varchar(32) COLLATE utf8_unicode_ci DEFAULT NULL,
    `log_trusted_port` varchar(16) COLLATE utf8_unicode_ci DEFAULT NULL,
    `log_remote_ip` varchar(32) COLLATE utf8_unicode_ci DEFAULT NULL,
    `log_remote_port` varchar(16) COLLATE utf8_unicode_ci DEFAULT NULL,
    `log_start_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `log_end_time` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
    `log_received` float NOT NULL DEFAULT '0',
    `log_send` float NOT NULL DEFAULT '0',
PRIMARY KEY (`log_id`),
KEY `user_id` (`user_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

\q

  • Install OpenVPN

[codesyntax lang="bash"]

apt-get install openvpn

[/codesyntax]

  • Generate keys

[codesyntax lang="bash"]

apt-get install openssl

cp -R /usr/share/doc/openvpn/examples/easy-rsa /etc/openvpn/.
cd /etc/openvpn/easy-rsa/2.0/
sed -i -e 's/--interact //g' build-key

# search and replace the following values in /etc/openvpn/easy-keys/2.0/vars
vim vars

[/codesyntax]

export KEY_SIZE=2048

export KEY_COUNTRY="SE"
export KEY_PROVINCE="SE"
export KEY_CITY="STOCKHOLM"
export KEY_ORG="Company Name"
export KEY_EMAIL="email@example.org"
export KEY_CN=vpn.example.org
export KEY_NAME=operations
export KEY_OU=operations
export PKCS11_MODULE_PATH=changeme
export PKCS11_PIN=1234

[codesyntax lang="bash"]

:wq

sed -i -e 's/unique_subject = yes/unique_subject = no/g' /etc/openvpn/easy-rsa/2.0/keys/index.txt.attr

source ./vars
./clean-all
./build-ca
./build-key-server vpn.example.org
./build-dh
cp -a keys /etc/openvpn/.

[/codesyntax]

  • Install libpam-mysql and setup pam authentication based on it

[codesyntax lang="bash"]

apt-get install libpam-mysql
vim /etc/pam.d/openvpn

[/codesyntax]

auth sufficient pam_mysql.so user=openvpn passwd=lNPg5TAIy82zFpEn host=localhost db=openvpn [table=user] usercolumn=user.user_id passwdcolumn=user.user_pass [where=user.user_enable=1 AND user.user_start_date!=user.user_end_date AND TO_DAYS(now()) >= TO_DAYS(user.user_start_date) AND (TO_DAYS(now()) <= TO_DAYS(user.user_end_date) OR user.user_end_date='0000-00-00')] sqllog=0 crypt=0

account required pam_mysql.so user=openvpn passwd=lNPg5TAIy82zFpEn host=localhost db=openvpn [table=user] usercolumn=user.user_id passwdcolumn=user.user_pass [where=user.user_enable=1 AND user.user_start_date!=user.user_end_date AND TO_DAYS(now()) >= TO_DAYS(user.user_start_date) AND (TO_DAYS(now()) <= TO_DAYS(user.user_end_date) OR user.user_end_date='0000-00-00')] sqllog=0 crypt=0

[codesyntax lang="bash"]

:wq

[/codesyntax]

  • Create scripts to log OpenVPN access activity

[codesyntax lang="bash"]

mkdir /etc/openvpn/scripts/ && cd $_

vim /etc/openvpn/scripts/config.sh

[/codesyntax]

#!/bin/bash
##Dababase Server
HOST='127.0.0.1'
#Default port = 3306
PORT='3306'
#Username
USER='openvpn'
#Password
PASS='lNPg5TAIy82zFpEn'
#database name
DB='openvpn'

[codesyntax lang="bash"]

:wq

[/codesyntax]

[codesyntax lang="bash"]

vim /etc/openvpn/scripts/connect.sh

[/codesyntax]

#!/bin/bash
. /etc/openvpn/scripts/config.sh
##insert data connection to table log
mysql -h$HOST -P$PORT -u$USER -p$PASS $DB -e "INSERT INTO log (log_id,user_id,log_trusted_ip,log_trusted_port,log_remote_ip,log_remote_port,log_start_time,log_end_time,log_received,log_send) VALUES(NULL,'$common_name','$trusted_ip','$trusted_port','$ifconfig_pool_remote_ip','$remote_port_1',now(),'0000-00-00 00:00:00','$bytes_received','$bytes_sent')"
##set status online to user connected
mysql -h$HOST -P$PORT -u$USER -p$PASS $DB -e "UPDATE user SET user_online='yes' WHERE user_id='$common_name'"
[codesyntax lang="bash"]

:wq

[/codesyntax]

[codesyntax lang="bash"]

vim /etc/openvpn/scripts/disconnect.sh

[/codesyntax]

#!/bin/bash
. /etc/openvpn/scripts/config.sh
##set status offline to user disconnected
mysql -h$HOST -P$PORT -u$USER -p$PASS $DB -e "UPDATE user SET user_online='no' WHERE user_id='$common_name'"
##insert data disconnected to table log
mysql -h$HOST -P$PORT -u$USER -p$PASS $DB -e "UPDATE log SET log_end_time=now(),log_received='$bytes_received',log_send='$bytes_sent' WHERE log_trusted_ip='$trusted_ip' AND log_trusted_port='$trusted_port' AND user_id='$common_name' AND log_end_time='0000-00-00 00:00:00'"

[codesyntax lang="bash"]

:wq

[/codesyntax]

[codesyntax lang="bash"]

chmod 755 /etc/openvpn/scripts/*.sh

[/codesyntax]

  • Create the password file for accessing OpenVPN management interface via telnet

[codesyntax lang="bash"]

echo "wYYoFlaQa8nGQoO8" > /etc/openvpn/pw-management-file
chmod 600 /etc/openvpn/pw-management-file

[/codesyntax]

  • Configure OpenVPN

[codesyntax lang="bash"]

vim /etc/openvpn/vpn.example.org.conf

[/codesyntax]

##general settings
port 1194
proto udp
dev tun

##keys
ca /etc/openvpn/keys/ca.crt
cert /etc/openvpn/keys/vpn.example.org.crt
key /etc/openvpn/keys/vpn.example.org.key
dh /etc/openvpn/keys/dh2048.pem

##FIXME: ip for the clients
server 10.0.1.0 255.255.255.0
ifconfig-pool-persist ipp.txt
##FIXME: routes pushed to the client
push "route 172.16.1.0 255.255.255.0"
push "route 10.0.0.0 255.0.0.0"
push "route 195.248.229.19 255.255.255.255"

##Auth
comp-lzo
user nobody
#group nogroup
client-to-client
#client-cert-not-required
username-as-common-name

##user/pass auth from mysql
plugin /usr/lib/openvpn/openvpn-auth-pam.so openvpn

##script connect-disconnect
script-security 3 system
client-connect /etc/openvpn/scripts/connect.sh
client-disconnect /etc/openvpn/scripts/disconnect.sh

##management
management localhost 1194 pw-management-file

keepalive 10 120
persist-key
persist-tun
status status.log
verb 3
[codesyntax lang="bash"]

:wq

[/codesyntax]

  • Start OpenVPN

[codesyntax lang="bash"]

/etc/init.d/openvpn start

[/codesyntax]

Note: I also created a script to manage OpenVPN users. If you would like to have it, please post a comment.
Note2: feel free to use my user management script! I am pretty sure it has a lot of bugs and things to improve!!! Use it on your own risk!

  1. You said you made a script to manage OpenVPN users, could you share that? It would be so greatly appreciated!

  2. The script for manage users would be very nice ;)

    And great tutorial, thanks for that!

  3. Hi guys,

    Thanks for your feedback! Please feel free to use my OpenVPN user management script. If you change it and if you improve it, please, ping me!

    Regards!

  4. hi
    thank you for your work.
    Can i have the admin-script ?

  5. Leslie Segeal

    I wish to transfer my contact list to this PC

  6. Leslie Segeal

    awaiting you help. LES

Scrie si tu o vorbulita


NOTE - You can use these HTML tags and attributes:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

This site uses Akismet to reduce spam. Learn how your comment data is processed.