15 June 2012

OpenVPN System Based On User/Password Authentication with mysql & Day Control (shell script)- Debian

OpenVPN System Based On User/Password Authentication with mysql &
Day Control (shell script) - Debian
System detail: I. Install MySQL Server for User/Pass Authentication, IP = 2.2.2.2

1. Install MySQL Server
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;
    
12. Edit file /etc/mysql/my.cnf insert # to line
bind-address  = 127.0.0.1
    
success
#bind-address  = 127.0.0.1
    
13. Edit file /etc/rc.local Add before exit 0;
iptables -A INPUT -i eth0 -p tcp -m tcp --dport 3306 -j ACCEPT
    
14. Run Script Iptables
/etc/rc.local
iptables-save
    
II. Install OpenVPN Server and generation of certificate, IP = 1.1.1.1
1. Install OpenVPN
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.
III. Customize shell script, IP = 1.1.1.1
1. Create directory for script '/etc/openvpn/script'
mkdir /etc/openvpn/script
cd /etc/openvpn/script
    
2. Create file config.sh '/etc/openvpn/script/config.sh'
#!/bin/bash
##Dababase Server
HOST='2.2.2.2'
#Default port = 3306
PORT='3306'
#Username
USER='USERNAME'
#Password
PASS='PASSWORD'
#database name
DB='openvpn'
    
3. Create file test_connect_db.sh'/etc/openvpn/script/test_connect_db.sh'
#!/bin/bash
. /etc/openvpn/script/config.sh
##Test Authentication
username=$1
password=$2
user_id=$(mysql -h$HOST -P$PORT -u$USER -p$PASS $DB -sN -e "select user_id from user where user_id = '$username' AND user_pass = '$password' AND user_enable=1 AND user_start_date != user_end_date AND TO_DAYS(now()) >= TO_DAYS(user_start_date) AND (TO_DAYS(now()) <= TO_DAYS(user_end_date) OR user_end_date='0000-00-00')")
##Check user
[ "$user_id" != '' ] && [ "$user_id" = "$username" ] && echo "user : $username" && echo 'authentication ok.' && exit 0 || echo 'authentication failed.'; exit 1
    
4. Create file login.sh '/etc/openvpn/script/login.sh'
#!/bin/bash
. /etc/openvpn/script/config.sh
##Authentication
user_id=$(mysql -h$HOST -P$PORT -u$USER -p$PASS $DB -sN -e "select user_id from user where user_id = '$username' AND user_pass = '$password' AND user_enable=1 AND user_start_date != user_end_date AND TO_DAYS(now()) >= TO_DAYS(user_start_date) AND (TO_DAYS(now()) <= TO_DAYS(user_end_date) OR user_end_date='0000-00-00')")
##Check user
[ "$user_id" != '' ] && [ "$user_id" = "$username" ] && echo "user : $username" && echo 'authentication ok.' && exit 0 || echo 'authentication failed.'; exit 1
    
5. 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'"
    
6. 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'"
    
Compose OpenVPN configuration files, 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.
7. 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
auth-user-pass-verify /etc/openvpn/script/login.sh via-env

##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
    
8. 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
auth-user-pass-verify /etc/openvpn/script/login.sh via-env

##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
    
9. 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
    
10. Changes the permission of files
chmod -R 755 /etc/openvpn
    
11. Test authentication username 'test' and password '1234'
/etc/openvpn/script/test_connect_db.sh test 1234
# user : test
# authentication ok.
# if authentication failed. check user and password in database
# or detail database server in /etc/openvpn/script/config.sh
    
12. Start serviece OpenVPN
/etc/init.d/openvpn start
    
IV. Share Internet to Client
1. Edit file /etc/sysctl.conf Remove # In line : #net.ipv4.ip_forward=1
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 -A INPUT -i eth0 -p tcp -m tcp --dport 3306 -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
    
V. Config for Client
1. Config for port TCP port 443
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
Day of user in database
# 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-00
Install finish.

34 comments:

Cute said...

Thank you very much for this. I used this to centralize the authentication of my VPN servers.

My next project is to create registration and monitoring pages in PHP even though I am not good with PHP.

ralphskie said...

Hi

Do you have knowledge on how to implement this on a remote mysql server?

lets say ServerA has a mysql installed
Server B and Server C will query on the Remote MySql of ServerA when clients will connect to either ServerB or ServerC.

Hope you get my question.
Thank you.

noob said...

