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

OpenVPN System Based On User/Password Authentication with mysql &
Day Control (lib-pam mysql) - Debian System detail: - OpenVPN Server, MySQL Server, IP = 1.1.1.1 - lib-pam MySQL - 1 user - many connections I. Install MySQL Server for User/Pass Authentication

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;
    

II. Install OpenVPN Server and generation of certificate

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. Install lib-pam MySQL

1. Install pam_mysql module

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
    

If you get this message,
To enable saslauthd, edit /etc/default/saslauthd and set START=yes (warning). it means that saslauthd is not configured as automatically start. Edit file /etc/default/saslauthd, and change START=no to START=yes, then restart saslauthd.

/etc/init.d/saslauthd restart
    

4. Test saslauthd config

testsaslauthd -u test -p 1234 -s openvpn
    

If you get the message 0: OK "Success." then your configuration is successful. Otherwise, refer to log /var/log/auth.log and find the reason. 5. Copy OpenVPN PAM module

cp /usr/lib/openvpn/openvpn-auth-pam.so /etc/openvpn/
    

IV. Compose OpenVPN configuration files

1. Create file config.sh '/etc/openvpn/script/config.sh'

#!/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
    

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

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

Read more »

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: - OpenVPN Server, IP = 1.1.1.1 - MySQL Server, IP = 2.2.2.2 - Shell script (Customize) - 1 user - many connections 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.

Read more »

MySQL update update more than one table

MySQL update update more than one table In a MySQL update statement you can update more than one table like this

UPDATE tableA A
JOIN tableB B ON A.ID = B.ID
SET B.status = '1', B.status = '1'
WHERE A.ID = 'ID'

Or

UPDATE C
JOIN tableB B ON B.ID = C.ID
JOIN tableA A ON A.ID = B.ID
SET C.status = '1', A.status = '1'
WHERE A.ID = '1' OR A.ID = '2' OR A.ID = '3'

Read more »

SQL query : count and result in MySQL

SQL query : count and result one query in MySQL Table: banks

bank_id	bank_name	bank_abbrev
1	Bangkok	BBL
2	Kasikornthai	KBANK
3	Siam Commercial	SCB
4	Krungthai	KTB
5	Krungsri	BAY

SQL query:

SELECT b.*, t.total
FROM banks b
LEFT JOIN (
  SELECT count(*) AS total
  FROM banks
) AS t ON b.bank_id != -1
LIMIT 0, 3

Query results:

bank_id	bank_name	bank_abbrev	total
1	Bangkok	BBL	5
2	Kasikornthai	KBANK	5
3	Siam Commercial	SCB	5

Read more »

VirtualHost on Linux

VirtualHost on Linux Create file : /etc/apache2/sites-available/project

<VirtualHost *:80>
  ServerAdmin admin@project
  ServerName  project.dev
  DocumentRoot /home/Workspace/project
  <Directory /home/Workspace/project/>
    Options Indexes FollowSymLinks
    AllowOverride All
    Order allow,deny
    allow from all
  </Directory>
  ErrorLog "|/usr/sbin/rotatelogs /home/log/apache_logs/project/error.log.%Y-%m-%d-%H_%M_%S 86400"
  CustomLog "|/usr/sbin/rotatelogs /home/log/apache_logs/project/access.log.%Y-%m-%d-%H_%M_%S 86400" combined
</VirtualHost>

Make link

ln -s /etc/apache2/sites-available/project /etc/apache2/sites-enabled/.

Insert to file /etc/hosts

127.0.0.1   project.dev

Create dir for log

mkdir /home/log/apache_logs/project

Restart Apache

/etc/init.d/apache2 restart

Read more »

.htacceess: Invalid command 'RewriteEngine'

.htacceess: Invalid command 'RewriteEngine' You haven't loaded mod_rewrite. (as a superuser)

a2enmod rewrite
apache2ctl restart

Read more »

sudoers command in root user

sudoers command in root user file : /etc/sudoers

user_group    ALL=(ALL) NOPASSWD: /usr/local/bin/command.sh
#Cmnd_Alias   COMMAND_USER = /usr/local/bin/command.sh
#user_group   ALL=(ALL) NOPASSWD: COMMAND_USER

Read more »

ASP.NET Error The conversion of a nvarchar data type to a smalldatetime data type resulted in an out-of-range value.

ASP.NET Error The conversion of a nvarchar data type to a smalldatetime data type resulted in an out-of-range value. Can fix:

userQuizDataSource.InsertParameters.Add(
    "DateTimeComplete",
    TypeCode.DateTime,
    DateTime.Now.ToString()
);

Read more »

ReadFile Encoding UTF-8 - java

ReadFile Encoding UTF-8 - java

import java.io.BufferedReader;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStreamReader;

public class ReadUTF8Data {
    private static final String FILE_ENCODE = "UTF-8";
    public static void main(String[] args) {
        String data = null;
        BufferedReader dataIns = null;
        String file = "";
        try {
            dataIns = new BufferedReader(new InputStreamReader(
            new FileInputStream(file), FILE_ENCODE));
            
            while ((data = dataIns.readLine()) != null) {
                System.out.println("Line 1 :" + data);
            }
            dataIns.close();
            dataIns = null;
            } catch (IOException e) {
            e.printStackTrace();
        }
    }
}

Read more »