Tag Archives: pam-mysql

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

Publicat la 2 December 2013 9 comentarii

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!

OpenVPN with pam-mysql username/password authentication

Publicat la 5 April 2012 2 comentarii

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
[codesyntax lang="bash"]

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

[/codesyntax]

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
[codesyntax lang="bash"]

vim /etc/openvpn/server.conf

[/codesyntax]

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

[codesyntax lang="bash"]

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

[/codesyntax]

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"

[codesyntax lang="bash"]

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

[/codesyntax]

4. Allow traffic to be routed from clients to server
[codesyntax lang="bash"]

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

[/codesyntax]

5. Configure PAM. Create a file named openvpn in /etc/pam.d directory
[codesyntax lang="bash"]

vim /etc/pam.d/openvpn

[/codesyntax]

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.

[codesyntax lang="bash"]

mysql -u root -pmysqlpasswd

[/codesyntax]

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