{"id":19398,"date":"2017-01-27T06:37:16","date_gmt":"2017-01-26T22:37:16","guid":{"rendered":"https:\/\/2blog.ilc.edu.tw\/25793\/2017\/01\/27\/%e5%bb%ba%e7%bd%ae%e4%bd%bf%e7%94%a8%e5%b8%b3%e8%99%9f%e5%8f%8a%e5%af%86%e7%a2%bc%e8%aa%8d%e8%ad%89%e7%9a%84-openvpn-server-1\/"},"modified":"2021-03-18T17:24:54","modified_gmt":"2021-03-18T09:24:54","slug":"%e5%bb%ba%e7%bd%ae%e4%bd%bf%e7%94%a8%e5%b8%b3%e8%99%9f%e5%8f%8a%e5%af%86%e7%a2%bc%e8%aa%8d%e8%ad%89%e7%9a%84-openvpn-server-1","status":"publish","type":"post","link":"https:\/\/2blog.ilc.edu.tw\/25793\/2017\/01\/27\/%e5%bb%ba%e7%bd%ae%e4%bd%bf%e7%94%a8%e5%b8%b3%e8%99%9f%e5%8f%8a%e5%af%86%e7%a2%bc%e8%aa%8d%e8%ad%89%e7%9a%84-openvpn-server-1\/","title":{"rendered":"\u5efa\u7f6e\u4f7f\u7528\u5e33\u865f\u53ca\u5bc6\u78bc\u8a8d\u8b49\u7684 OpenVPN Server – 1"},"content":{"rendered":"

\u53c3\u8003\u7db2\u7ad9\uff1a
OpenVPN System Based On User\/Password Authentication with mysql & Day Control (shell script)- Debian ~ Mr.TUM’s Blog<\/a> <\/p>\n

\u53c3\u8003\u7db2\u7ad9\u4e2d\uff0cOpenVPN \u548c MySQL \u5206\u5225\u5c6c\u65bc\u4e0d\u540c\u4e3b\u6a5f\uff0c\u5728\u9019\u88e1\u6539\u7528\u540c\u4e00\u4e3b\u6a5f\u3002 <\/p>\n

1. \u5b89\u88dd MySQL Server
# apt-get install mariadb-server<\/span>[@more@]2. \u8a2d\u5b9a root \u5bc6\u78bc\u53ca\u4e00\u4e9b\u5b89\u5168\u6027\u4e0a\u7684\u8a2d\u5b9a
# \/usr\/bin\/mysql_secure_installation<\/span>
\u9084\u672a\u8a2d\u5b9a root \u5bc6\u78bc\uff0c\u6240\u4ee5\u76f4\u63a5\u6309 Enter \u9375
Enter current password for root (enter for none):<\/span>
OK, successfully used password, moving on…<\/span> <\/p>\n

Setting the root password ensures that nobody can log into the MariaDB<\/span>
root user without the proper authorisation.<\/span> <\/p>\n

\u8a2d\u5b9a MySQL root \u5bc6\u78bc
Set root password? [Y\/n]<\/span>
New password:<\/span>
Re-enter new password:<\/span>
Password updated successfully!<\/span>
Reloading privilege tables..<\/span>
\u00a0… Success!<\/span> <\/p>\n

By default, a MariaDB installation has an anonymous user, allowing anyone<\/span>
to log into MariaDB without having to have a user account created for<\/span>
them.\u00a0 This is intended only for testing, and to make the installation<\/span>
go a bit smoother.\u00a0 You should remove them before moving into a<\/span>
production environment.<\/span> <\/p>\n

\u79fb\u9664\u533f\u540d\u4f7f\u7528\u8005
Remove anonymous users? [Y\/n]<\/span>
\u00a0… Success!<\/span> <\/p>\n

Normally, root should only be allowed to connect from ‘localhost’.\u00a0 This<\/span>
ensures that someone cannot guess at the root password from the network.<\/span> <\/p>\n

\u53d6\u6d88 root \u53ef\u4ee5\u9060\u7aef\u767b\u5165
Disallow root login remotely? [Y\/n]<\/span>
\u00a0… Success!<\/span> <\/p>\n

By default, MariaDB comes with a database named ‘test’ that anyone can<\/span>
access.\u00a0 This is also intended only for testing, and should be removed<\/span>
before moving into a production environment.<\/span> <\/p>\n

\u79fb\u9664 \u6e2c\u8a66\u7684\u8cc7\u6599\u5eab
Remove test database and access to it? [Y\/n]<\/span>
\u00a0– Dropping test database…<\/span>
\u00a0… Success!<\/span>
\u00a0– Removing privileges on test database…<\/span>
\u00a0… Success!<\/span> <\/p>\n

Reloading the privilege tables will ensure that all changes made so far<\/span>
will take effect immediately.<\/span> <\/p>\n

\u91cd\u65b0\u8f09\u5165\u8cc7\u6599\u8868\u6b0a\u9650
Reload privilege tables now? [Y\/n]<\/span>
\u00a0… Success!<\/span> <\/p>\n

Cleaning up…<\/span><\/p>\n

3. \u5efa\u7acb openvpn \u8cc7\u6599\u5eab\uff0c\u4e26\u5efa\u7acb\u4e00\u500b\u4f7f\u7528\u8005\u53ca\u8a2d\u5b9a\u5bc6\u78bc\u4f86\u9032\u884c\u7ba1\u7406
# \/usr\/bin\/mysql -u root -p<\/span>
MariaDB [(none)]> CREATE DATABASE openvpn;<\/span>
MariaDB [(none)]> GRANT ALL ON openvpn.* TO ‘pi’@”%” IDENTIFIED BY ‘123456’;<\/span>
MariaDB [(none)]> FLUSH PRIVILEGES;<\/span>
MariaDB [(none)]> exit;<\/span> <\/p>\n

4. \u6539\u7528 pi \u4f7f\u7528\u8005\u4f86\u5efa\u7acb openvpn \u76f8\u95dc\u8cc7\u6599\u5eab\u8a2d\u5b9a
# \/usr\/bin\/mysql -u pi -p<\/span><\/p>\n

5. \u958b\u555f openvpn \u8cc7\u6599\u5eab
MariaDB [(none)]> USE openvpn;<\/span><\/p>\n

6. \u5efa\u7acb user \u8cc7\u6599\u8868<\/p>\n

CREATE TABLE IF NOT EXISTS `user` ( <\/span>
\u00a0\u00a0\u00a0 `user_id` varchar(32) COLLATE utf8_unicode_ci NOT NULL, <\/span>
\u00a0\u00a0\u00a0 `user_pass` varchar(32) COLLATE utf8_unicode_ci NOT NULL DEFAULT ‘1234’, <\/span>
\u00a0\u00a0\u00a0 `user_mail` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL, <\/span>
\u00a0\u00a0\u00a0 `user_phone` varchar(16) COLLATE utf8_unicode_ci DEFAULT NULL, <\/span>
\u00a0\u00a0\u00a0 `user_online` tinyint(1) NOT NULL DEFAULT ‘0’, <\/span>
\u00a0\u00a0\u00a0 `user_enable` tinyint(1) NOT NULL DEFAULT ‘1’, <\/span>
\u00a0\u00a0\u00a0 `user_start_date` date NOT NULL, <\/span>
\u00a0\u00a0\u00a0 `user_end_date` date NOT NULL, <\/span>
PRIMARY KEY (`user_id`), <\/span>
KEY `user_pass` (`user_pass`) <\/span>
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;<\/span><\/p>\n

\n
<\/div>\n
7. \u5efa\u7acb log \u8cc7\u6599\u8868<\/div>\n
CREATE TABLE IF NOT EXISTS `log` ( <\/span>
\u00a0\u00a0\u00a0 `log_id` int(10) unsigned NOT NULL AUTO_INCREMENT, <\/span>
\u00a0\u00a0\u00a0 `user_id` varchar(32) COLLATE utf8_unicode_ci NOT NULL, <\/span>
\u00a0\u00a0\u00a0 `log_trusted_ip` varchar(32) COLLATE utf8_unicode_ci DEFAULT NULL, <\/span>
\u00a0\u00a0\u00a0 `log_trusted_port` varchar(16) COLLATE utf8_unicode_ci DEFAULT NULL, <\/span>
\u00a0\u00a0\u00a0 `log_remote_ip` varchar(32) COLLATE utf8_unicode_ci DEFAULT NULL, <\/span>
\u00a0\u00a0\u00a0 `log_remote_port` varchar(16) COLLATE utf8_unicode_ci DEFAULT NULL, <\/span>
\u00a0\u00a0\u00a0 `log_start_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, <\/span>
\u00a0\u00a0\u00a0 `log_end_time` timestamp NOT NULL DEFAULT ‘0000-00-00 00:00:00’, <\/span>
\u00a0\u00a0\u00a0 `log_received` float NOT NULL DEFAULT ‘0’, <\/span>
\u00a0\u00a0\u00a0 `log_send` float NOT NULL DEFAULT ‘0’, <\/span>
PRIMARY KEY (`log_id`), <\/span>
KEY `user_id` (`user_id`) <\/span>
) ENGINE=MyISAM\u00a0 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;<\/span><\/div>\n
<\/div>\n
8. \u5efa\u7acb\u4f7f\u7528\u8005\u8cc7\u6599<\/div>\n
INSERT INTO `user` ( <\/span>
\u00a0\u00a0\u00a0 `user_id`, `user_pass`, `user_mail`, `user_phone`, <\/span>
\u00a0\u00a0\u00a0 `user_online`, `user_enable`, `user_start_date`, `user_end_date` <\/span>
) <\/span>
VALUES ( <\/span>
\u00a0\u00a0\u00a0 ‘test’, ‘123456’, ‘test@test.com’, <\/span>
\u00a0\u00a0\u00a0 ‘+66815447514’, 0, 1, ‘2012-01-01’, ‘0000-00-00’ <\/span>
);<\/span><\/div>\n
<\/div>\n<\/div>\n

9. \u986f\u793a\u8cc7\u6599\u5eab\u4e2d\u7684\u8cc7\u6599\u8868
MariaDB [openvpn]> show tables;<\/span>
+————————–+<\/span>
| Tables_in_openvpn |<\/span>
+—————————+<\/span>
| log\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |<\/span>
| user\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0\u00a0 |<\/span>
+—————————-+<\/span>
2 rows in set (0.00 sec)<\/span><\/p>\n

10. \u5217\u51fa user \u8cc7\u6599\u8868\u4e2d\u7684\u4f7f\u7528\u8005\u8cc7\u6599
MariaDB [openvpn]> select user_id,user_pass from user;<\/span>
+———-+—————-+<\/span>
| user_id | user_pass \u00a0 |<\/span>
+———-+—————–+<\/span>
| test\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | 123456 \u00a0 \u00a0 \u00a0\u00a0 |<\/span>
+———-+—————–+<\/span>
1 row in set (0.00 sec)<\/span><\/p>\n

11. \u9000\u51fa\u8cc7\u6599\u5eab
MariaDB [openvpn]> exit;<\/span><\/p>\n

12 . \u5efa\u7acb Script \u6a94\u76ee\u9304 \/etc\/openvpn\/script<\/span>
# mkdir \/etc\/openvpn\/script<\/span><\/p>\n

13. \u5efa\u7acb \/etc\/openvpn\/script\/config.sh<\/span>
# cat etc\/openvpn\/script\/config.sh<\/span>
#!\/bin\/bash<\/span>
##Dababase Server<\/span>
HOST=’127.0.0.1′<\/span>
#Default port = 3306<\/span>
PORT=’3306′<\/span>
#Username<\/span>
USER=’pi’<\/span>
#Password<\/span>
PASS=’123456′<\/span>
#database name<\/span>
DB=’openvpn’<\/span><\/p>\n

14. \u5efa\u7acb \/etc\/openvpn\/script\/test_connect_db.sh<\/span>
# cat \/etc\/openvpn\/script\/test_connect_db.sh<\/span>
#!\/bin\/bash<\/span>
. \/etc\/openvpn\/script\/config.sh<\/span>
##Test Authentication<\/span>
username=$1<\/span>
password=$2<\/span>
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′)”)<\/span>
##Check user<\/span>
[ “$user_id” != ” ] && [ “$user_id” = “$username” ] && echo “user : $username” && echo ‘authentication ok.’ && exit 0 || echo ‘authentication failed.’; exit 1<\/span><\/p>\n

15. \u5efa\u7acb \/etc\/openvpn\/script\/login.sh<\/span>
# cat \/etc\/openvpn\/script\/login.sh<\/span>
#!\/bin\/bash<\/span>
. \/etc\/openvpn\/script\/config.sh<\/span>
##Authentication<\/span>
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′)”)<\/span>
##Check user<\/span>
[ “$user_id” != ” ] && [ “$user_id” = “$username” ] && echo “user : $username” && echo ‘authentication ok.’ && exit 0 || echo ‘authentication failed.’; exit 1<\/span><\/p>\n

16. \u5efa\u7acb \/etc\/openvpn\/script\/connect.sh<\/span>
# cat \/etc\/openvpn\/script\/connect.sh<\/span>
#!\/bin\/bash<\/span>
. \/etc\/openvpn\/script\/config.sh<\/span>
##insert data connection to table log<\/span>
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’)”<\/span>
##set status online to user connected<\/span>
mysql -h$HOST -P$PORT -u$USER -p$PASS $DB -e “UPDATE user SET user_online=1 WHERE user_id=’$common_name'”<\/span><\/p>\n

17. \u5efa\u7acb \/etc\/openvpn\/script\/disconnect.sh<\/span>
# cat \/etc\/openvpn\/script\/disconnect.sh<\/span>
#!\/bin\/bash<\/span>
. \/etc\/openvpn\/script\/config.sh<\/span>
##set status offline to user disconnected<\/span>
mysql -h$HOST -P$PORT -u$USER -p$PASS $DB -e “UPDATE user SET user_online=0 WHERE user_id=’$common_name'”<\/span>
##insert data disconnected to table log<\/span>
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′”<\/span><\/p>\n

18. \u66f4\u6539 Script \u6a94\u6848\u6b0a\u9650
# chmod 755 \/etc\/openvpn\/script\/*.sh<\/span><\/p>\n

19. \u4fee\u6539 \/etc\/openvpn\/server.conf<\/span>\u00a0 \u8a2d\u5b9a\u6a94
# vim \/etc\/openvpn\/server.conf<\/span>
\u52a0\u5165\u4ee5\u4e0b\u8a2d\u5b9a
username-as-common-name<\/span>
client-cert-not-required<\/span>
auth-user-pass-verify \/etc\/openvpn\/script\/login.sh via-env<\/span><\/p>\n

# \u8a2d\u5b9a\u4f7f\u7528\u8005\u767b\u5165\u53ca\u767b\u51fa\u6642\u8981\u505a\u7684\u52d5\u4f5c
##script connect-disconnect
script-security 3 system<\/span>
client-connect \/etc\/openvpn\/script\/connect.sh<\/span>
client-disconnect \/etc\/openvpn\/script\/disconnect.sh<\/span><\/p>\n

20. \u6e2c\u8a66 MariaDB SQL Server \u53ef\u5426\u6b63\u5e38\u9023\u7dda(\u5e33\u865f\/\u5bc6\u78bc\uff1atest \/ 123456)
# \/etc\/openvpn\/script\/test_connect_db.sh test 123456<\/span>
user : test<\/span>
authentication ok.<\/span><\/p>\n

\u5982\u679c\u662f\u4e0a\u9762\u7684\u8a0a\u606f\uff0c\u5247\u662f\u9023\u7dda\u6210\u529f\uff01<\/p>\n

21. \u8a2d\u5b9a OpenVPN Client \u7aef *.ovpn
\u52a0\u5165\u4ee5\u4e0b\u8a2d\u5b9a
auth-user-pass<\/span>
reneg-sec 0<\/span><\/p>\n

22. \u91cd\u65b0\u555f\u52d5 OpenVPN Server
# systemctl restart openvpn@server.service<\/span><\/p>\n

23. \u5217\u51fa\u4f7f\u7528\u8005\u7684\u767b\u5165\u8cc7\u6599
MariaDB [openvpn]> select user_id,log_trusted_ip,log_remote_ip,log_start_time,log_end_time from log;<\/span>
+———-+——————–+———————+——————————+—————————–+<\/span>
| user_id | log_trusted_ip | log_remote_ip | log_start_time\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | log_end_time\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |<\/span>
+———-+——————–+———————+——————————+——————————+<\/span>
| test\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | 1.162.15.9\u00a0\u00a0\u00a0\u00a0\u00a0 | 10.8.0.6\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | 2016-12-29 09:27:32 | 2016-12-29 10:27:36 |<\/span>
+———-+——————–+———————+——————————+——————————+<\/span><\/p>\n","protected":false},"excerpt":{"rendered":"

\u53c3\u8003\u7db2\u7ad9\uff1a OpenVPN System Based On User\/Password Authenticat … <\/p>\n

\u95b1\u8b80\u5168\u6587\u3008\u5efa\u7f6e\u4f7f\u7528\u5e33\u865f\u53ca\u5bc6\u78bc\u8a8d\u8b49\u7684 OpenVPN Server – 1\u3009<\/span><\/a><\/p>\n","protected":false},"author":55,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[116,168,119],"tags":[],"class_list":["post-19398","post","type-post","status-publish","format-standard","hentry","category-c-78461","category-c-84780","category-c-78464"],"meta_key":{"_pingme":["1"],"_encloseme":["1"],"lt_post_id":["681403"]},"_links":{"self":[{"href":"https:\/\/2blog.ilc.edu.tw\/25793\/wp-json\/wp\/v2\/posts\/19398"}],"collection":[{"href":"https:\/\/2blog.ilc.edu.tw\/25793\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/2blog.ilc.edu.tw\/25793\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/2blog.ilc.edu.tw\/25793\/wp-json\/wp\/v2\/users\/55"}],"replies":[{"embeddable":true,"href":"https:\/\/2blog.ilc.edu.tw\/25793\/wp-json\/wp\/v2\/comments?post=19398"}],"version-history":[{"count":1,"href":"https:\/\/2blog.ilc.edu.tw\/25793\/wp-json\/wp\/v2\/posts\/19398\/revisions"}],"predecessor-version":[{"id":20936,"href":"https:\/\/2blog.ilc.edu.tw\/25793\/wp-json\/wp\/v2\/posts\/19398\/revisions\/20936"}],"wp:attachment":[{"href":"https:\/\/2blog.ilc.edu.tw\/25793\/wp-json\/wp\/v2\/media?parent=19398"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/2blog.ilc.edu.tw\/25793\/wp-json\/wp\/v2\/categories?post=19398"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/2blog.ilc.edu.tw\/25793\/wp-json\/wp\/v2\/tags?post=19398"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}