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.

39 comments:

Unknown 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

Unknown said...

using ubuntu veraion what

Unknown said...

Version

Unknown said...

Success new free version. 13 june 2012

Unknown said...

how if i using webhost??

port delete??

Unknown said...

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

Unknown 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??

Unknown 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

Unknown said...

Try $proto_1

Unknown 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

Unknown said...

very great!!!
Thank you much!!!

Unknown said...

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

Unknown 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...

Unknown 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?

Unknown 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

Kied said...

how make 1 user single connection with this?

Localhost 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

Unknown 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?

Unknown said...

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

gautham said...

i really enjoyed a lot from this blog ui course online

svrtechnologies said...

This post is really nice and informative. The explanation given is really comprehensive and informative on windows azure training

Netexpertise said...

Passwords are stored UNENCRYPTED in the database.
Pity, this could be easily fixed using the mysql PASSWORD function or any SHA within the script

Dave
----
htttp://www.netexpertise.eu

Netexpertise said...

It also raises some major security issues such as SQL injections making it INSECURE
You can easily connect to the VPN just knowing a user login WITHOUT any password, since the password in the database isn't checked against the one in the form

Replace the password with something like:
' or user_id='VALIDUSER'; --
And you're IN!

Dave
----
http://www.netexpertise.eu

Unknown said...

instagram takipçi satın al
ucuz takipçi
takipçi satın al
https://takipcikenti.com
https://ucsatinal.org
instagram takipçi satın al
https://perdemodelleri.org
https://yazanadam.com
instagram takipçi satın al
balon perdeler
petek üstü perde
mutfak tül modelleri
kısa perde modelleri
fon perde modelleri
tül perde modelleri
https://atakanmedya.com
https://fatihmedya.com
https://smmpaketleri.com
https://takipcialdim.com
https://yazanadam.com
yasaklı sitelere giriş
aşk kitapları
yabancı şarkılar
sigorta sorgula
https://cozumlec.com
word indir ücretsiz
tiktok jeton hilesi
rastgele görüntülü sohbet
fitness moves
gym workouts
https://marsbahiscasino.org
http://4mcafee.com
http://paydayloansonlineare.com

Anonymous said...

perde modelleri
sms onay
vodafone mobil ödeme bozdurma
Nft nasıl alınır
ANKARA EVDEN EVE NAKLİYAT
Trafik sigortasi
dedektör
Web sitesi kurma
ASK KİTAPLARİ

Anonymous said...

smm panel
SMM PANEL
iş ilanları
instagram takipçi satın al
hirdavatciburada.com
beyazesyateknikservisi.com.tr
SERVİS
Tiktok Jeton Hile

Post a Comment

Twitter Delicious Facebook Digg Stumbleupon Favorites More

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