openVBN+MySQL用户认证

• 1.创建数据库

MariaDB [(none)]> CREATE DATABASE IF NOT EXISTS openvpn DEFAULT CHARSET utf8;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> GRANT ALL PRIVILEGES ON openvpn.* TO vpn@’localhost’ IDENTIFIED BY ‘vpn123456′;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> GRANT ALL PRIVILEGES ON openvpn.* TO vpn@’127.0.0.1’ IDENTIFIED BY ‘vpn123456’;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> use openvpn
Database changed
1
2
3
4
5
6
7
8
9
10
11

• 创建用户表

MariaDB [openvpn]> CREATE TABLE IF NOT EXISTS user (username char(32) COLLATE utf8_unicode_ci NOT NULL,password char(128) COLLATE utf8_unicode_ci DEFAULT NULL,active int(10) NOT NULL DEFAULT 1,creation timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,expired_time timestamp NOT NULL DEFAULT ‘0000-00-00 00:00:00’, PRIMARY KEY (username)) DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
Query OK, 0 rows affected (0.09 sec)
1
2

• —–有自增ID的不推荐使用——–

MariaDB [openvpn]> CREATE TABLE IF NOT EXISTS USER (
-> id int(4) NOT NULL AUTO_INCREMENT COMMENT ‘主键id’,
-> username CHAR (32) COLLATE utf8_unicode_ci NOT NULL,
-> PASSWORD CHAR (128) COLLATE utf8_unicode_ci DEFAULT NULL,
-> active INT (10) NOT NULL DEFAULT 1,
-> creation TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
-> expired_time TIMESTAMP NOT NULL DEFAULT ‘0000-00-00 00:00:00’,
-> PRIMARY KEY (id)
-> )DEFAULT CHARSET = utf8 COLLATE = utf8_unicode_ci;
Query OK, 0 rows affected (0.04 sec)
1
2
3
4
5
6
7
8
9
10

使用以下

CREATE TABLE IF NOT EXISTS USER (
id int(4) NOT NULL AUTO_INCREMENT COMMENT ‘主键id’,
username CHAR (32) COLLATE utf8_unicode_ci NOT NULL,
PASSWORD CHAR (128) COLLATE utf8_unicode_ci DEFAULT NULL,
active INT (10) NOT NULL DEFAULT 1,
creation TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
expired_time TIMESTAMP NOT NULL DEFAULT ‘0000-00-00 00:00:00’,
PRIMARY KEY (id)
)DEFAULT CHARSET = utf8 COLLATE = utf8_unicode_ci;
1
2
3
4
5
6
7
8
9

• 创建VPN用户

MariaDB [openvpn]> INSERT INTO user(username, password, expired_time) VALUES(‘test’, ENCRYPT(‘123456’), DATE_ADD(CURRENT_TIMESTAMP, INTERVAL 30 DAY));
Query OK, 1 row affected (0.02 sec)
1
2

• 创建登录日志

CREATE TABLE logtable (msg char(254),user char(100),pid char(100),host char(100),rhost char(100),time char(100));
1

• 下载vpn软件包—–

rpm -ivh http://dl.fedoraproject.org/pub/epel/epel-release-latest-7.noarch.rpm

yum install openssl-devel lzo-devel openvpn openvpn-devel easy-rsa openvpn-devel pam pam-devel

yum install -y automake pam pam-devel openssl-devel
yum install pam_mysql pam_krb5 pam pam_devel -y

yum install -y pam-devel fprintd-pam pam_passwdqc pam
yum install mariadb-devel
1
2
3
4
5
6
7
8
9

• MySQL编译安装认证模块

wget http://prdownloads.sourceforge.net/pam-mysql/pam_mysql-0.7RC1.tar.gz

./configure –with-openssl –with-pam-mods-dir=/lib/security/
make && make install
1
2
3
4

• 安装完成后，会生成如下两个文件

/lib/security/pam_mysql.la /lib/security/pam_mysql.so
1

• 配置pam.d验证

