Friday, June 15, 2012
Unknown
OpenVPN System Based On User/Password Authentication with mysql &
Day Control (lib-pam mysql) - Debian System detail: I. Install MySQL Server for User/Pass Authentication
Day Control (lib-pam mysql) - Debian System detail: I. Install MySQL Server for User/Pass Authentication
1. Install MySQL Server
II. Install OpenVPN Server and generation of certificate
apt-get install mysql-server
2. Log in MySQL as root
mysql -uroot -p
3. Create the database 'openvpn'
CREATE DATABASE openvpn;
4. Create a MySQL user with username 'USERNAME' and password 'PASSWORD'
GRANT ALL ON openvpn.* TO 'USERNAME'@"%" IDENTIFIED BY 'PASSWORD';
5. Log out root user
exit;
6. Log in MySQL as new user 'USERNAME'
mysql -uUSERNAME -pPASSWORD
7. Switch database
USE openvpn;
8. Create user, log table and insert user data
- user table
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_online` tinyint(1) NOT NULL DEFAULT '0',
`user_enable` tinyint(1) NOT NULL DEFAULT '1',
`user_start_date` date NOT NULL,
`user_end_date` date NOT NULL,
PRIMARY KEY (`user_id`),
KEY `user_pass` (`user_pass`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
- log table
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;
- user data
INSERT INTO `user` (
`user_id`, `user_pass`, `user_mail`, `user_phone`,
`user_online`, `user_enable`, `user_start_date`, `user_end_date`
)
VALUES (
'test', '1234', 'mr.tumcpe@gmail.com',
'+66815447514', 0, 1, '2012-01-01', '0000-00-00'
);
9. Show tables
show tables;
+-------------------+
| Tables_in_openvpn |
+-------------------+
| log |
| user |
+-------------------+
10. Show user data
select * from user;
+---------+-----------+---------------------+--------------+-------------+-------------+-----------------+---------------+
| user_id | user_pass | user_mail | user_phone | user_online | user_enable | user_start_date | user_end_date |
+---------+-----------+---------------------+--------------+-------------+-------------+-----------------+---------------+
| test | 1234 | mr.tumcpe@gmail.com | +66815447514 | 0 | 1 | 2012-01-01 | 0000-00-00 |
+---------+-----------+---------------------+--------------+-------------+-------------+-----------------+---------------+
11. Log out
exit;
1. Install OpenVPN
III. Install lib-pam MySQL
apt-get install openvpn
2. Generate the certificate Copy the certificate generator scripts from OpenVPN docs
cp -R /usr/share/doc/openvpn/examples/easy-rsa /etc/openvpn/.
cd /etc/openvpn/easy-rsa/2.0/
3. Modify certificate variables
vi vars
Edit this file and change the following lines into your case
export KEY_COUNTRY="TH"
export KEY_PROVINCE="BKK"
export KEY_CITY="Bangkok"
export KEY_ORG="Chtunnel-VPN"
export KEY_EMAIL="support@chtunnel.com"
4. Save and exit. Run the variable script and clean
source ./vars
./clean-all
5. Generate the public and private certificates. Just press ENTER or YES by default
./build-ca
./build-key-server server
./build-key client
./build-dh
mv keys /etc/openvpn/.
In fact, generation of client is not necessary for a User/Pass authentication approach.
1. Install pam_mysql module
IV. Compose OpenVPN configuration files
apt-get install libpam-mysql
2. Configure PAM for OpenVPN Create file '/etc/pam.d/openvpn'
auth sufficient pam_mysql.so \
user=USERNAME passwd=PASSWORD 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=USERNAME passwd=PASSWORD 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
Detail
# Set db, user, passwd to your own values.
# Here crypt is the method to encrypt password in the database, which means
# 0 (or "plain") = No encryption.
# Passwords stored in plaintext. HIGHLY DISCOURAGED.
# 1 (or "Y") = Use crypt(3) function.
# 2 (or "mysql") = Use MySQL PASSWORD() function. It is possible
# that the encryption function used by PAM-MySQL
# is different from that of the MySQL server, as
# PAM-MySQL uses the function defined in MySQL's
# C-client API instead of using PASSWORD()
# SQL function in the query.
# 3 (or "md5") = Use plain hex MD5.
# 4 (or "sha1") = Use plain hex SHA1.
3. Install saslauthd
apt-get install sasl2-bin
/etc/init.d/saslauthd restart
/etc/init.d/saslauthd restart
4. Test saslauthd config
testsaslauthd -u test -p 1234 -s openvpn
5. Copy OpenVPN PAM module
cp /usr/lib/openvpn/openvpn-auth-pam.so /etc/openvpn/
1. Create file config.sh '/etc/openvpn/script/config.sh'
For each file, it forks a daemon. In this system,
we need both UDP and TCP support. I created two configuration files for two daemons in charge of UDP and TCP respectively. 4. Create file server-tcp-443.conf '/etc/openvpn/server-tcp-443.conf' for Server Port:443
V. Share Internet to Client
#!/bin/bash
##Dababase Server
HOST='127.0.0.1'
#Default port = 3306
PORT='3306'
#Username
USER='USERNAME'
#Password
PASS='PASSWORD'
#database name
DB='openvpn'
2. Create file connect.sh '/etc/openvpn/script/connect.sh'
#!/bin/bash
. /etc/openvpn/script/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=1 WHERE user_id='$common_name'"
3. Create file disconnect.sh '/etc/openvpn/script/disconnect.sh'
#!/bin/bash
. /etc/openvpn/script/config.sh
##set status offline to user disconnected
mysql -h$HOST -P$PORT -u$USER -p$PASS $DB -e "UPDATE user SET user_online=0 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'"
OpenVPN server will scan .conf files in /etc/openvpn when it starts.For each file, it forks a daemon. In this system,
we need both UDP and TCP support. I created two configuration files for two daemons in charge of UDP and TCP respectively. 4. Create file server-tcp-443.conf '/etc/openvpn/server-tcp-443.conf' for Server Port:443
##protocol port
port 443
proto tcp
dev tun
##ip server client
server 10.4.0.0 255.255.255.0
##key
ca /etc/openvpn/keys/ca.crt
cert /etc/openvpn/keys/server.crt
key /etc/openvpn/keys/server.key
dh /etc/openvpn/keys/dh1024.pem
##option
persist-key
persist-tun
keepalive 5 60
reneg-sec 432000
##option authen.
comp-lzo
user nobody
#group nogroup
client-to-client
username-as-common-name
client-cert-not-required
##user/pass auth from mysql
plugin /etc/openvpn/openvpn-auth-pam.so openvpn
##push to client
max-clients 50
push "persist-key"
push "persist-tun"
push "redirect-gateway def1"
#push "explicit-exit-notify 1"
##DNS-Server
push "dhcp-option DNS 8.8.8.8"
push "dhcp-option DNS 8.8.4.4"
##script connect-disconnect
script-security 3 system
client-connect /etc/openvpn/script/connect.sh
client-disconnect /etc/openvpn/script/disconnect.sh
##log-status
status /etc/openvpn/log/tcp_443.log
log-append /etc/openvpn/log/openvpn.log
verb 3
5. Create file server-udp-53.conf '/etc/openvpn/server-udp-53.conf' for Server Port:53
##protocol port
port 53
proto udp
dev tun
##ip server client
server 10.5.0.0 255.255.255.0
##key
ca /etc/openvpn/keys/ca.crt
cert /etc/openvpn/keys/server.crt
key /etc/openvpn/keys/server.key
dh /etc/openvpn/keys/dh1024.pem
##option
persist-key
persist-tun
keepalive 5 60
reneg-sec 432000
##option authen.
comp-lzo
user nobody
#group nogroup
client-to-client
username-as-common-name
client-cert-not-required
##user/pass auth from mysql
plugin /etc/openvpn/openvpn-auth-pam.so openvpn
##push to client
max-clients 50
push "persist-key"
push "persist-tun"
push "redirect-gateway def1"
push "explicit-exit-notify 1"
##DNS-Server
push "dhcp-option DNS 8.8.8.8"
push "dhcp-option DNS 8.8.4.4"
##script connect-disconnect
script-security 3 system
client-connect /etc/openvpn/script/connect.sh
client-disconnect /etc/openvpn/script/disconnect.sh
##log-status
status /etc/openvpn/log/udp_53.log
log-append /etc/openvpn/log/openvpn.log
verb 3
6. Create directory for log '/etc/openvpn/log'
mkdir /etc/openvpn/log
touch /etc/openvpn/log/openvpn.log
touch /etc/openvpn/log/tcp_443.log
touch /etc/openvpn/log/udp_53.log
7. Changes the permission of files
chmod -R 755 /etc/openvpn
8. Start serviece OpenVPN
/etc/init.d/openvpn start
1. Edit file /etc/sysctl.conf Remove # In line : #net.ipv4.ip_forward=1
VI. Config for Client
net.ipv4.ip_forward=1
3. Edit file /etc/rc.local Add before exit 0;
echo "1" > /proc/sys/net/ipv4/ip_forward
echo "1" > /proc/sys/net/ipv4/ip_dynaddr
iptables -A INPUT -i tun0 -j ACCEPT
iptables -A FORWARD -i tun0 -j ACCEPT
iptables -A INPUT -i tun1 -j ACCEPT
iptables -A FORWARD -i tun1 -j ACCEPT
iptables -A INPUT -p udp --dport 53 -j ACCEPT
iptables -t nat -A POSTROUTING -s 10.4.0.0/24 -o eth0 -j MASQUERADE
iptables -t nat -A POSTROUTING -s 10.5.0.0/24 -o eth0 -j MASQUERADE
4. Run Script Iptables Share Internet
/etc/rc.local
iptables-save
1. Config for port TCP port 443
Day of user in database
client
dev tun
proto tcp
remote 1.1.1.1 443
nobind
auth-user-pass
reneg-sec 432000
resolv-retry infinite
ca ca.crt
comp-lzo
verb 1
2. Config for port UDP port 53
client
dev tun
proto udp
remote 1.1.1.1 53
nobind
auth-user-pass
reneg-sec 432000
resolv-retry infinite
ca ca.crt
comp-lzo
verb 1
3. Copy file ca.crt from /etc/openvpn/keys/ca.crt to same config path in client
# If today = '2012-01-01' # day = user_start_date | user_end_date # 0 = 0000-00-00 | 0000-00-00 # 0 = 2012-01-01 | 2012-01-01 # 0 = 2012-01-02 | 2012-01-01 # 1 = 2012-01-01 | 2012-01-02 # unlimited = 2012-01-01 | 0000-00-00Install finish.