how can i connect or create web based using sql database or how to synchronize this database to other database?

hope you could create tutorial for web based using this sql

thank you

Ruzaini Ourtech said...

using ubuntu veraion what

Ruzaini Ourtech said...

Version

Chagridsada Boonthus said...

Success new free version. 13 june 2012

Ruzaini Ourtech said...

how if i using webhost??

port delete??

Ruzaini Ourtech said...

please repost your old tutorial.
i cannot use this
im using webhost for mysql auth

Chagridsada Boonthus said...

old tutorial
http://chagridsada.blogspot.com/2012/06/openvpn-system-based-on-user-pass-lib.html

Anonymous said...

why i cannot auth after a few hour,
i have to restart again and again few hour

Anonymous said...

echo "1" > /proc/sys/net/ipv4/ip_dynaddr
what for??

Ruzaini Ourtech said...

hello.can tell me how to get protocol type in mysql

ruzainiourtech.com/capture.png

example for geting trusted_ip is $trusted_ip
how about protocol
UDP,TCP

Chagridsada Boonthus said...

Try $proto_1

Chagridsada Boonthus said...

echo "1" > /proc/sys/net/ipv4/ip_dynaddr
http://www.mjmwired.net/kernel/Documentation/networking/ip_dynaddr.txt

Unknown said...

EXCELLENT !!!

It would be much easier and better if all of this ported to PHP or another scripting language so we can manage everythings from web browser.

Input, update, username and password from an admin panel ... etc.

And end user just need to register through our web pages.

Just an idea.

Anonymous said...

admin
can u custom coding openvpn to make icmp tunneling using mysql authentication like this.
all my isp in my country block many udp n tcp port

Anonymous said...

credit 4 u

thank's this is very helpful... great!!

mca295188 said...

hello Mr.Tum

your script is helping me out very effectively

first of all thanks for this script and tutorial

but i have only 1 question if a user don't disconnect and use reconnect option than the main problem arises no disconnection take place, no user table updated and row in log table never shows data received and send and an end time of the client

can you do something in regard to this problem

Lê Dũng said...

very great!!!
Thank you much!!!

hochong dhochong said...

how to see the total usage , by a single user?

hochong dhochong said...

and also i want to know how to prevent multiple login from same user id. i want to make 1 user id per user...

Ruzaini ourtech said...
This comment has been removed by the author.
Anonymous said...

Hello if a user tries to login to the same server from multiple devices at the same time, they are rejected. Is there anyway that two users can use the same account at the same time?

vqoley said...

in bash script, how to make $password in md5 or $password in md5 with salt?

Anonymous said...

I'm getting this error when testing the connection: /etc/openvpn/script/test_connect_db.sh: line 6: mysql: command not found
authentication failed.

I'm not hosting the mySQL database on this server, it's on another but I made sure to enter the right login information in etc/openvpn/script/config.sh. And ideas why it would give me an error on line 6?

nelly_technology said...

Justin Miller:
did you allow outside/remote access to your remote mysql server

Warnet Amanah Sragen said...

how make 1 user single connection with this?

Sakhawath Hossen said...

why UDP not working

Jr. Williams said...

Which is the best and cheap web host?
Unlimited Subdomains

Jr. Williams said...

Your Post is very useful, I am truly happy to post my note on this blog . It helped me with ocean of awareness so I really consider you will do much better in the future.
best domain name registration

Paolo Meraviglia said...

Hi, I have a question: I modified your script to keep the IP assigned from ccd dir generated from mysql and keep password saved in md5. With this scenario I have 2 problems:
1) even if --duplicate-cn is not in my config, a user can login multiple times and have multiple client connected
2)WARNING: POTENTIALLY DANGEROUS OPTION --client-cert-not-required may accept clients which do not present a certificate

Do you have idea of how to solve this?

raghu konda said...

Nice and good article.. it is very useful for me to learn and understand easily.. thanks for sharing your valuable information and time.. please keep updating.more 
php jobs in hyderabad.

Unknown said...

• Nice and good article. It is very useful for me to learn and understand easily. Thanks for sharing your valuable information and time. Please keep updatingAzure Online course

WhatsappSMS gratis via web said...

Cheapest RDP Windows vpn linux on earth !
Try this :
Cheap-RDP

Post a Comment

Twitter Delicious Facebook Digg Stumbleupon Favorites More

 
Blog by Chagridsada Boonthus | http://chagridsada.blogspot.com/