[root@fiel ~]# egrep -v “#|^$;” /etc/pam.d/openvpn
auth sufficient /lib/security/pam_mysql.so user=vpn passwd=vpn123456 host=localhost db=openvpn table=user logtable=logtable usercolumn=username passwdcolumn=password where=active=1 crypt=1 sqllog=true logmsgcolumn=msg logusercolumn=user logpidcolumn=pid loghostcolumn=host logrhostcolumn=rhost logtimecolumn=time

account required /lib/security/pam_mysql.so user=vpn passwd=vpn123456 host=localhost db=openvpn table=user logtable=logtable usercolumn=username passwdcolumn=password where=active=1 crypt=1 sqllog=true logmsgcolumn=msg logusercolumn=user logpidcolumn=pid loghostcolumn=host logrhostcolumn=rhost logtimecolumn=time
1
2
3
4

- yum -y install cyrus-sasl
（这个命令的包testsaslauthd）
———没有启动报错的———

[root@fiel pam_mysql-0.7RC1]# testsaslauthd -u test -p 123456 -s openvpn
connect() : No such file or directory
0: [root@fiel pam_mysql-0.7RC1]#
1
2
3

—启动后——–

[root@fiel pam_mysql-0.7RC1]# systemctl start saslauthd.service
[root@fiel pam_mysql-0.7RC1]# systemctl status saslauthd.service
● saslauthd.service – SASL authentication daemon.
Loaded: loade
(/usr/lib/systemd/system/saslauthd.service; disabled; vendor preset: disabled)
Active: active (running) since 四 2018-06-14 03:58:44 EDT; 1s ago
Process: 25444 ExecStart=/usr/sbin/saslauthd -m $SOCKETDIR -a $MECH $FLAGS (code=exited, status=0/SUCCESS)
1
2
3
4
5
6
7

• 验证成功

[root@fiel pam_mysql-0.7RC1]# testsaslauthd -u test -p 123456 -s openvpn
0: OK “Success.”
1
2

安装openVBN就不介绍了
服务端配置

[root@fiel ~]# cat /etc/openvpn/server/server.conf
port 1194
proto tcp
dev tun
ca /etc/openvpn/pki/ca.crt
cert /etc/openvpn/pki/issued/server.crt
key /etc/openvpn/pki/private/server.key
dh /etc/openvpn/pki/dh.pem
server 11.1.1.0 255.255.255.0

ifconfig-pool-persist ipp.txt
client-to-client
keepalive 10 120
compress lz4-v2
push “compress lz4-v2”
persist-key
persist-tun

plugin /usr/lib64/openvpn/plugin/lib/2_0_9openvpn-auth-pam.so openvpn
verify-client-cert none
username-as-common-name
–script-security 3
status /var/log/openvpn-status.log
log /var/log/openvpn.log
verb 3
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

这个模块一定要openvpn-2.0.9.tar.gz的

进入目录编译得到openvpn-auth-pam.so
cd openvpn-2.0.9/plugin/auth-pam/

make
1
2
3
4
5
6

[root@fiel auth-pam]# ll
总用量 96
-rw-r–r– 1 root root 17682 11月 1 2005 auth-pam.c
-rw-r–r– 1 root root 12392 6月 15 01:13 auth-pam.o
-rw——- 1 root root 56 6月 15 16:16 ipp.txt
-rwxr-xr-x 1 root root 702 11月 1 2005 Makefile
-rwxr-xr-x 1 root root 22712 6月 15 01:13 openvpn-auth-pam.so
-rw-r–r– 1 root root 5235 11月 1 2005 pamdl.c
-rw-r–r– 1 root root 162 11月 1 2005 pamdl.h
-rw-r–r– 1 root root 9096 6月 15 01:13 pamdl.o

2_0_9openvpn-auth-pam.so
1
2
3
4
5
6
7
8
9
10
11
12

——–客户端配置———–

[root@dfg a-24]# cat open.ovpn
client
dev tun
proto tcp
remote 192.168.1.24 1194
resolv-retry infinite
nobind
remote-cert-tls server
persist-key
persist-tun
ca ca.crt
#ns-cert-type server
verb 3
auth-user-pass
1
2
3
4
5
6
7
8
9
10
11
12
13
14